Oracle - External Procedures

http://docs.oracle.com/cd/A58617_01/server.804/a58236/10_procs.htm

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;

/

Example:
CREATE OR REPLACE FUNCTION SYSTEM.comp_Shell(cmd IN char) RETURN BINARY_INTEGER
AUTHID CURRENT_USER
   AS EXTERNAL
   NAME  "sh"
   LIBRARY comphost
   LANGUAGE C
   PARAMETERS (cmd     string,
               RETURN  INT);
/

CREATE OR REPLACE FUNCTION SYSTEM.comp_Host(Cmd IN  VARCHAR2) RETURN BOOLEAN
AUTHID CURRENT_USER IS
-- This Function is used to call the external function which will execute the host commands on the server node
-- The exit status of the execution is returned back to the function as a boolean.
--
   Stats   INTEGER;
   Retrn   BOOLEAN;
BEGIN
-- Call the external function for executing the host command
   Stats := comp_Shell(Cmd);

-- Check the status of the shell execution
   IF Stats = 0 THEN
      Retrn := TRUE;
   ELSE
      Retrn := FALSE;
   END IF;

-- Return the execution status to the end user.
   RETURN Retrn;
END;