Re: external function problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 20 May 1999 19:28:29 GMT
Message-ID: <374e61b9.30586150@newshost.us.oracle.com>

A copy of this was sent to "Fredrik Antonsson" <pt96fan_at_student.hk-r.se> (if that email address didn't require changing) On Thu, 20 May 1999 17:20:44 GMT, you wrote:

>Hi,
>I call an external function in oracle8 in a trigger like this (where
>converter is the lib) :
>
>value1 float;
>value2 float;
>
>converter.myfunktion(value1, value2);
>
>new:myval1 := value1;
>new:myval 2 := value2;
>
>The function is a c function which sets the values by reference. It works
>fine when I work with floats but I would like to use int instead but this
>doesn't work. What shall I declare value1 and value2 as when I wish to send
>them as integers, I have tried a couple of different datatypes but the only
>thing I gets back from the c function is 0. Can it bee somthing I have to
>change in the c-function, can't I return the values as int by reference in
>the c-function?
>
> Fredrik Antonsson, Ronneby - Sweden
>

If I understand -- you would like to pass IN OUT parameters from PLSQL to C. If thats correct, this is how.

Here is the C code to do a 'swap' routine:

#include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>

#ifndef OCI_ORACLE

# include <oci.h>
#endif

#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);

    OCIExtProcRaiseExcpWithMsg(ctx,errCode,msg,strlen(msg));     return -1;
}

long

swap( OCIExtProcContext  * ctx,
      long * p_x1, short * p_x1_i,
      long * p_x2, short * p_x2_i )

{
long tmp;

    if ( *p_x1_i )

        raise_application_error( ctx, 20001, "X1 may not be NULL" );     if ( *p_x2_i )

        raise_application_error( ctx, 20001, "X2 may not be NULL" );

    tmp = *p_x1;
    *p_x1 = *p_x2;
    *p_x2 = tmp;
}

Note that we use "long *" as the datatype for the numbers and since they are in out, we use "short *" for the indicators. The binding to this would be in sql:

CREATE OR REPLACE PACKAGE demo
as

    procedure swap( p_x1 in out binary_integer,

                    p_x2 in out binary_integer );

end;
/
show errors

drop library demo_lib
/

create library demo_lib as
'/export/home/oracle8i/local/test/extproc.so' /

CREATE OR REPLACE PACKAGE BODY demo
as

    procedure swap( p_x1 in out binary_integer,

                    p_x2 in out binary_integer )

    is external
    name "swap"
    library demo_lib
    language C
    with context
    parameters ( CONTEXT,

        p_x1    long,
        p_x1    INDICATOR short,
        p_x2    long,
        p_x2    INDICATOR short );

end;
/

Note the use of binary_integer -- that maps nicely to a C long type. Now we can test:

declare

    a number := 5;
    b number := 10;
begin

    dbms_output.put_line( 'a = ' || a );     dbms_output.put_line( 'b = ' || b );     demo.swap( a, b );
    dbms_output.put_line( 'a = ' || a );     dbms_output.put_line( 'b = ' || b ); end;
/

a = 5
b = 10
a = 10
b = 5

PL/SQL procedure successfully completed.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

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 Thu May 20 1999 - 14:28:29 CDT