That function maps to a C routine with a prototype like this:

Maps to a C function with the following prototype:
OCINumber *sh
(
OCIExtProcContext *, /* 1 : With-Context */
char *, /* 2 : PS_CMD */
sb4 , /* 3 : PS_CMD (Length) */
int * /* 4 : RETURN (Indicator) */
);

You have short, where you should have sb4 (4 bytes, not 2 bytes). Also, you have short * for return indicator, that is wrong as well.

You are not setting the return indicator at all -- thats wrong as well.

Lastly -- you are saying the function returns a NUMBER. Well, that would map to a C Datatype of "OCINumber *" -- that is where the grievous error is in this case! You are returning the number 199 which, when we dereference as a pointer, causes an immediate seg-fault!

If you have my book -- i spend about 70 pages on this topic, going over everything you need to know (if you are interested)

Your quick fix (after fixing the INPUT parameters!) is to return the plsql type BINARY_INTEGER. That is mapped to the C int type which is what you are returning....

how are you calling it?

i created a /test/temp.ini with:


[tkyte@localhost test2]$ cat /tmp/test.ini
debugf = true

[tkyte@localhost test2]$ od -c /tmp/test.ini
0000000   d   e   b   u   g   f       =       t   r   u   e  \n  \n
0000017


and then:

ops$tkyte@ORA920LAP> select getdummy from dual;

GETDUMMY
---------------------------------------------------
Success.



worked first time...
 

well, they closed the bug because of a bug in the test case:

char *getDummy( OCIExtProcContext *ctx, short *return_i, int *return_l )
{
int *myCtx;
char dummy[50];
....

}
strcpy(dummy,"Success.");
*return_l = strlen(dummy);
*return_i = OCI_IND_NOTNULL;
return dummy;
}

you are returning a pointer to a stack variable!!!

that is why that bug was "not a bug"


but -- can you put a couple of

fopen( 'a')
fprintf ( 'im on line %d', __LINENO__ )
fclose

debug lines in there to see which call exactly is failing?

would you mind fopening /tmp/test.ini , freading the first 1k of it and fwriting what you get into the debug? just to see what we see?

i'll have to try later on 817 (only 9ir2 right now) but it'll be solaris

you need to

n = fread()
fwrite( ..., n, ... )

and do it in the EXTPROC -- i wanna see what it sees

i'll try later.

Ok, lets try this piece of code. this is a generic template I use for all of my external procedures (if you have my book expert one on one Oracle -- this is from there)



include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>
#include <time.h>
#include <string.h>
#include <errno.h>
#include <ctype.h>

#include <oci.h>

#ifdef WIN_NT
#define INI_FILE_NAME "c:\\temp\\extproc.ini"
#else
#define INI_FILE_NAME "/tmp/extproc.ini"
#endif

#define strupr(a) {char * cp; for(cp=a;*cp;*cp=toupper(*cp), cp++);}

/*
* This is our "context". It holds what normally might be global variables
* in a typical program. We cannot (should not) use global variables in an
* external procedure. Also, since static data will be reinitialized
* between calls -- globals would not work correct anyway. We will use the
* OCI context management API calls to get and set a global context for
* our extproc. You would add any state variables you needed to preserve
* from call to call in this structure below.
*
* the debugf_* variables are initialized from a parameter file. In our
* init() routine, we will read the parameter file and set these values.
* you may add your own parameters to this structure as well and set them
* in init() as I do.
*
* curr_lineno and curr_filename are set by the debugf macro below so when
* we create a trace file, I can tell you what line number and source code
* file a message was generated from
*
*/

typedef struct myCtx
{
OCIExtProcContext * ctx; /* Context passed to all external procs */
OCIEnv * envhp; /* OCI environment handle */
OCISvcCtx * svchp; /* OCI Service handle */
OCIError * errhp; /* OCI Error handle */

int curr_lineno;
char * curr_filename;

ub1 debugf_flag;
char debugf_path[255];
char debugf_filename[50];

/* add your own state variables here... */
}
myCtxStruct;


/*
* Here is the implementation of our "debugf" routine. It is used to
* instrument the code and aid in debugging runtime issues.
*
* It works much like the C printf function (well, exactly like it)
* in that you send a C format and then a varying number of arguments
* to be formatted and printed. We add to it the Year, Month, Day,
* Hour, Minute, Second in GMT to each trace record as well as the name
* of the C source code file and the line number.
*
* Note that in order to be as OS independent as possible, we use the
* OCI API's to write to files. The File API's are expected to have
* been initialized in the init() routine alread and will be closed out
* in the term() routine.
*/

