Calling External routines from PL/SQL
(Part 1)

 

This document describes how to to call external routines (eg. written in C) from PL/SQL.

This page describes the creation of a function which takes two double precision floating point numbers and returns a double precision floating point number. This was used as an initial example as the parameter passing mechanism is simplest in this case.

This is the first page of three. The next page describes an example which accepts and returns variable length strings (VARCHAR2s) as parameters, and demonstrates the use of indicator parameters to hold the null/not-null status of the strings and their input and return lengths. The third page describes how to implement an external routine which can execute an arbitrary operating system command.

These notes apply specifically to Oracle running on Sun Solaris, although the principles probably apply to other platforms as well.

 

• First write your C routine.

  In this example, our C routine is actually a function which takes two double precision floating point numbers as input parameters and returns a double precision floating point result.

  ie. the function prototype is:

         double chisq( double arg1, double arg2 )
      

  For more information about passing parameters to external routines, see More about parameters later in this page.
  
  
  
  

• Compile the routine, including the appropriate include libraries.

  This example uses the GNU C compiler, gcc, to compile the C routine.

      gcc -I$ORACLE_HOME/rdbms/demo
  	-I$ORACLE_HOME/rdbms/public \
          -I$ORACLE_HOME/plsql/public \
          -I$ORACLE_HOME/network/public \
          -c chisq.c
      

  Note: In general, compiling C code for use as an Oracle external routine needs the above include libraries (which contain code for the special Oracle types such as OCINumber, and also enable the routine to make database calls itself). However, if, as in this case, the routine does not need any of these facilities, a simple "gcc -c chisq.c" will suffice.
  
  
  
  

• Next load the compiled object module into a dynamic load library. This requires the $ORACLE_HOME/rdbms/demo/demo_rdbms.mk make file.

      make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk extproc_callback \
           SHARED_LIBNAME=chisqlib.so \
           OBJS=chisq.o
      

  The above command creates a dynamic load library called chisqlib.so in the current directory, containing the single object module: chisq.o
  
  
  
  

• Move the generated dynamic load library to some appropriate directory of your choosing.

  In my case, I created a directory: $ORACLE_HOME/local/c_libraries to hold these libraries, but it might be better, to simplify future upgrades of the Oracle deliverables, to create this directory outside the $ORACLE_HOME directory, so that it is not associated with a particular version of Oracle.
  
  
  
  

• Within Oracle (eg. using SQL*Plus), declare the library you have created:

      CREATE OR REPLACE LIBRARY CHISQ_C_LIB AS
             '/local0/oracle/local/c_libraries/chisqlib.so';
      

  You only need to do this step once, you do not need to repeat it if you rebuild the library. This step can be performed under any schema which has CREATE LIBRARY privileges, but I recommend running it under the SYSTEM schema.
  
  
  
  

• Within Oracle (eg. using SQL*Plus), define a PL/SQL function or procedure to call the external routine.
  eg:

      CREATE OR REPLACE FUNCTION chisq (
             arg1 IN DOUBLE PRECISION,
             arg2 IN DOUBLE PRECISION )
             return  DOUBLE PRECISION AS
  
             EXTERNAL NAME "chisq"
             LIBRARY "CHISQ_C_LIB"
             LANGUAGE C
             PARAMETERS (
                 arg1 double,
                 arg2 double,
                 RETURN );
      

  Note that the LIBRARY clause indicates the logical name of the external library, as declared to Oracle in the previous step.

  The NAME clause indicates the name of the C function which we are calling from within that library. (Although we have not discussed it yet, the external library could contain several external routines.) The name of the C function is the real name of the function as declared in the C code, not the name of the source file in which it was defined.

  It is important that the EXTERNAL NAME value is surrounded in double quotes, otherwise Oracle will ignore the case and search for the routine name in uppercase when the routine is called. This will fail with the error:

      ORA-06521: PL/SQL: Error mapping function
      ORA-06522: ld.so.1: extprocPLSExtProc: fatal: XXXXXX: can't find symbol
      

  where XXXXXX is your routine name in uppercase.

  The PARAMETERS clause is optional, but it is best to include it. The names of the parameters (arg1, arg2) are arbitrary, they do not have to match the names of the parameters as defined in the C code.

  This step can be performed under any schema, but I recommend running it under the SYSTEM schema.

  For more information on parameter passing, refer to More about parameters (below).
  
  
  
  

• Create a public synonym and public execution rights to the routine (optional)
  eg.

      grant execute on system.chisq to public;
  
      create public synonym chisq on system.chisq;
      

  
  
  
• test the external routine

      declare
  
        arg1 double precision;
        arg2 double precision;
        return_value double precision;
      
      begin
  
        arg1 := 3.841;
        arg2 := 1;
  
        return_value := chisq(arg1,arg2);
  
        dbms_output.put_line('return_value from chisq ' || to_char(return_value));
  
      end;
      .
      /
      

 

Pre-requisites

The ability to support external routines was introduced in Oracle 8. It is implemented using the Oracle Listener (ie. the database makes a call via the listener to the external routine, which is then invoked independently of the database. This protects the database from crashing if the external routine terminates abnormally.)

Consequently, entries need to be added to the listener.ora and tnsnames.ora files in the $ORACLE_HOME/network/admin directory on the database server. This only needs to be done once, not for each external routine.

