MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

External C Procedure

with 7 comments

Somebody wanted to know how to write an external procedure in C. I entered the reply as a comment and then thought it would be better as a blog post. Here are the instructions for deploying and calling an external procedure.

The quick list of things to do, summarized from Chapter 13 on External Procedures from my Oracle Database 11g PL/SQL Programming book are:

1. Configure your tnsnames.ora file with a callout listener, which is required because the IPC/TCP layers must be separated. That means your LISTENER should only support the TCP protocol, which means you’ll remove the IPC line from your listener.ora file. If you fail to take this step, you’ll encounter the following error:

ORA-28595: Extproc agent: Invalid DLL Path

Your callout listener has two parts, the CALLOUT_LISTENER and the SID_LIST_CALLOUT_LISTENER listener. Your listener.ora file should look like this:

CALLOUT_LISTENER = 
  (DESCRIPTION_LIST =
    (DESCRIPTION = 
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)
                   (KEY = extproc)
        )
      )
    )
  )
 
SID_LIST_CALLOUT_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = <oracle_home_directory>)
      (PROGRAM = extproc)
      (ENV = "EXTPROC_DLLS=ONLY:/<customlib>/writestr1.so,LD_LIBRARY_PATH=/lib")
    )
  )

2. You also need to have a tnsnames.ora file with an instance TNS alias and EXTPROC alias, like:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)
               (HOST = <host_name>.<domain_name>)
               (PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <database_sid>)
    )
  )
 
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)
                 (KEY = EXTPROC)
      )
    )
    (CONNECT_DATA = 
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

3. Create a shared library, like this:

#include <stdio.h>
void writestr(char *path, char *message) {
  FILE *file_name;
  file_name = fopen(path,"w");
  fprintf(file_name,"%s\n",message);
  fclose(file_name); }

4. In Linux, compile the shared library from Step #1 with the following syntax:

gcc -shared -o writestr1.so writestr1.c

If you encounter an error like this:

/usr/bin/ld: /tmp/ccTIWHVA.o: relocation R_X86_64_32 against `.rodata' can not be used when making a shared object; recompile with -fPIC
/tmp/ccTIWHVA.o: could not read symbols: Bad value
collect2: ld returned 1 exit status

Then, use this alternative syntax:

gcc -shared -fPIC -o writestr.so writestr.c

5. Create a library. Granting CREATE LIBRARY is a potentially large security risk, and a DBA should put all external procedure libraries in a single schema with additional monitoring protocols.

CREATE [OR REPLACE] LIBRARY library_write_string AS | IS '/<customlib>/writestr1.so

6. Write a wrapper to the library.

CREATE OR REPLACE PROCEDURE write_string
( PATH VARCHAR2, message VARCHAR2 ) AS EXTERNAL
LIBRARY library_write_string
NAME "writestr"
PARAMETERS
(PATH STRING, message STRING);

7. Call the wrapper procedure with valid values:

BEGIN
  write_string('/tmp/file.txt','Hello World!');
END;
/

Hope this helps some folks.

Written by maclochlainn

April 20th, 2009 at 6:50 pm

Posted in C,Oracle

«
»