void _debugf( myCtxStruct * myCtx, char * fmt, ... )
{
va_list ap;
OCIFileObject * fp;
time_t theTime = time(NULL);
char msg[8192];
ub4 bytes;

if ( OCIFileOpen( myCtx->envhp, myCtx->errhp, &fp,
myCtx->debugf_filename,
myCtx->debugf_path,
OCI_FILE_WRITE_ONLY, OCI_FILE_APPEND|OCI_FILE_CREATE,
OCI_FILE_TEXT ) != OCI_SUCCESS ) return;

strftime( msg, sizeof(msg),
"%y%m%d %H%M%S GMT ", gmtime(&theTime) );
OCIFileWrite( myCtx->envhp, myCtx->errhp, fp, msg, strlen(msg), &bytes );

va_start(ap,fmt);
vsprintf( msg, fmt, ap );
va_end(ap);
strcat( msg,"\n");

OCIFileWrite( myCtx->envhp, myCtx->errhp, fp, msg, strlen(msg), &bytes );
OCIFileClose( myCtx->envhp, myCtx->errhp, fp );
}

/*
* this macro is a more convienent way to use debugf. Instead of have
* to pass the __LINE__, __FILE__ each time we call -- we just code:
*
* debugf( myCtx, "This is some format %s", some_string );
*
* and this macro will set them in our context and then call _debugf for us.
*
*/
void _debugf( myCtxStruct * myCtx, char * fmt, ... );
#define debugf \
if ((myCtx!=NULL) && (myCtx->debugf_flag)) \
myCtx->curr_lineno = __LINE__, \
myCtx->curr_filename = __FILE__, \
_debugf


/*
* This routine works like the PLSQL builtin by the same name. It
* sets an application defined error message and error code. It also
* works like the C printf routine in that you pass to it a C format
* string and then a varying number of arguments
*
* The resulting string is limited to 8k in size. Note the call
* to debugf. Debugf is the macro above, we will use it throughout
* the code to 'instrument' it.
*/

static int raise_application_error( myCtxStruct * myCtx,
int errCode,
char * errMsg, ...)
{
char msg[8192];
va_list ap;

va_start(ap,errMsg);
vsprintf( msg, errMsg, ap );
va_end(ap);

debugf( myCtx, "raise application error( %d, %s )", errCode, msg );
if ( OCIExtProcRaiseExcpWithMsg(myCtx->ctx,errCode,msg,0) ==
OCIEXTPROC_ERROR )
{
debugf( myCtx, "Unable to raise exception" );
}
return -1;
}



/*
* This is a convienence routine to allocate storage for an error message
* and return it. Note that the type of storage we allocate is CALL based,
* hence, when we return from the extproc, OCI will automatically free it
* for us.
*
* This routine removes the trailing newline from the error message as well
*/
static char * lastOciError( myCtxStruct * myCtx )
{
sb4 errcode;
char * errbuf = (char*)OCIExtProcAllocCallMemory( myCtx->ctx, 256 );

strcpy( errbuf, "unable to retrieve message\n" );
OCIErrorGet( myCtx->errhp, 1, NULL, &errcode, errbuf,
255, OCI_HTYPE_ERROR );
errbuf[strlen(errbuf)-1] = 0;
return errbuf;
}