• Setting up the listener.ora

  According to the Metalink documentation, you need to create a separate listener for calling external routines, although you still define it in the existing listener.ora file.

  eg. add the following stanzas to your listener.ora file:

     CALLOUT_LISTENER =
       (ADDRESS_LIST =
          (ADDRESS= (PROTOCOL= IPC)(KEY=EXTPROC))
       )
  
       log_directory_callout_listener = some_directory_name
       trc_directory_callout_listener = some_directory_name
  
       SID_LIST_CALLOUT_LISTENER =
         (SID_LIST =
           (SID_DESC =
             (SID_NAME = PLSExtProc)
             (ORACLE_HOME= your_value_for_$ORACLE_HOME)
             (PROGRAM = your_value_for_$ORACLE_HOME/bin/extproc)
           )
         )
  

  and then start this listener:

     lsnrctl start callout
  

  However, I have not found it necessary to start a new listener, and have simply added the ADDRESS_LIST and SID_DESC stanzas to my existing listener definition (simply called "LISTENER") in my existing listener.ora file. ie. the ADDRESS_LIST stanza is inserted in the LISTENER= block, and the SID_DESC stanza is inserted in the SID_LIST_LISTENER= block.

  I then just restart the default listener:

     lsnrctl stop
     lsnrctl start
  

  Notes:

  1. You may find that these entries have already been set up from the default installation.
     
     
  2. The prefix CALLOUT is arbitrary, it just gives a name to the independent listener so that you can stop and start it without affecting the main database listener.
     
     
  3. The SID name does not matter as long as it matches with the SID in the tnsnames.ora (see below)
     
     
  4. The name of the KEY in the ADDRESS line does not matter as long as it matches with the KEY in the tnsnames.ora (see below).
     
     
  5. The program name is fixed. This must be 'extproc'. (Note: this is not the name of the external routine you have written, but refers to an Oracle executable which listens for calls to external routines and then invokes them.)
  
  
  
• Setting up the tnsnames.ora

  Add the following stanza to your tnsnames.ora file:

      extproc_connection_data =
        (DESCRIPTION =
          (ADDRESS =
            (PROTOCOL = IPC)
            (KEY = EXTPROC)
          )
          (CONNECT_DATA =
            (SID = PLSExtProc)
          )
        )
     

  Notes:

  1. The SID= name does not matter as long as it matches with the SID in the listener.ora (see above)
     
     
  2. The KEY= name must match the IPC KEY name in the ADDRESS_LIST section of listener.ora (see above)
     
     
  3. The entry name 'extproc_connection_data' is fixed,

 

More about parameters

Once the basic principles have been grasped, a lot of the complexity in calling external routines is in passing parameters between Oracle and the external routine.

There are two issues: mapping Oracle datatypes onto C datatypes, and handling parameter attributes (eg. how do we indicate that a returned value from an external routine is null, or tell the external routine the maximum length of a varchar2 parameter?)

 

Parameter passing by value or by reference

By default, most IN and RETURN parameters are passed by reference, as opposed to by value. (There are some exceptions, but they're not common. They're documented in the Oracle8i Application Developer's Guide - Fundamentals, Release 2 (8.1.6) manual.) This means that pointers to the locations of the values are passed to the external routine (as opposed to copies of the input parameter values), so the external routine's prototype must declare its input and return parameters as char*, int*, double* as appropriate.

 

Parameter datatypes

Although not complete, this table shows how PL/SQL variable data-types match to C datatypes. Note that the C datatype is dependent on whether the PL/SQL variable is an IN, OUT, IN OUT or RETURN parameter.

PL/SQL datatype	parameter mode	C datatype
DOUBLE	IN, RETURN	double
DOUBLE	IN OUT, OUT	double*
VARCHAR2	IN, RETURN	char*
VARCHAR2	IN OUT, OUT	char*
NUMBER	IN, RETURN	OCINumber*
NUMBER	IN OUT, OUT	OCINumber*

Note that the PL/SQL NUMBER datatype maps to the OCINumber* C datatype. This is not a native C datatype but is defined in the header file $ORACLE_HOME/rdbms/demo/oci.h (I think). Unfortunately as this is not a primitive C datatype, you cannot use the usual C arithmetic operators to manipulate it. Instead, you must use special OCI operators.
eg.

   OCINumberAdd() - add two OCINumbers,
   OCINumberMul() - multiply two OCINumbers  etc.
   

However, I've not yet been able to get external routines which pass NUMBER datatypes as parameters to work. As a get-around, create the external routine so that it passes DOUBLEs as its parameters, and move any NUMBER values to temporary DOUBLE variables to use as parameters in your PL/SQL program.

The same problem occurs with DATE variables (which map to OCIDate datatypes in external routines).

 

Parameter attributes

As well as the actual parameter values, we may wish to pass other information to the external routine about the parameters. For example, we may need to tell the external routine what the length of an input varchar2 parameter is (the length of the variable will be known, but what about the actual length of the string held in it?). We may also need to indicate to an external routine that a parameter is null: If we just pass the parameter values, then there is no way to tell the difference between the values 0 and null in a numeric field.

The next page works through an example which makes use of parameter attributes.

 

Related documentation

I didn't find any of the documentation particularly easy to follow as a step-by-step guide to calling an external routine, which is why I wrote this page.

However, I referred to the following documents whilst doing so.

• Chapter 10: External Routines of the Oracle8i Application Developer's Guide - Fundamentals (Release 2 (8.1.6)) manual
  
  
• Documents on Oracle MetaLink
  • 50598.1 : PL/SQL 8.0: Building External C Procedures using the Callback Method

    This document was less useful as an introduction as it further complicates the issue by subsequently calling Oracle from the External routine.
    
    

  • 50868.1 : Calling C from PL/SQL in Oracle8

    This document was the most useful, as it is the simplest. It advises that it "contains some references that are Digital Unix specific, however the technique can be applied to other platforms too". In truth there is hardly anything which is Digital Unix specific.