This discussion is archived
5 Replies Latest reply: Jan 26, 2013 9:01 AM by BillyVerreynne RSS

external c program call from plsql

Sudeshna Newbie
Currently Being Moderated
hi,
my db version is:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

entry in my listener.ora file
===========================
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc0))
)
)

ADR_BASE_LISTENER = /u01/app/oracle

entry in tnsnames.ora file:
===============================
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = extproc0))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.example.com)
)
)

listener is running perfectly.
created c program,
compile the program by gcc -c <filename>
ld -shared -o <filename.so> <filename.o>
cp <filename.so> $ORACLE_HOME/bin

create library & function from db. when I am invoking this function it is throwing error message:

ORA-28546: connection initialization failed, probable Net8 admin error

tnx in advance
  • 1. Re: external c program call from plsql
    BluShadow Guru Moderator
    Currently Being Moderated
    We seem to be missing a crucial part of the required information to be able to help you...
    Sudeshna wrote:
    create library & function from db. when I am invoking this function it is throwing error message:
    We don't know how you've created a library and function from the database, or how you are going about invoking it.

    Please be clear in your requirements. {message:id=9360002}
  • 2. Re: external c program call from plsql
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    It is not clear from your posting how you want to execute your C code from PL/SQL.

    Is the C code intended to be called via the extproc interface? Do you want to call it via the kernel instead?

    As for where to place your executable code - using +$ORACLE_HOME+ is a bad idea. It is for Oracle s/w. It is not a location for user s/w.

    If you want to use extproc, then the important configuration pieces are
    - the extproc configuration in the listener.ora file
    - the PL/SQL wrapper definition for the external procedure

    A basic example is in {message:id=2271919}.
  • 3. Re: external c program call from plsql
    Sudeshna Newbie
    Currently Being Moderated
    Sir,
    My c program is in a regular user home directory.
    steps followed:
    1.
    #include <ctype.h>
    int my_prog(char istr, char ostr){
    int i = 0;
    while(istr){
    ostr[i] = toupper(istr[i]);
    i++;
    }
    return 0;
    }

    2.compile and generate shared library using the following commands:
    gcc -c sb_lib.c
    ld -shared -o sb_lib.so sb_lib.o

    3.copy it in /bin
    cp sb_lib.so $ORACLE_HOME/bin/

    4.configure and restarted listener as i have already mentioned.

    5.log in as sysdba:
    grant create library to hr.

    6. log in as hr:
    create or replace library sb_lib
    as '/app/oracle/product/10.2.0.3/bin/sb_lib.so'

    7.create function

    create or replace function c_function(
         p_istr in varchar2
    ,     p_ostr out varchar2)
    return binary_integer
    as external
         library sb_lib
         name "my_prog"
         language c
         parameters (
              p_istr string
         ,     p_ostr string);
    compiled

    8.exec anonymous block to call it

    set serveroutput on
    declare
         res binary_integer;
         v_in CHAR(100);
         v_out CHAR(100);
    begin
         v_in := 'hello world';

         res := c_function(v_in, v_out);

         dbms_output.put_line(res);
         dbms_output.put_line(v_in);
         dbms_output.put_line(v_out);
    end;
    /

    giving me error.
  • 4. Re: external c program call from plsql
    onedbguru Pro
    Currently Being Moderated
    The most important thing you neglected to share with us is the actual ERROR codes/messages. The errors will tell you a lot about what is missing from the definition/configuration. Are there any errors in the alert or listener log? if so, post those as well.
  • 5. Re: external c program call from plsql
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Here's a working example (11.2.0.3 using Oracle Linux 5.9):
    SQL> !cat test.c
    #include <ctype.h>
    
    int upcase(char *istr, char *ostr){
            int i = 0;
    
            while(istr){
    ostr[i] = toupper(istr[i]);
    i++;
    }

    return 0;
    }

    SQL>
    SQL> --// compile
    SQL> ! gcc -fPIC -c test.c

    SQL> --// result
    SQL> ! ls -l test*
    -rw-r--r-- 1 oracle oinstall 140 Jan 26 18:40 test.c
    -rw-r--r-- 1 oracle oinstall 1480 Jan 26 18:48 test.o

    SQL>
    SQL> --// create shared object
    SQL> ! ld -shared -o libtest.so test.o

    SQL>
    SQL> ! file libtest.so
    libtest.so: ELF 64-bit LSB shared object, AMD x86-64, version 1 (SYSV), not stripped

    SQL>
    SQL> --// wrapper library and function
    SQL> create or replace library libtest as '/home/oracle/sql/libtest.so';
    2 /

    Library created.

    SQL>
    SQL> create or replace function upcase( instr in varchar2, outstr out varchar2 ) return binary_integer is
    2 external
    3 library libtest
    4 name "upcase"
    5 language C
    6 calling standard C
    7 parameters(
    8 instr string,
    9 outstr string
    10 )
    11 ;
    12 /

    Function created.

    SQL>
    SQL> declare
    2 str1 varchar2(20);
    3 str2 varchar2(20);
    4 rc binary_integer;
    5 begin
    6 str1 := 'hello world';
    7 rc := upcase( str1, str2 );
    8 dbms_output.put_line( 'rc='||rc||' instr='||str1||' outstr='||str2 );
    9 end;
    10 /
    rc=0 instr=hello world outstr=HELLO WORLD

    PL/SQL procedure successfully completed.

    SQL>
    The following 2 configuration settings are needed - in the +listener.ora+ (server) and the +tnsnames.ora+ (client).
    
    For the Listener - add the ADDRESS line for EXTPROC to the LISTENER config, and add the EXTPROC SID entry to the SID list.
    LISTENER=
    (DESCRIPTION=
    (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))
    )
    )


    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME= <insert oracle home here> )
    (PROGRAM = extproc)
    (ENVS = "EXTPROC_DLLS=ANY")
    )
    )
    The client needs to have a TNS alias that defines how to contact EXTPROC. This is done in +tnsnames.ora+:
    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))
    (CONNECT_DATA=(SID=plsextproc))
    )
    Are you using RAC? if so, I do not think IPC connections are allowed by default between an Oracle db  server process and the Grid listener process. In which case, you can create an IPC only listener in the Oracle o/s user.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points