/*
* Here is the init routine. This routine must be called very early
* in the execution of any external procedure using this template. It
* will
* o retrieve the OCI handles and set them in the context for us
* o retrieve our CONTEXT, if the context has not yet been allocated,
* it will allocate storage for our context and save it. The memory
* is allocated to last as long as our process lasts.
* o it will read and retrieve the values from the parameter file if
* they have not been. You can add more parameters of your own by:
* - adding elements the the myCtxStruct above
* - increment the counter sent into ExtractSetNumKeys appropriately
* - adding a call to ExtractSetKey
* - adding a call to ExtractTo* (string, bool, int, etc)
* o it will initialize the FILE apis via OCIFileInit(). This is crucial
* for the successful operation of debugf above.
*
* You may add other "init" type of calls. for example, if you choose to
* use the String Formatting Interface (similar to vsprintf() but in OCI)
* You could add a call to OCIFormatInit() here. You would add the
* corresponding OCIFormatTerm() call to term() below.
*
*/
static myCtxStruct * init( OCIExtProcContext * ctx )
{
ub1 false = 0;
myCtxStruct *myCtx = NULL;
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIError *errhp;
ub4 key = 1;


if ( OCIExtProcGetEnv( ctx, &envhp, &svchp, &errhp ) != OCI_SUCCESS )
{
OCIExtProcRaiseExcpWithMsg(ctx,20000,
"failed to get OCI Connection",0);
return NULL;
}
if ( OCIContextGetValue( envhp, errhp, (ub1*)&key, sizeof(key),
(dvoid**)&myCtx ) != OCI_SUCCESS )
{
OCIExtProcRaiseExcpWithMsg(ctx,20000,"failed to get OCI Context",0);
return NULL;
}

if ( myCtx == NULL )
{
if ( OCIMemoryAlloc( envhp, errhp, (dvoid**)&myCtx,
OCI_DURATION_PROCESS,
sizeof(myCtxStruct),
OCI_MEMORY_CLEARED ) != OCI_SUCCESS )
{
OCIExtProcRaiseExcpWithMsg(ctx,20000,
"failed to get OCI Memory",0);
return NULL;
}
myCtx->ctx = ctx;

myCtx->envhp = envhp;
myCtx->svchp = svchp;
myCtx->errhp = errhp;
if ( OCIContextSetValue( envhp, errhp,
OCI_DURATION_SESSION, (ub1*)&key,
sizeof(key), myCtx ) != OCI_SUCCESS )
{
raise_application_error(myCtx, 20000, "%s", lastOciError(myCtx));
return NULL;
}

if (( OCIExtractInit( envhp, errhp ) != OCI_SUCCESS ) ||
( OCIExtractSetNumKeys( envhp, errhp, 3 ) != OCI_SUCCESS ) ||
( OCIExtractSetKey( envhp, errhp, "debugf",
OCI_EXTRACT_TYPE_BOOLEAN,
0, &false, NULL, NULL ) != OCI_SUCCESS ) ||
( OCIExtractSetKey( envhp, errhp, "debugf_filename",
OCI_EXTRACT_TYPE_STRING,
0, "extproc.log",
NULL, NULL ) != OCI_SUCCESS ) ||
( OCIExtractSetKey( envhp, errhp, "debugf_path",
OCI_EXTRACT_TYPE_STRING,
0, "", NULL, NULL ) != OCI_SUCCESS ) ||
( OCIExtractFromFile( envhp, errhp, 0,
INI_FILE_NAME ) != OCI_SUCCESS ) ||
( OCIExtractToBool( envhp, errhp, "debugf", 0,
&myCtx->debugf_flag ) != OCI_SUCCESS ) ||
( OCIExtractToStr( envhp, errhp, "debugf_filename", 0,
myCtx->debugf_filename,
sizeof(myCtx->debugf_filename ) )
!= OCI_SUCCESS ) ||
( OCIExtractToStr( envhp, errhp, "debugf_path",
0, myCtx->debugf_path,
sizeof(myCtx->debugf_path ) )
!= OCI_SUCCESS ) ||
( OCIExtractTerm( envhp, errhp ) != OCI_SUCCESS ))
{
raise_application_error(myCtx, 20000, "%s", lastOciError(myCtx));
return NULL;
}
}
else
{
myCtx->ctx = ctx;
myCtx->envhp = envhp;
myCtx->svchp = svchp;
myCtx->errhp = errhp;
}
if ( OCIFileInit( myCtx->envhp, myCtx->errhp ) != OCI_SUCCESS )
{
raise_application_error(myCtx, 20000, "%s", lastOciError(myCtx));
return NULL;
}
return myCtx;
}

/*
* This must be called after any successful call to init() above. It
* should be the last thing you call in your routine before returning
* from C to SQL
*/
static void term( myCtxStruct * myCtx )
{
OCIFileTerm( myCtx->envhp, myCtx->errhp );
}


/*
* everthing above this is BOILERPLATE!!!
*/




/*
* With the string (the varchar) we'll use 2 parameters -- the
* indicator variable and the LENGTH field. This time, much like an
* OUT parameter, we set the length field to let the caller know how
* long the returned string is.
*
* Many of the same considerations apply for returning strings as above
* -- we'll allocate storage, set the indicator, supply the value and
* return it...
*/
#ifdef WIN_NT
_declspec (dllexport)
#endif
char * return_string
( OCIExtProcContext * ctx,
short * return_i,
int * return_l )
{
char * data_we_want_to_return = "Hello World!";

char * return_value;
myCtxStruct*myCtx;

if ( (myCtx = init( ctx )) == NULL ) return NULL;
debugf( myCtx, "Enter return String" );

return_value = (char *)OCIExtProcAllocCallMemory(ctx,
strlen(data_we_want_to_return)+1 );

if( return_value == NULL )
{
raise_application_error( myCtx, -20001, "%s", "no memory" );
}
else
{
*return_i = OCI_IND_NULL;
strcpy( return_value, data_we_want_to_return );
*return_l = strlen(return_value);
*return_i = OCI_IND_NOTNULL;
}
term(myCtx);
return return_value;
}



