Subject:  Calling C from PL/SQL in Oracle8 
Creation Date:  23-APR-1998 
 

       Calling C routines from PL/SQL in Oracle8 on Digital UNIX          
       =========================================================

This article contains some references that are Digital specific, however
the technique can be applied to other platforms too.

Calling C code from PL/SQL in Oracle7 was messy. A C program would have
to sit in a loop, waiting on information signalled through a pipe by PL/SQL.
The PL/SQL would interface to the pipe via calls to DBMS_PIPE. When the
looping C program received information from the pipe it could then perform
an action.

Oracle8 provides a much neater solution. 

The mechanism is as follows:

o A C function is declared within the PL/SQL as a foreign function.
o A special callout listener is started under the Oracle8 ORACLE_HOME.
  This listener doesn't have to be separate from the normal SQL*Net
  listener.
o At runtime, when the PL/SQL function calls the C routine, the name of the 
  foreign function is looked up. If the name is valid, then the callout 
  listener process is accessed by the server.
o The listener in turn invokes a process named extproc (plsff in 8.0.1).
  The extproc process then runs the C code.
  
Note: This implementation is considered safe because the address space
      of the C program is different to the address space of the Oracle 
      background processes. Consequently if the C program crashes it does 
      not bring the instance down.

Restrictions with External Procedures.
======================================
1. Your external procedure to call must be located on the server when
   the database lives. You can NOT call an external procedure on 
   another machine or client.

2. With Oracle release 8.0, the external procedure could only reconnect
   to the Oracle database using the OCI api. With Oracle 8i, this
   restriction has been removed, the external procedure can now make Pro*C 
   calls back to the database.


How to setup and configure External Procedures
============================================

Step 1.  Setup the callout listener:
------------------------------------

The listener needs to be declared in the usual listener.ora.
The contents should be similar to:

callout_listener_V804 =
  (ADDRESS_LIST =
     (ADDRESS=
        (PROTOCOL=IPC)
        (KEY=callout)
     )
  )

SID_LIST_callout_listener_V804 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = callout)
      (ORACLE_HOME = /oracle2/app/oracle/product/8.0.1)
      (PROGRAM = extproc)
    )
  )

Notes:

1. The SID name does not matter as long as it matches
   with the SID in the tnsnames.ora (see below)

2. The program name is fixed. This must be 'extproc'. ('plsff' in Oracle 8.0.1) 

3. The listener should be started in the same way as a standard Oracle8
   listener.  ie

    % lsnrctl start callout_listener_V804


Step 2.  Create a tnsnames.ora entry for the callout listener:
--------------------------------------------------------------

Your tnsnames.ora needs to contain an entry similar to the following:
 
extproc_connection_data =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = IPC)
      (KEY = callout)
    )
    (CONNECT_DATA =
      (SID = callout)
    )
  )

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, ('pls_default_callout' 
   in Oracle 8.0.1)


Step 3.  Write a PL/SQL definition of the C code being called:
-------------------------------------------------------------

An example: (The library referenced in line 2, is created in Step 7)

CREATE OR REPLACE LIBRARY MY_C_LIB IS
   '/usr1/users/ashrives/oracle8/proc/myshared.so';

CREATE OR REPLACE PROCEDURE wordcat (
   word1 IN VARCHAR2,
   word2 IN VARCHAR2,
   both_words OUT VARCHAR2) IS

EXTERNAL NAME "wordcat"
LIBRARY "MY_C_LIB"
PARAMETERS (
   word1 string,
   word1 INDICATOR short,
   word1 LENGTH short,
   word2 string,
   word2 INDICATOR short,
   word2 LENGTH short,
   both_words string,
   both_words INDICATOR short,
   both_words LENGTH short,
   both_words MAXLEN short);
/

Step 4.  Write some PL/SQL that uses the above definition:
---------------------------------------------------------

CREATE OR REPLACE PROCEDURE wordcat_test (
   word1 IN VARCHAR2,
   word2 IN VARCHAR2) AS

   both_words VARCHAR2(50);

BEGIN
   wordcat(word1, word2, both_words);

   dbms_output.put_line('word1     ="' || word1 || '"');
   dbms_output.put_line('word2     ="' || word2 || '"');
   dbms_output.put_line('both_words="' || both_words|| '"');
END;
/

Step 5. Write some C code that is to be called:   (test.c)
-----------------------------------------------

/*
   Concatenate 2strings (word1, word2) together, separate by a space
*/

#include <string.h>
#include <stdio.h>

