| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: rpc problem when calling external procedure
I see that your example files are using C Ansi. I don't have a C Ansi
compiler. Does this affect something? Here is the code.
The C program.
The unix lib
The Oracle lib
The function
The call
Thanks once more.
Thomas Kyte wrote:
>
> A copy of this was sent to Dyrson Rodrigues Alves Junior
> <dyrson_at_casasoft.com.br>
> (if that email address didn't require changing)
> On Thu, 24 Jun 1999 16:59:41 -0400, you wrote:
>
> >Dear gentleman,
> >
> >I am trying to call an external procedure but I got the following error:
> >
> >ORA-28576: lost RPC connection to external procedure agent
> >ORA-06512: at "ALUNO8.SQUARE_DE", line 0
> >ORA-06512: at line 4
> >
>
> Lost connection means the RPC was established but then went away. that
> indicates that the listener and tnsnames files are OK.
>
> The lost connection typically results from a 'crash' of your extproc routine
> (its seg faulting). If you are willing, you can try a small example I have
> below (you can also post your example PLSQL bindings and C code for us to take a
> look at since thats where the problem lies):
>
> Here is an example routine -- the whole routine -- with some convienence
> functions I put into ALL of my extprocs for error handling and debuging:
>
> ==================================================================================
> #include <stdio.h>
> #include <stdlib.h>
> #include <stdarg.h>
> #include <errno.h>
> #include <string.h>
> #include <time.h>
>
> #ifndef OCI_ORACLE
> # include <oci.h>
> #endif
>
> /*
> * these following variables and function are used to print debug trace
> * files. You will enable the generation of trace files from my extproc
> * by putting:
>
> (SID_DESC =
> (SID_NAME = extproc)
> (ORACLE_HOME = /d01/home/oracle81)
> (PROGRAM = extproc)
> (ENVS=DEBUG_MODULES=all,DEBUG_FILE=/tmp/ext_proc.log)
> )
>
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> * in your listener.ora file. the line that reads (ENVS=....) turns tracing
> * on in my extproc (my debugf routine looks for it to be set). You tell
> * this extproc what file to trace to in this line as well. this shows how
> * to set up an environment for extprocs -- just put the VAR=VALUE,VAR=VALUE
> * in the listener.ora file as above...
> */
>
> static int currLineNo;
> static char * currFileName;
>
> void _debugf( char * fmt, ... );
>
> #define debugf \
> currFileName=__FILE__, \
> currLineNo=__LINE__, \
> _debugf
>
> /*
> * the raise_application_error macro is a convienence routine i use to
> * propagate an error back to the calling pl/sql routine. It works alot
> * like 'printf' would in that it takes a format string and var args.
> * you might call it like this:
>
> if ( some_condition )
> raise_application_error( ctx, -20001, "Bummer, error" );
>
> * that would cause the exception to go back to the client right away,
> * your code will be exited from (the macro does a return). See below
> * for an example use of this (in the code)
> */
>
> #define raise_application_error return raise_application_error_x
>
> static long raise_application_error_x( OCIExtProcContext * ctx,
> int errCode,
> char * errMsg, ...)
> {
> char msg[8192];
> va_list ap;
>
> va_start(ap,errMsg);
> vsprintf( msg, errMsg, ap );
> va_end(ap);
>
> debugf( "raise application error( %d, %s )", errCode, msg );
> OCIExtProcRaiseExcpWithMsg(ctx,errCode,msg,strlen(msg));
> return -1;
> }
>
> /*
> * convienence routine for printing out strings and numbers passed to
> * your extproc routine. I like to put at the head of each routine
> * something like:
>
> debugf( "entering ROUTINE_NAME" );
> debug_str( "parm1 = ", parm1_i, parm1, parm1_l );
> debug_num( "parm2 = ", parm2_i, parm2 );
> *
> * that way, if i enable trace, i get to see all of my inputs in the trace
> * file
> */
>
> static void debug_str( char * msg, short ind, char * data, int len )
> {
> if ( ind ) { debugf( "%s is NULL", msg ); }
> else { debugf( "%s (%d bytes) = %.*s", msg, len, len, data ); }
> }
>
> static void debug_num( char * msg, short ind, int data )
> {
> if ( ind ) { debugf( "%s is NULL", msg ); }
> else { debugf( "%s = %d", msg, data ); }
> }
>
> /*
> * here is the routine. everything above and below this routine is
> * 'boilerplate' (reusable) code. I always pass the context into the
> * routine for raise_application_error. I also ALWAYS pass the indicator
> * so I can look for NULLS. This function is set up to accept:
>
> - the calling context
> - a string along with its indicator variable and length
> - a pointer to a short so I can tell the caller whether the return
> value is 'set' or NULL. If I set *return_indicator to 1, the return
> value is ignored and NULL is returned. set it to zero and the return
> value is returned
>
> */
>
> long
> printhello( OCIExtProcContext * ctx,
> char * p_uname, short p_uname_i, int p_uname_l,
> short * return_indicator )
> {
> FILE * fp;
>
> debugf( "enter printhello" );
> debug_str( "p_uname", p_uname_i, p_uname, p_uname_l );
>
> if ( (fp=fopen("/tmp/hellofile.txt", "w")) == NULL )
> {
> raise_application_error( ctx, -20001, "error opening file %d (%s)",
> errno, strerror(errno) );
> }
> debugf( "opened file" );
>
> if ( p_uname_i != 0 )
> {
> fprintf( fp, "Your argument was NULL\n" );
> }
> else
> {
> fprintf( fp, "You sent me '%s'\n", p_uname );
> }
> fclose( fp );
>
> *return_indicator = 0;
> return 1234;
> }
>
> /*
> * this is the real implementation of 'debugf'
> */
>
> /* ------------------------------------- */
> #undef debugf
>
> void _debugf( char * fmt, ... )
> {
> va_list ap;
> FILE * fp;
> char * debug_modules = getenv( "DEBUG_MODULES" );
> char * debug_file = getenv( "DEBUG_FILE" );
> time_t theTime = time(NULL);
> char buffer[25];
>
> if ( !debug_modules ) return;
>
> if ( (fp = fopen( debug_file, "at" )) == NULL ) fp = stderr;
>
> strftime( buffer, sizeof(buffer), "%y%m%d %H%M%S GMT", gmtime(&theTime) );
>
> fprintf( fp, "%s (%13s,%3d) ", buffer, currFileName, currLineNo );
> va_start(ap,fmt);
> vfprintf( fp, fmt, ap );
> va_end(ap);
> fprintf( fp, "\n" );
>
> if ( fp != stderr ) fclose(fp);
> }
>
> ==================================================================================
>
> And here is the makefile I used (assuming you save the above source code to
> extproc.c)
>
> ==================================================================================
> MAKEFILE= $(ORACLE_HOME)/rdbms/demo/demo_rdbms.mk
>
> INCLUDE= -I$(ORACLE_HOME)/rdbms/demo \
> -I$(ORACLE_HOME)/rdbms/public \
> -I$(ORACLE_HOME)/plsql/public \
> -I$(ORACLE_HOME)/network/public
>
> all: extproc.so
>
> include $(MAKEFILE)
>
> extproc.so: extproc.c extproc.o
> $(MAKE) -f $(MAKEFILE) extproc_callback \
> SHARED_LIBNAME=extproc.so OBJS="extproc.o"
>
> CFLAGS= -g -I. $(INCLUDE) -Wall
> ==================================================================================
>
> This is my extproc.sql file to create the bindings to C
>
> ==================================================================================
> CREATE OR REPLACE PACKAGE my_extproc_pkg
> as
> function printhello( p_uname in varchar2 ) return binary_integer;
> pragma restrict_references( printhello, rnds, wnds, rnps, wnps );
>
> pragma restrict_references( my_extproc_pkg, rnds, wnds, rnps, wnps );
> end;
> /
> show errors
>
> drop library my_extproc_lib
> /
> create library my_extproc_lib as '/export/home/tkyte/beehive/examp/extproc.so'
> /
>
> CREATE OR REPLACE PACKAGE BODY my_extproc_pkg
> as
>
> function printhello( p_uname in varchar2 ) return binary_integer
> is external
> name "printhello"
> library my_extproc_lib
> language C
> with context
> parameters ( CONTEXT,
> p_uname STRING,
> p_uname INDICATOR short,
> p_uname LENGTH int,
> RETURN INDICATOR short );
>
> end;
> /
> ==================================================================================
>
> Notice:
>
> - in the body, we:
> - name "printhello" -- important to quote that name AND make sure
> -- you have a function named that in the .so
> -- file. you had main in the .so but printhello
> -- in the body....
>
> - with context -- you pretty much ALWAYS want to send the context
> -- if you want to be able to do error handling
>
> - p_uname INDICATOR -- important to send null indicators for ALL variables.
> -- if not and a pl/sql routine gives you a NULL, you
> -- will NOT be able to detect it.
>
> - p_uname LENGTH -- lengths are always sent with strings. You need to
> -- have a variable in your c routine to accept it
>
> - RETURN INDICATOR -- if you want to be able to return NULL or a value, you
> -- must include this.
>
> So, now that I've done the above, I can run:
>
> SQL> select my_extproc_pkg.printhello( 'Hello there' ) from dual
> 2 /
>
> MY_EXTPROC_PKG.PRINTHELLO('HELLOTHERE')
> ---------------------------------------
> 1234
>
> and it returns 1234 as expected. Additionally:
>
> $ ls -l /tmp/hellofile.txt
> -rw-rw-r-- 1 oracle81 26 Feb 18 08:23 /tmp/hellofile.txt
>
> $ cat /tmp/hellofile.txt
> You sent me 'Hello there'
>
> and if I set up debugging in the listener.ora file I can also see:
>
> $ ls -l /tmp/ext_proc.log
> -rw-rw-r-- 1 oracle81 176 Feb 18 08:25 /tmp/ext_proc.log
>
> $ cat /tmp/ext_proc.log
> 990218 132509 GMT ( extproc.c,126) enter printhello
> 990218 132509 GMT ( extproc.c, 94) p_uname (11 bytes) = Hello there
> 990218 132509 GMT ( extproc.c,134) opened file
>
> you will find this *extremely* useful to debug your extprocs. I would suggest
> you heavily instrument the code with debugf calls to aid you in fixing
> problems....
>
> In the event you hit an 'rpc' error in the next steps (eg: the listner is
> configured wrong), the following might help as well:
>
> You need to setup the listener.ora and tnsnames.ora file for extprocs.
>
> Here is a sample listener.ora file:
>
> ----------------------------------------------------
> LISTENER =
> (ADDRESS_LIST =
> (ADDRESS=(PROTOCOL=IPC)(KEY=oracle81.world))
> (ADDRESS=(PROTOCOL=TCP)(Host=sheepdog)(Port=1521))
> )
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (GLOBAL_DBNAME=sheepdog.us.oracle.com)
> (ORACLE_HOME= /d01/home/oracle81)
> (SID_NAME = oracle81)
> )
> (SID_DESC =
> (SID_NAME = extproc)
> (ORACLE_HOME = /d01/home/oracle81)
> (PROGRAM = extproc)
> #(ENVS=DEBUG_MODULES=all,DEBUG_FILE=/tmp/test/ext_proc.log)
> )
> )
> STARTUP_WAIT_TIME_LISTENER = 0
> CONNECT_TIMEOUT_LISTENER = 10
> ----------------------------------------------------
>
> The important things in the listener file for extprocs:
>
> o ADDRESS=(PROTOCOL=IPC)(KEY=oracle81.world))
>
> set up an IPC based listener. remember the value of the KEY= (you can make it
> whatever you want, just remember what it is)
>
> o (SID_DESC=(SID_NAME=extproc)
>
> remember that SID_NAME, call it extproc.
>
> o (ENVS=DEBUG_MODULES=all,DEBUG_FILE=/tmp/test/ext_proc.log)
>
> if you want a specific set of environment variables setup for your extproc
> routines, put them here, separated by commas. The above for example, sets up 2
> environment variables for the extproc servcies, DEBUG_MODULES and DEBUG_FILE.
> Since extprocs run detached from a console and are a little touchy to debug,
> having copious amounts of 'tracing' that you can enable/disable via the
> environment is suggested (by me)...
>
> The next file is the tnsnames.ora file, it needs an entry like:
>
> ---------------------------------------------------
> extproc_connection_data =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = IPC)(KEY = oracle81.world))
> (CONNECT_DATA = (SID = extproc))
> )
> --------------------------------------------------
>
> Important things:
>
> o (ADDRESS = (PROTOCOL = IPC)(KEY = oracle81.world))
>
> should be the same as was in the listener.ora file....
>
> o (CONNECT_DATA = (SID = extproc))
>
> the sid must match the sid in the (SID_DESC=(SID_NAME=extproc) from the
> listener.ora....
>
> Also, if your sqlnet.ora specifies some default domain, it needs to be on the
> tnsnames entry. So if you have a sqlnet.ora with stuff like:
>
> sqlnet.authentication_services=(none)
> names.directory_path = (TNSNAMES, HOSTNAME)
> names.default_domain = world
> name.default_zone = world
> automatic_ipc = off
>
> in it, that would be extproc_connection_data.world -- not just
> extproc_connection_data.
>
> hope this helps...
>
> >I followed every step described in the bulletin PLSQL:Oracle8 External
> >Procedure Calls. So I donīt understand what is the problem.
> >
> >I am using HP-UX B.10.20 with Oracle 8.0.4.
> >
> >The tnsnames.ora and listener.ora of my server are below. Thanks in
> >advance.
> >
> ># Filename: Tnsnames.ora
> >#
> >extproc_connection_data =
> > (DESCRIPTION =
> > (ADDRESS = (PROTOCOL = IPC)(KEY = O8))
> > (CONNECT_DATA = (SID = extproc))
> > )
> >
> >O8 =
> > (DESCRIPTION =
> > (ADDRESS = (PROTOCOL= TCP)(Host= oaec)(Port= 1521))
> > (CONNECT_DATA = (SID = O8))
> > )
> >
> >
> ># Filename: Listener.ora
> >#
> >LISTENER =
> > (ADDRESS_LIST =
> > (ADDRESS= (PROTOCOL= IPC)(KEY= O8))
> > (ADDRESS= (PROTOCOL= IPC)(KEY= PNPKEY))
> > (ADDRESS= (PROTOCOL= TCP)(Host= 144.23.10.5)(Port= 1521))
> > (ADDRESS= (PROTOCOL= IPC)(KEY= extproc))
> > )
> >SID_LIST_LISTENER =
> > (SID_LIST =
> > (SID_DESC =
> > (ORACLE_HOME= /u01/oracle8/product/8.0.4)
> > (SID_NAME = O8)
> > )
> > (SID_DESC =
> > (SID_NAME = extproc)
> > (ORACLE_HOME = /u01/oracle8/product/8.0.4)
> > (PROGRAM = extproc)
> > )
> > )
> >STARTUP_WAIT_TIME_LISTENER = 0
> >CONNECT_TIMEOUT_LISTENER = 60
> >TRACE_LEVEL_LISTENER = off
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
--
+-------------------------------+----------------------------------+ | Dyrson Rodrigues Alves Junior | Casa de Software | | Analista de Sistemas | Oracle Approved Education Center | | mailto:dyrson@casasoft.com.br | http://www.casasoft.com.br | +-------------------------------+----------------------------------+Received on Fri Jun 25 1999 - 09:23:20 CDT
 |
 |