The Makefile I use is:

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

TGTDLL= extproc.so
OBJS = extproc.o

all: extproc.so

clean:
rm *.o

extproc.so: $(OBJS)
$(MAKE) -f $(MAKEFILE) extproc_callback \
SHARED_LIBNAME=$(TGTDLL) OBJS=$(OBJS)

CC=cc
CFLAGS= -g -I. $(INCLUDE) -Wall


but your command line should work as well... Then test.sql is:

create or replace library demoPassing
as '/usr/oracle/ora920/bin/extproc.so'
/

create or replace
function return_string return varchar2
as
language C name "return_string" library demoPassing
with context parameters
( CONTEXT, RETURN INDICATOR short, RETURN LENGTH int, RETURN STRING );

-- char *return_string
-- (
-- OCIExtProcContext *, /* 1 : With-Context */
-- short *, /* 2 : RETURN (Indicator) */
-- int * /* 3 : RETURN (Length) */
-- );
/


select return_string from dual;




i could not reproduce your issue on solaris with your code - could be a bug in the code -- if this code works, we'll have to see what is different about them

I would say this is a bug on AIX -- to do with parsing that file. That my code fails and then runs is expected cause I only parse that file once per session.


I do not have an AIX box anywhere to play with -- so my suggestion is

simplify the test case -- function returning an integer, just send the context, no indicators, minimal code.


something they can easily reproduce in house. If you like, you can put it here before filing with them and we'll eyeball it to make sure it is OK first.

NO GLOBALS -- they always cause suspicion.

OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
errbuf, (ub4) sizeof(errbuf),
OCI_HTYPE_ERROR);

shouldn't sizeof(errbuf) be 511? since you allocated 512 bytes?

but otherwise -- looks good.

that works if errbuf was defined as

char errbuf[255];


sizeof(errbuf) would be 255 -- telling us "255 bytes of storage for the error message"

if you however:

char * errbuf = call_to_allocate_memory();


sizeof( errbuf ) is now 4 (generally), even if you allocated 255 bytes.

your C code is "crashing".

(as stated in the original answer.... problem in your C code, not in plsql or anything else)

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:952229840241 <code>

use a java stored procedure.... option 6

you have the ability to very very very finely control which programs are callable and not -- something you don't have the capability to do with extprocs.

you can specify "this guy can run programs A and B" with java

with an extproc, you can specify the user extprocs run as -- by running a listener as that user, but you cannot limit the programs they may execute.

java can easily ftp files.

heck, plsql can. (smtp is for email).

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6379798216275#19264891198142 <code>


you can ftp files in/out of the database directly.

to save a binary file without using ftp they would be needed... to ftp a binary file in or out - it does not need it.

the example I had there was "saving a blob to disk" -- everything we need is there, the utl_file stuff isn't needed to get a file into the database via ftp (nor out actually). I showed that a binary interface...

yes, as long as you stick with blobs and clobs and use ftp to get/put to the file system -- it'll be just fine in 8i.

plsql records cannot be bound to 3gl structs/recs/whatever.

the only thing that can be bound to a plqsl record is another plsql records.


You can use object types "create type foo as object ( x int, y date );" and use those, but not a plsql record type

you can pass a foo - i only have demo of collections of simple types like collections of numbers, dates, and strings -- objects, I find them too "complicated" from the 3gl perspective -- but there are examples of binding to object types in the OCI guides -- that is all they are -- "object types"

you want to see about "ott"

</code> http://otn.oracle.com/pls/db92/db92.drilldown?remark=&word=ott&book=&preference= <code>

the object type translator as well.

Data cartridges are like an "application"
</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14289/dciwhatis.htm#sthref16 <code>

they are an implementation. They extend the functionality of the server by adding say a new datatype.

A data cartridge might well use an external procedure in it's implementation. You might choose to implement your data cartridge code in say the "C" language. The way to do that would be to code the "C" routines as external procedures which are now callable via the server.

So, a data cartridge is an implementation, that might use the database feature "external procedures"..

you would either

a) map it to scalars in a table, insert the components of the struct (my choice)
b) map it to a complex object type, hence create your own sql type.
c) store it in a blob - something I would never recommend, demonstrate, or even suggest.


A) would be my choice.

an object type coupled with functionality. like spatial does, like text does, like image does.....

