You Asked

Hi Tom,

I am trying to call a C external routine from PL/SQL stored procedure using one IN OUT parameter. My objective is to pass a string from PL/SQL, concatenate this string with a second string inside the C routine and return the concatenated string back to PL/SQL.
PASSASTRING is the PL/SQL wrapper to the C dll.
When I execute my procedure CALLPASSASTRING, the following error shows up:

The following error has occurred:

ORA-06525: Length Mismatch for CHAR or RAW data
ORA-06512: at "SAMPLE.PASSASTRING", line 0
ORA-06512: at "SAMPLE.CALLPASSASTRING", line 14
ORA-06512: at line 2

I am using Oracle 8i and MSVC++ to build my C dll. I selected Win32 Dynamic Link Library and Simple dll as my options in MSVC++ IDE.

I have tried to follow your example that uses one IN and one IN OUT parameter modifying the function prototype and C code. I would like to get this one working with only one IN OUT parameter. I am missing something and I am not able to figure out where I am going wrong.

Here is my stored procedure CALLPASSASTRING:
---------------
CREATE OR REPLACE procedure CallPassAString
as
c char(100);
BEGIN
c:= 'WhiteHouse';
PassAString(c);
dbms_output.put_line(c);
END;
/
---------------
Here is my PL/SQL wrapper:
----------------------------
CREATE OR REPLACE procedure PassAString (p_out in out char)
as
language C
name "GetName"
library stringLib
with context
parameters ( CONTEXT,
p_out STRING,
p_out INDICATOR short,
p_out MAXLEN int);
/
------------------------------

and here is my C dll code:
----------------------------------

// ConcatEx.cpp : Defines the entry point for the DLL application.

#include <iostream.h>
using namespace std;
#include <fstream.h>

#include "stdafx.h"
#include <stdio.h>
#include <string>
#include <oci.h>
#include <ociextp.h>
#include <stdlib.h>

BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved
)
{
return TRUE;
}

extern "C" void __declspec(dllexport) GetName(OCIExtProcContext * ctx,
/* IN OUT PARAMETER */
char * p_ostr,
short * p_ostr_i,
int * p_ostr_l,
int * p_ostr_ml)

{
char * lstr = "Testing...";

int newlen = strlen(p_ostr) + strlen(lstr) + 2;

char * tmpstr;
tmpstr = (char *) malloc(newlen);

strcpy(tmpstr,p_ostr);
strcat(tmpstr,lstr);

//free(p_ostr); //TOAD hangs

/*strcpy(p_ostr, tmpstr);*/

int tmpstr_l = strlen(tmpstr)+1;

if (*p_ostr_i == OCI_IND_NOTNULL)
{
memmove( p_ostr, tmpstr, tmpstr_l );
*p_ostr_l = tmpstr_l;
*p_ostr_ml= tmpstr_l;

}
}
---------------------------------------------

Thank you very much for your help. - Inayat

and Tom said...

the problems here are a couple -- using CHAR is strong (vehemently) discouraged by me.

You are passing a fixed width string that is 100 bytes always. CHAR's are blank padded. Always use VARCHAR2, never use CHAR.

So, your input length is already at your max length! it would be physically impossible to concatenate here. Even in PLSQL:

ops$tkyte@ORA9IR2> declare
2 c char(100) := 'Hello World';
3 begin
4 c := c || 'x';
5 end;
6 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 4

so, don't use char, use varchar.

And then -- don't free OUR MEMORY. (in fact, do not use free, malloc, etc in an extproc. use the provided memory allocation functions and then free only that which you allocate yourself!) Toad isn't hanging, you've destroyed the extproc by doing that free. C is very very picky about memory managment.

Here is some sample code, I'm using the template I used in Expert One on One Oracle (gives you a debugf routine, raise_application_error, state mgmt and so on). It appears you have access to it since you are using some of my conventions there. This routine expects on IN OUT string. If you do not have my template (downloadable from www.apress.com), you can just read about the OCI functions that raise errors and replace the raise application errors with that:
...

#define ERROR_OCI_ERROR 20001
#define ERROR_STR_TOO_SMALL 20002
#define ERROR_RAW_TOO_SMALL 20003
#define ERROR_CLOB_NULL 20004
#define ERROR_ARRAY_NULL 20005

