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

7 Responses to 'External C Procedure'

Subscribe to comments with RSS or TrackBack to 'External C Procedure'.

  1. Hi Mac,

    That’s wonderfully written, I work with Windows environment and if you have any example for calling a dll to access win registry info that would help me a lot.

    Prazy

    6 Sep 09 at 10:28 pm

  2. I’ll put it on the list but I’m not sure when.

    maclochlainn

    6 Sep 09 at 10:58 pm

  3. Thanks a Ton, Mac!

    Prazy

    6 Sep 09 at 11:41 pm

  4. Hi Mac,

    Nice tutorial. It helped me a lot. Thanks!

    But I think there are 2 issues:
    Step 2 (library name is missing in include statement):
    #include

    Step 4 (keyword IS or AS is missing):
    CREATE [OR REPLACE] LIBRARY IS|AS library_write_string ‘//writestr1.so

    andreas

    17 Nov 09 at 4:03 pm

  5. Hi Mac,

    Could you please guide me what changes I have to made
    in listener.ora and tnsnames.ora file to execute
    C-PRG in Oracle 10g Linux Enviroment.
    Regards
    Nidhika

    Nidhika Vaidh

    20 Jun 13 at 11:50 pm

  6. I Mac ,

    I have tried this in Mac OS , but when I am compiling the function in oracle it is giving the below error:

    ORA-06520: PL/SQL: Error loading external library
    ORA-06522:      0509-022 Cannot LOAD module /usr/orasys/11.2.0.4r1/lib/encrypt_test.so.
    0509-026 System error: Cannot run a file that does NOT have a valid format.

    I checked both TNSlistener and listener.ora and also given the library the correct path for library creation still I am getting the same error. Could you please help?

    Thanks in advance
    Abhimpi

    Abhimpi

    27 Oct 14 at 6:57 pm

  7. Abhimpi, Is Mac OS X your native OS? Are you running Oracle 11g server on Mac OS X or Oracle 11g client on Mac OS X?

    maclochlainn

    12 Nov 14 at 10:28 pm

Leave a Reply