27 November 2011

External C Procedures In the Oracle Database


It is possible to use external library procedures within PL/SQL. If external procedures are written in Java language, it is very easy. You can create and compile a java source inside the database via DDL commands. But if you have to call some C procedures, you cannot do it as easy as Java sources. First, you have to create a C procedure, compile it with proper compiler, generate shared object (so) with necessary parameters and copy it to $ORACLE_HOME/lib ($LD_LIBRARY_PATH). Up to now, the steps should be followed in the server side, not in the database. Then, database steps (DDL commands) should be executed: create a library object and a wrapper PL/SQL function to call external C procedure. For java sources, there is no need for creating library object.

Prior to Oracle 11G, external procedures need a special network configuration, extproc with IPC protocol. With 11G, you do not need to make this tns configuration in most situations.

I will demonstrate, how to create a simple C procedure to get value of environment variable value and execute and get response of system commands with PL/SQL functions. Please note that, you can do most of these requirements with java sources. Note also that, C procedures below are intended to be use in simulation purposes. You may need to add some more lines of codes for your reqs.


I did not write any C programs almost for nine years. I remember my old-school days and how difficult I understand the pointers concepts in C languageJ. At least ten times I read Chapter 5 (or 6) - Pointers from “C How to Program” (Deitel pub.) J….



1.       First make necessary Oracle network configuration. You can use netmgr tool or do it by handJ.
listener.ora before change
==================================
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )

    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 80))
      (PROTOCOL_STACK =
         (PRESENTATION = HTTP)
         (SESSION = RAW)
      )
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 21))
      (PROTOCOL_STACK =
         (PRESENTATION = FTP)
         (SESSION = RAW)
      )
    )
  )
listener.ora after change
==================================
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = extproc)
      (SID_NAME = extproc)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_2)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 80))
      (PROTOCOL_STACK =
        (PRESENTATION = HTTP)
        (SESSION = RAW)
      )
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 21))
      (PROTOCOL_STACK =
        (PRESENTATION = FTP)
        (SESSION = RAW)
      )
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = extproc))
    )
  )

tnsnames.ora entry
==================================
EXTPROC_CONNECTION_DATA=           
 (DESCRIPTION=                    
   (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))                     
   (CONNECT_DATA=
    (SID=extproc)))


2.       Create a C program (c_system_lib.c) that fulfills our needs.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

char* execute_command( char *inputCommandText )
{

  FILE *fp;
  char temp[10000];
  char commandReturnText[1000000];
  char *commandReturnPointer;
 
  memset(&commandReturnText[0], 0, sizeof(commandReturnText));
 
  fp = popen(inputCommandText, "r");
  if (fp == NULL) {
    commandReturnPointer = "ERROR:Unable to popen...";
  }
  else{
    while (fgets(temp, sizeof(temp)-1, fp) != NULL) {
      strcat (commandReturnText,temp);
    }
    pclose(fp);
  }
 
  commandReturnPointer = commandReturnText;
  return commandReturnPointer;
}

char* get_env_value( char *environmentValueName )
{
    char *environmentValue;
    environmentValue = getenv(environmentValueName);
    return environmentValue;
}


3.       Compile c_system_lib.c with proper compiler, generate shared object (so) with necessary parameters and copy it to $ORACLE_HOME/lib ($LD_LIBRARY_PATH).  
[oracle@dhcppc5 admin]$ uname -a
Linux dhcppc5 2.6.18-194.17.1.0.1.el5 #1 SMP Wed Sep 29 15:40:03 EDT 2010 i686 i686 i386 GNU/Linux
[oracle@dhcppc5 sample_c_ext]$ gcc -c  c_system_lib.c
[oracle@dhcppc5 sample_c_ext]$ ld -shared -melf_i386 -o c_system_lib.so c_system_lib.o
[oracle@dhcppc5 sample_c_ext]$ chmod 755 c_system_lib.so
[oracle@dhcppc5 sample_c_ext]$ cp c_system_lib.so $ORACLE_HOME/lib


4.       Create a library object (c_system_lib) and two wrapper PL/SQL functions(ExecuteShellCommandViaC and GetEnvValueViaC) to call external C procedures:
CREATE LIBRARY c_system_lib is '/home/oracle/app/oracle/product/11.2.0/dbhome_2/lib/c_system_lib.so';
/

CREATE OR REPLACE FUNCTION ExecuteShellCommandViaC(pis_CommandString IN VARCHAR2)
RETURN VARCHAR2 AS EXTERNAL
NAME "execute_command"
LIBRARY c_system_lib
LANGUAGE C
PARAMETERS (pis_CommandString string,RETURN string);
/

CREATE OR REPLACE FUNCTION GetEnvValueViaC(pis_EnvironmentVariableName IN VARCHAR2)
RETURN VARCHAR2 AS EXTERNAL
NAME "get_env_value"
LIBRARY c_system_lib
LANGUAGE C
PARAMETERS (pis_EnvironmentVariableName string,RETURN string);
/


5.       Test the wrapper functions:
select GetEnvValueViaC('ORACLE_HOME') from dual;

GETENVVALUEVIAC('ORACLE_HOME')
--------------------------------------------------------------------------------
/home/oracle/app/oracle/product/11.2.0/dbhome_2



select ExecuteShellCommandViaC('/bin/ls /home/oracle') from dual;
EXECUTESHELLCOMMANDVIAC('/BIN/
--------------------------------------------------------------------------------
apex
apexlistener.sh
app
dav
deneme.txt
Desktop
emshutdown
emstartup
fix.sql
jdeveloper
listener
oracle
oracle_db11g_clr.gif
oradiag_oracle
otn_logo_small.gif
otn_new.css
repos
reset_apex
reset_imdbcache
reset.log
reset_OE.sql
reset_sqldev
reset_xdbPorts.sql
reset_xmldb
shrink.sh
shutdown
StartHere2010.htm
start_timesten
startup
status
stop_timesten
svn_repo
temp_dir
tmp





Please note that DBMS_SYSTEM.get_env procedure can also show envrionment variable values; but DBMS_SYSTEM package does not recommended for customer use. By default, there is no synonym for DBMS_SYSTEM supplied package.
DECLARE
  vs_OracleHome VARCHAR2(100);
BEGIN
  sys.dbms_system.get_env('ORACLE_HOME', vs_OracleHome);
  dbms_output.put_line(vs_OracleHome);
END;
/

1 comment:

Alan said...

Thanks for sharing your experiences bro. Please keep doing it (I recommend you to use Quora).