Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Setting up and calling external procedures
A copy of this was sent to "Nicholls, Pamela" <Pamela.Nicholls_at_nortel-dasa.de>
(if that email address didn't require changing)
On Fri, 18 Jun 1999 11:25:14 +0200, you wrote:
>Hello,
>
>I need help! I am trying call my external procedure written in 'c'.
>
>Does someone have an example of the tnsnames, listener and the library
>and c program? Any details would be greatly appreciated.
>
>Thanks in advance,
>p.
>
>pamela.nicholls_at_nortel-dasa.de
Here is an example I cut from another posting i made. It shows some 'defensive' programming I do with extprocs via 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);
}
>
> I created a shared library using:
> gcc -I$ORACLE_HOME/dbms/demo -I$ORACLE_HOME/rdbms/public
> -I$ORACLE_HOME/plsql/public -I$ORACLE_HOME/network/public -c printhello.c
>
> make -f demo_rdbms.mk extproc_callback SHARED_LIBNAME=my_lib.so
> OBJS=printhello.o
>
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
> All that I am trying to do is write to a file.
>
> I then created the Oracle library and defined my function:
>
> create library MY_LIB as 'my_lib.so';
>
> CREATE OR REPLACE FUNCTION myfunc
> (uname IN VARCHAR2)
> RETURN INTEGER
> AS EXTERNAL LIBRARY MY_LIB
> NAME printhello
> LANGUAGE c
> CALLING STANDARD c
> PARAMETERS (uname);
>
In order to call this from SQL, we need to specify the purity (you must want to call it from a SELECT instead of just going:
SQL> execute dbms_output.put_line( myfunc('some string') )
else you would not get the error you are getting.... )
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:
> I tried using the function, but it gives an error:
>
> SQL> select myfunc('WriteMe') from dual;
> select myfunc('WriteMe') from dual
> *
> ERROR at line 1:
> ORA-06571: Function MYFUNC does not guarantee not to update database
>
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....
> I have checked with our DBA, and we have a listener dedicated to
> external procedure calls. What am I doing wrong?
>
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...
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--