Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Can you give me a stub .c prog that lets you reproduce this? –  FreshPhilOfSO Aug 14 '12 at 15:25
    
Sorry for asking but what do you mean by stub.c? –  hmmftg Aug 14 '12 at 15:26
    
Stub. A small test case .c file –  FreshPhilOfSO Aug 14 '12 at 15:34
    
I do call the C lib from db and it is something like this: RET := CALL_C_PROGRAM(NULL, '/address', OUT_RESP); –  hmmftg Aug 14 '12 at 15:37
    
Ok, please post the source code for a small C library that reproduces the issue –  FreshPhilOfSO Aug 14 '12 at 15:38
show 3 more comments

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment

This error is caused by uninitialized variables.
E.g. if you call external procedure like this:

declare
  p1  varchar2(32767);
  p2  varchar2(32767);
  p3  varchar2(32767);
  rc  pls_integer;
begin
  rc := call_c_program( p1, p2, p3 );
end;

, you will get the error.
And in this case you won't:

declare
  p1  varchar2(32767) := null;
  p2  varchar2(32767) := null;
  p3  varchar2(32767) := null;
  rc  pls_integer;
begin
  rc := call_c_program( p1, p2, p3 );
end;

I suggest you to implement a wrapper for call_c_program which will initialize all the variables that are passed to C library as parameters.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.