You'll need a different C function call - you cannot call any arbitrary function - it has to have datatypes we can map to, using SQL types.


If you have access to Expert One on One Oracle - in the chapter on external procedures, you'll find a full example, but in short, it would look like this:

tkyte@TKYTE816> create or replace type numArray as table of number
  2  /
Type created.


...
 12
 13      procedure pass( p_in in numArray, p_out out numArray );
 14
.....

100      procedure pass( p_in in numArray, p_out out numArray )
101      as
102      language C name "pass_numArray" library demoPassing
103      with context parameters
104      ( CONTEXT,
105        p_in  OCIColl, p_in  INDICATOR short,
106        p_out OCIColl, p_out INDICATOR short );
107
108       -- void pass_numArray
109       --   (
110       --  OCIExtProcContext  *,  /*   1 : With-Context  */
111       --            OCIColl  *,  /*   2 : P_IN  */
112       --              short   ,  /*   3 : P_IN (Indicator) */
113       --            OCIColl **,  /*   4 : P_OUT  */
114       --              short  *   /*   5 : P_OUT (Indicator) */
115       --   );
116
......
#ifdef WIN_NT
_declspec (dllexport)
#endif
void pass_numArray
 ( OCIExtProcContext * ctx        /* CONTEXT */,
   OCIColl *           p_in       /* OCICOL  */,
   short               p_in_i     /* INDICATOR short */,
   OCIColl **          p_out      /* OCICOL  */,
   short *             p_out_i    /* INDICATOR short */
 )
{
ub4        arraySize;
double     tmp_dbl;
boolean    exists;
OCINumber *ocinum;
int        i;
myCtxStruct*myCtx;

    if ( (myCtx = init( ctx )) == NULL ) return;
    debugf( myCtx,  "Enter Pass numArray" );

    if ( p_in_i == OCI_IND_NULL )
    {
        raise_application_error( myCtx, ERROR_ARRAY_NULL,
                                       "Input array was NULL" );
    }
    else

    if ( OCICollSize( myCtx->envhp, myCtx->errhp,
                      p_in, &arraySize ) != OCI_SUCCESS )
    {
        raise_application_error(myCtx,ERROR_OCI_ERROR,
                                "%s",lastOciError(myCtx));
    }
    else
    {
        debugf( myCtx,  "IN Array is %d elements long", arraySize );

        for( i = 0; i < arraySize; i++ )
        {
            if (OCICollGetElem( myCtx->envhp, myCtx->errhp, p_in, i,
                              &exists, (dvoid*)&ocinum, 0 ) != OCI_SUCCESS )
            {
                raise_application_error(myCtx,ERROR_OCI_ERROR,
                                        "%s",lastOciError(myCtx));
                break;
            }
            if (OCINumberToReal( myCtx->errhp, ocinum,
                                 sizeof(tmp_dbl), &tmp_dbl ) != OCI_SUCCESS )
            {
                raise_application_error(myCtx,ERROR_OCI_ERROR,"%s",
                                        lastOciError(myCtx));
                break;
            }
            debugf( myCtx,  "p_in[%d] = %g", i, tmp_dbl );
            if ( OCICollAppend( myCtx->envhp, myCtx->errhp, ocinum, 0,
                                *p_out ) != OCI_SUCCESS )
            {
                raise_application_error(myCtx,ERROR_OCI_ERROR,
                                        "%s",lastOciError(myCtx));
                break;
            }
            debugf( myCtx,  "Appended to end of other array" );
        }
        *p_out_i = OCI_IND_NOTNULL;
    }
    term(myCtx);
}

You'll be passing a collection - you will need to write a wrapper C function that takes the collection, allocates a long array, fills it, invokes existing C function and then repackages the answer back into the collection.

ora_ffi - the "foreign function interface" is for forms/reports and the like - in the middle tier.

I haven't ever used it - but it'll have similar issues - the C concept of an array with long * is a bit different then the plsql representation of a plsql index by table, collection or varray.

What I'm trying to say is that not just any C function is callable from PLSQL, you might have to implement an intermediate C function that marshalls the data between PLSQL and C.

http://asktom.oracle.com/pls/ask/search?p_string=28575

you would need the Oracle software installed on the remote machine - the listener, the database and all.

otn.oracle.com has a discussion forum all about forms and using forms.... try there

"U" isn't available, "U" is dead as far as I know. Look it up, it is true.

http://en.wikipedia.org/wiki/U_of_Goryeo


I have no clue what n3sgus.dll is, does, or where it came from. That would be something for you to figure out.