/*
* Passing strings back and forth is a little differnt then
* Dates and Numbers. It is a little easier perhaps. We get normal C
* Null terminated strings. On the other hand, we must (as always in
* C) be mindful of potential buffer overwrites. Therefore, we always
* have the extproc pass in the MAXLEN variable with every string that
* is an IN/OUT or OUT parameter. Without it -- we would not have any
* sure way of knowing the max length.
*
* Notice the lack of an OCIExtProcGetEnv call -- it is simply not
* needed for this routine since we are not making any complex
* OCI calls as we did above.
*/

#ifdef WIN_NT
_declspec (dllexport)
#endif
void pass_str
( OCIExtProcContext * ctx /* CONTEXT */,

char * p_ostr /* STRING */,
short * p_ostr_i /* INDICATOR short */,
int * p_ostr_ml /* MAXLEN int */
)
{
myCtxStruct*myCtx;
char * stringToAdd = " ta dah";

if ( (myCtx = init( ctx )) == NULL ) return;
debugf( myCtx, "Enter Pass Str" );
/*
* Now, we will make the output string = upper(input string)
* if the output buffer is big enough and
*/
if ( *p_ostr_i == OCI_IND_NOTNULL )
{
if ( (strlen(p_ostr) + strlen(stringToAdd) + 1) <= *p_ostr_ml )
strcat( p_ostr, stringToAdd );
else
raise_application_error
( myCtx, ERROR_STR_TOO_SMALL, "String too small" );
}
else
{
if ( (strlen(stringToAdd) + 1) <= *p_ostr_ml )
strcpy( p_ostr, stringToAdd );
else
raise_application_error
( myCtx, ERROR_STR_TOO_SMALL, "String too small" );
}
term(myCtx);
}

Now, you just need plsql like this:
ops$tkyte@ORA9IR2> create or replace
2 procedure pass( p_out in out varchar2 )
3 as
4 language C name "pass_str" library demoPassing
5 with context parameters
6 ( CONTEXT,
7 p_out STRING, p_out INDICATOR short, p_out MAXLEN int );
8
9 -- void pass_str
10 -- (
11 -- OCIExtProcContext *, /* 1 : With-Context */
12 -- char *, /* 4 : P_OUT */
13 -- short *, /* 5 : P_OUT (Indicator) */
14 -- int * /* 6 : P_OUT (Maxlen) */
15 -- );
16 /

Procedure created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x varchar2(100)
ops$tkyte@ORA9IR2> exec :x := 'Hello World';

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec pass( :x )

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> print x

X
--------------------------------------------------------------------------------------------------------------------------------
Hello World ta dah

ops$tkyte@ORA9IR2> exec :x := rpad( 'x', 95, 'x' )

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> exec pass( :x )
BEGIN pass( :x ); END;

*
ERROR at line 1:
ORA-20002: String too small
ORA-06512: at "OPS$TKYTE.PASS", line 0
ORA-06512: at line 1

and there you go.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Getting Oracle error mapping function

Inayat, December 27, 2003 - 8:39 pm UTC

I was pleasantly surprised to see your quick response.

Let me tell you that I am a newbie to C/C++. I earnestly request to you to 
please bear with me if you notice me doing something funny!, I assure you 
that I will take a class in C/C++ and also purchase your book Expert One On One
as a valuable reference for my department!

I want to clearly spell out what exactly I did so you would better be able to 
help me accomplish my goal.

I downloaded your template from apress.com.

1.     I created a new project in MSVC++ selecting Win32 DLL and Simple DLL as 
    my options from the IDE.
2.    I copied the function pass_str from your answer (to my question),and 
    the myCtx struct definition, the functions lastOciError, init, term,
    debugf, and raise_application_error into my new project.
3.    I got tons of type conversion compile errors that I fixed by type 
    casting them. Please see my comments to the right of the statements 
    where I have made such a change.
4.    I prefixed debugf with an _ (underscore) in your pass_str function code. 
    Did you mean not to have an underscore? There is a _debugf function but 
    there is no function called debugf.
5.    In the init function, I renamed the variable false as false1 as the 
    compiler wouldn't compile. I felt that the compiler was thinking it to 
    be a keyword as it appeared in blue font in MSVC++. Did you mean it to be 
    false and not something else? To have the code compiled, I also had to 
    prefix (void *) to false1  in the call to OCIExtractSetKey inside 
    the init function. Is this okay?

    When I executed the procedure, here is the error I get:

    SQL> variable x varchar2(100)
    SQL> exec :x := 'Hello World';
    
    PL/SQL procedure successfully completed.
    
    SQL> exec pass( :x );
    BEGIN pass( :x ); END;
    
    *
    ERROR at line 1:
    ORA-06521: PL/SQL: Error mapping function
    ORA-06522: Unable to load symbol from DLL
    ORA-06512: at "SAMPLE.PASS", line 0
    ORA-06512: at line 1