void wordcat (char *word1, short word1_ind, short word1_len,
              char *word2, short word2_ind, short word2_len,
              char *both_words,      short *both_words_ind,
              short *both_words_len, short *both_words_mxlen)
{
   FILE *outfile;
   outfile=fopen("/tmp/wordcat.out","w");
   fprintf(outfile,"word1           =\"%s\"\n",word1);
   fprintf(outfile,"word1_ind       =%d\n",word1_ind);
   fprintf(outfile,"word1_len       =%d\n",word1_len);
   fprintf(outfile,"word2           =\"%s\"\n",word2);
   fprintf(outfile,"word2_int       =%d\n",word2_ind);
   fprintf(outfile,"word2_len       =%d\n",word2_len);
   fprintf(outfile,"both_words      =\"%s\"\n",both_words);
   fprintf(outfile,"both_words_ind  =%d\n",*both_words_ind);
   fprintf(outfile,"both_words_len  =%d\n",*both_words_len);
   fprintf(outfile,"both_words_mxlen=%d\n",*both_words_mxlen);
   fflush(outfile);

   /* Ensure we have enough space to write into */
   if ((*both_words_mxlen >= (word1_len + word2_len + 1)) &&
              (word1_ind != -1) && (word2_ind != -1))
   {
      /* Put the words together, separated by spaces */
      strcpy(both_words, word1);
      strcat(both_words, " ");
      strcat(both_words, word2);

      /* Set the length of the OUT string */
      *both_words_len = word1_len + word2_len + 1;

      /* Set the OUT string NULL indicator */
      *both_words_ind = 0;
   }
   else
   {
      *both_words_ind = -1;
   }
}

Step 6. Build a Sharable (dynamic link) library:
------------------------------------------------

There are a couple of ways of building a shared library.
The shared library will include your code. It is this code that
is called from PL/SQL.

Which method you use depends on whether the library needs to be totally
stand-alone. 

Pick either Method 1 or 2 below:

(i) Method 1 - Genuine Shared Library.

This method is probably the easiest, and creates the smallest 
shared library. Internally the shared library will reference, but not
include, other shared libraries.

Firstly, create a makefile with the following contents:

include $(ORACLE_HOME)/plsql/lib/env_plsql.mk
.SUFFIXES: .pc .c .o
EXTP_MKFILE = $(ORACLE_HOME)/rdbms/demo/demo_rdbms.mk

myshared.so:
  $(MAKE) -f $(EXTP_MKFILE) extproc_callback SHARED_LIBNAME=myshared.so OBJS="test.o"

Notes

1. The last line (before $(MAKE)) should start with a <TAB> character.
2. myshared.so at line 5 is the name of the shared library to be built.
   This is referenced in your SQL in step 3.
3. test.o iS the file name of the C code from step 5 with a .o suffix.

To use this makefile to produce a shared library, type the following:
  
   % make -f <makefile name> myshared.so


IMPORTANT
========= 
If you use this method to build the shared library you MUST ensure that
the UNIX environment variable LD_LIBRARY_PATH is set to:

   $ORACLE_HOME/lib:/usr/lib

BEFORE starting the extproc listener. If you don't, you will see errors like:
******* ^^^^ READ THIS ^^^^ ********

  ORA 6520 PLSQL: Error loading external library .....
  ORA 6522 dlopen: cannot load <shared library.so>
  ORA 6512 at ....
  ORA 6512 at .... 

when running your PLSQL in Step 8.


(ii) Method 2 - Standalone Library

The library to be built in this step contains our C code as well
as other functions that our C code calls. All functions the C code 
uses (eg strcpy) must be linked into our shared library as well. 

This method is completely standalone, LD_LIBRARY_PATH need not
be set when starting the extproc listener.


We must first compile our C code:
 
   % cc -c test.c

This produces a test.o file.

We must now merge this object with the object code of all functions
our C code calls.

/usr/lib/libc.a stores the objects that make up the C function library,
including strcpy(). The following example extracts the objects
from this library:

       (A) Create a set of .o files from libc.a (ie so you can call functions
           like strcpy() ...etc)

           % cd /tmp/andy
           % ar xv /usr/lib/libc.a
     
       (B) Copy test.o created above into the directory containing 
           the '.o's created in (A).

           % cp test.o /tmp/andy

       (C) Create our shared library:
  
           % cd /tmp/andy
           % ld -shared -o myshared.so *.o  

           Note: Under Digital UNIX 4.0d, you will get the follow 
                 warning when you run this ld command:
  
                 Warning:Unresolved:
                 __ldr_data
                 __ArgV
                 __ArgC

                 This can be ignored, the shared library created will 
                 still be valid.
               
   It is this resulting shared library, that is referenced in the library 
   definition in Step 3 above. It is REALLY IMPORTANT that the sql in Step 3
   references the shared library just created!
          
This step is platform specific. The above is for Digital UNIX.

@See [NOTE:16356.1] for help on building a shared library for your platform.

Step 7.
-------
Compile each of the 2 sql scripts above.

Note, depending on versions, you may find your PLSQL has to be
compiled from Server Manager. 

Step 8.
-------

Now run your PL/SQL!

eg   SQL> set serveroutput on
     SQL> execute wordcat_test('hello','world');

You should see output like:

word1=   "hello"
word2=   "world"
both_words= "hello.world"

PL/SQL procedure successfully completed.