Error on Oracle calling external procedures

functionsoraclestored-procedures

I have a C library which should be called from a pl/sql function so I make a .so library and after that I create a pl/sql function to call it.

the C source(libcprog.c) is like this:

int exec_hmmftg ( char *p1 , char *p2, char *p3 ){}

the library in oracle database is:

CREATE LIBRARY CPROGLIB UNTRUSTED AS '/libcprog.so';

the created pl/sql function is:

CREATE OR REPLACE FUNCTION CALL_C_PROGRAM(P1    IN     VARCHAR2,
                                          P2    IN     VARCHAR2,
                                          P3    IN OUT VARCHAR2)
    RETURN BINARY_INTEGER 
    IS EXTERNAL NAME "exec_hmmftg"
    LIBRARY CPROGLIB
    LANGUAGE C
    PARAMETERS(P1 STRING, P2, P3 BY REFERENCE STRING, RETURN INT);

When I tried to call CALL_C_PROGRAM I catch:

ORA-01405: fetched column value is NULL

How can I know where is the problem?

New

I changed library definition to address somewhere wrong and I still catch the same exception!

Is there a parameter or something which I have to set before calling the function?

Best Answer

The following configuration works for me on Oracle 11.2.0.2:

C prog:

[oracle@node1 ~]$ cat exec_hmmftg.c
#include <stdio.h>

int exec_hmmftg ( char *p1 , char *p2, char *p3 )
{
  return 5;
}
[oracle@node1 ~]$

Compile it, and make sure it's placed in $ORACLE_HOME/bin or $ORACLE_HOME/lib (configuration can be changed to allow other directories) - compiler flags will differ for AIX too.

gcc -shared -o $ORACLE_HOME/lib/exec_hmmftg.so exec_hmmftg.c

Configure EXTPROC in the listener.ora file:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.6)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PHIL112)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
      (SID_NAME = PHIL112)
    )
  )

Restart the listener with lsnrctl stop followed by lsnrctl start.

Create the reference to the external library & create the function to interface between PL/SQL and the C prog:

[oracle@node1 ~]$ sqlplus phil/phil

SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 14 17:51:21 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE LIBRARY CPROGLIB UNTRUSTED AS '/u01/app/oracle/product/11.2.0/dbhome_1/lib/exec_hmmftg.so            ';
  2  /

Library created.

SQL> CREATE OR REPLACE FUNCTION CALL_C_PROGRAM(P1 IN VARCHAR2, P2 IN VARCHAR2, P3 IN VARCHAR2)
  2      RETURN BINARY_INTEGER
  3      IS EXTERNAL NAME "exec_hmmftg"
  4      LIBRARY CPROGLIB
  5      LANGUAGE C
  6      PARAMETERS(P1 STRING, P2 STRING, P3 STRING, RETURN INT);
  7  /

Function created.

SQL>

Test the function:

SQL> select call_c_program('a','b','c') from dual;

CALL_C_PROGRAM('A','B','C')
---------------------------
                          5

SQL>

Not quite sure what you're doing wrong without seeing the full C you've written. Try and setup this simple case I've made for you, then add functionality bit by bit from there.

Related Question