| 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
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
/*
* 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
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
#define raise_application_error return raise_application_error_x
static long raise_application_error_x( OCIExtProcContext * ctx,
int errCode,
char * errMsg, ...)
{
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
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)
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
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:
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:
(GLOBAL_DBNAME=sheepdog.us.oracle.com)
(ORACLE_HOME= /d01/home/oracle81)
(SID_NAME = oracle81)
(SID_NAME = extproc)
(ORACLE_HOME = /d01/home/oracle81)
(PROGRAM = extproc)
#(ENVS=DEBUG_MODULES=all,DEBUG_FILE=/tmp/test/ext_proc.log)
)
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:
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 Received on Fri Jun 25 1999 - 06:12:04 CDT
 |
 |