5 Replies Latest reply: Jan 26, 2013 11:01 AM by Billy~Verreynne RSS

    external c program call from plsql

    Sudeshna
      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
          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
            Billy~Verreynne
            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
              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
                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
                  Billy~Verreynne
                  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.