Now, here are my questions:
    

1.    The _debugf function shows a ... after fmt meaning we can pass addn'al 
    parameters to it. If I choose not to use additional parameters for now,
    could you please verify that the calls to _debugf are proper for use 
    with just the two shown myCtx and fmt?
    
        void _debugf( myCtxStruct * myCtx, char * fmt)

2.    I also notice a similar thing for the function raise_application_error. 
    For now I would like to have just the 3 parameters as below:

    static int raise_application_error(  myCtxStruct * myCtx,
                                     int           errCode, 
                                     char *        errMsg)

The bottom line is that I WANT TO SEE MY C CODE WORKING 100% that I could use 
with joy and cheer. 

I would be very thankful to you from the bottom of my heart if you could
please take a look at my C code and point out the mistakes if any, to make it 
working? 
I want to see "Hello World ta dah" on my screen! that would be awesome !!!

Thanks - Inayat

------------------------ attached C code compiled with MSVC++ ---------------

I HAVE PUT A COMMENT "added .... ABOVE EACH LINE WHERE I HAVE DONE TYPE-CASTING

-----------------------------------------------------------------------------

// TResponse.cpp : Defines the entry point for the DLL application.
//

#include "stdafx.h"
#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 -_FILE_NAME "c:\\temp\\extproc.ini"
#else
#define INI_FILE_NAME "/export/home/tkyte/src/demo_passing/extproc.ini"
#endif

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



BOOL APIENTRY DllMain( HANDLE hModule, 
                       DWORD  ul_reason_for_call, 
                       LPVOID lpReserved
                     )
{
    return TRUE;
}




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;



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

   //added (unsigned char *) for parameters 4 and 5 below    
    if ( OCIFileOpen( myCtx->envhp, myCtx->errhp, &fp, 
                      (unsigned char *)myCtx->debugf_filename,                                
                      (unsigned char *)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 );
}




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 );
    
    //added (unsigned char *) to parameter 3
   if (OCIExtProcRaiseExcpWithMsg(myCtx->ctx,errCode,(unsigned char *)msg,0)== 
                                                OCIEXTPROC_ERROR )
    {
      _debugf( myCtx,  "Unable to raise exception" );                            
    }
    return -1;
}




