IT-eye

At the moment we are implementing an internet application (with ADF faces)and have to call a (pro*)C-module. This c-module is an old monster of 8000 lines of code and there is no time to rewrite (and test !) it to plsql. The module is also called in the backoffice system in webforms (ora_ffi).

I want :

• to use the same dynamic link library (DLL) for both systems
• no linking of pro*C libraries in the java application
• security

The solution is to build a plsql wrapper around the DLL and store this in the database so that you can call the plsql module in the database.

This is what I did (Linux) :

1. Listener proces.

Start a new listener proces exclusively for external procedures. By default the agent that handles the procedures is named extproc.

tnsnames.ora

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = IPC)
        (KEY = EXTPROC_KEY)
      )
    )
    (CONNECT_DATA =
      (SID = EXTPROC_AGENT)
    )
  )

listener.ora

PLS_LISTENER =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)
                   (KEY = EXTPROC_KEY)
        )
    )

SID_LIST_PLS_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = EXTPROC_AGENT)
      (ORACLE_HOME = /u01/product/1010)
      (PROGRAM = extproc)
      (ENVS="C_LIB_HOME=/u01/projects/bin")
    )
  )

$C_LIB_HOME is an environment variable which is used for the location in step 2. De KEY in tnsnames.ora and listener.ora must be the same.

2. Make the location of the library known in the database

Run this command in sqlplus :

create or replace library c_analyse is
  '${C_LIB_HOME}/analyse.so'
/

3. Build the wrapper per function.

This can de done with the AS LANGUAGE clause in plsql functions, procedures or packages. I prefer the package specification. In the package body you can then add other code which is related to the c-module.

create or replace package C_wrapper
as

   function init
   return pls_integer
   as language c
      name "init"
      library c_analyse;

   function do_things( par1  pls_integer
                     , par2  pls_integer
                     )
   return pls_integer
   as language c
      name "do_things"
      library c_analyse
      parameters(par1 long, par2 long);

end;

4. Errorhandling.

With step 1-3 it all works fine and I have not changed a single line of code in the C monster. But if you want to get proper error messages back from the call, you have to add a parameter in each call and some(?) more lines in the code. You can make use of the OCI functions OCIExtProcRaiseExcp or OCIExtProcRaiseExcpWithMsg. Add the parameter context pointer to all the functions.

int do_things( OCIExtProcContext *ctx
             , long par1
             , long par2
             )
...
     if (OCIExtProcRaiseExcpWithMsg(ctx, (int)20100,
          "This is the error message", 0) == OCIEXTPROC_SUCCESS)
    {
      return;
...

In the plsql wrapper add the WITH CONTEXT clause and the extra parameter:

   function do_things( par1  pls_integer
                     , par2  pls_integer
                     )
   return pls_integer
   as language c
      name "do_things"
      library c_analyse
      with context
      parameters(context, par1 long, par2 long);