static char * lastOciError( myCtxStruct * myCtx )
{
sb4       errcode;
char      * errbuf = (char*)OCIExtProcAllocCallMemory( myCtx->ctx, 256 );

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




static myCtxStruct * init( OCIExtProcContext * ctx )
{
ub1          false1 = 0;    //225                                    
myCtxStruct *myCtx = NULL;                                        
OCIEnv      *envhp;                                            
OCISvcCtx   *svchp;                                            
OCIError    *errhp;                                            
ub4          key = 1;


    if ( OCIExtProcGetEnv( ctx, &envhp, &svchp, &errhp ) != OCI_SUCCESS )
    {
        //added (unsigned char *) to parameter 3
         OCIExtProcRaiseExcpWithMsg(ctx,20000,
                            (unsigned char *)"failed to get OCI Connection",0);     
         return NULL;
    }
    if ( OCIContextGetValue( envhp, errhp, (ub1*)&key, sizeof(key), 
                             (dvoid**)&myCtx ) != OCI_SUCCESS ) 
    {
        //added (unsigned char *) to parameter 3
        OCIExtProcRaiseExcpWithMsg(ctx,20000,
                (unsigned char *)"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 )
        {
            //added (unsigned char *) to parameter 3
            OCIExtProcRaiseExcpWithMsg(ctx,20000,
                          (unsigned char *)"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 ) ||
        //added (unsigned char *) to parameter 3
            ( OCIExtractSetKey( envhp, errhp, (unsigned char *)"debugf",      
                          OCI_EXTRACT_TYPE_BOOLEAN,
           //added (void *), removed the "&" prefixing false                          
                          0, (void *)false1, NULL, NULL ) != OCI_SUCCESS ) ||
               //added (unsigned char *) to parameter 3           
            (OCIExtractSetKey(envhp, errhp, (unsigned char *)"debugf_filename", 
                                OCI_EXTRACT_TYPE_STRING, 
                                0, "extproc.log", 
                                NULL, NULL ) != OCI_SUCCESS )  ||
               //added (unsigned char *) to parameter 3                 
            ( OCIExtractSetKey( envhp, errhp, (unsigned char *)"debugf_path",      
                                OCI_EXTRACT_TYPE_STRING, 
                                0, "", NULL, NULL ) != OCI_SUCCESS )  ||
            ( OCIExtractFromFile( envhp, errhp, 0,
              //added (unsigned char *) to parameter 4
                         (unsigned char *)INI_FILE_NAME ) != OCI_SUCCESS ) ||
              //added (unsigned char *) to parameter 3           
            ( OCIExtractToBool( envhp, errhp, (unsigned char *)"debugf", 0,      
                                &myCtx->debugf_flag ) != OCI_SUCCESS ) ||
              //added (unsigned char *) to parameter 3 and 5                  
            (OCIExtractToStr( envhp, errhp, (unsigned char *)"debugf_filename",     
                             0,(unsigned char *)myCtx->debugf_filename,            
                               sizeof(myCtx->debugf_filename ) )
                                                     != OCI_SUCCESS ) ||
              //added (unsigned char *) to parameter 3 and 5                                                                       
            ( OCIExtractToStr( envhp, errhp, (unsigned char *)"debugf_path",        
                               0, (unsigned char *)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;
}



static void term( myCtxStruct * myCtx )
{
    OCIFileTerm( myCtx->envhp, myCtx->errhp );
}


#define ERROR_OCI_ERROR     20001
#define ERROR_STR_TOO_SMALL 20002
#define ERROR_RAW_TOO_SMALL 20003
#define ERROR_CLOB_NULL     20004
#define ERROR_ARRAY_NULL    20005



#ifdef WIN_NT
_declspec (dllexport)
#endif
void pass_str
 ( OCIExtProcContext * ctx        /* CONTEXT */,
                                                                                 
                          
   char *             p_ostr     /* STRING */,
   short *            p_ostr_i   /* INDICATOR short */,
   int *              p_ostr_ml  /* MAXLEN int */
 )
{
myCtxStruct*myCtx;
char  * stringToAdd = " ta dah";
                                                                                 
                          
    if ( (myCtx = init( ctx )) == NULL ) return;
    _debugf( myCtx,  "Enter Pass Str" );                                
    /*
     * Now, we will make the output string = upper(input string)
     * if the output buffer is big enough and
     */
    if ( *p_ostr_i == OCI_IND_NOTNULL )
    {
      //added unsigned int to *p_ostr_ml
     if ((strlen(p_ostr) + strlen(stringToAdd) +1) <= unsigned int(*p_ostr_ml))    
            strcat( p_ostr, stringToAdd );
     else
           raise_application_error
           ( myCtx, ERROR_STR_TOO_SMALL, "String too small" );
    }
    else
    {
        //added unsigned int to *p_ostr_ml
        if ( (strlen(stringToAdd) + 1) <= unsigned int(*p_ostr_ml) )            
            strcpy( p_ostr, stringToAdd );
        else
           raise_application_error
           (  myCtx, ERROR_STR_TOO_SMALL, "String too small" );
    }
    term(myCtx);
}
--------------------------------------the end----------------------------------

December 28, 2003 - 10:48 am UTC

sorry -- but external procedures are probably the last thing I would choose as a method to cut my teeth on in C. The last thing.

I'm not really going to look at pages of C code that is slightly modified from what I provided (that I know to work "out of the box", tested thoroughly) and look for bugs!

to get over your current error -- you need to define WIN_NT

#ifdef WIN_NT
_declspec (dllexport)
#endif

to get the _declspec to be there (else the function is not exported), or just remove the #ifdef/#endif (i write code that works on all OS's - making exceptions for windows non-portability when necessary like that)

debugf is a macro that should be in the code after _debugf:

/*
* 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

Got it working!!

Inayat Jilani, December 30, 2003 - 1:02 am UTC

Hi Tom,

A big fat thanks to you!. Your code is working exactly as is with the only exception of type-casting I had to do to compile it in MSVC++. As you suggested I also had to removed the #Ifdef and #endif, as the OS I am working on is XP.

#ifdef WIN_NT
_declspec (dllexport)
#endif

and I my 'Hello Ta da' made it's merry way to the SQL Plus window!

Thanks again, Inayat

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Getting Oracle error mapping function

Got it working!!

More to Explore

CLOB/BLOB/etc

Calling external procedure to concatenate a string

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Getting Oracle error mapping function

Got it working!!

More to Explore

CLOB/BLOB/etc