Re: Calling stored procedure ProC

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 03 Nov 1998 17:51:13 GMT
Message-ID: <36454238.16625466_at_192.86.155.100>

A copy of this was sent to Oliver Muthig <oliver.muthig_at_ubs.com> (if that email address didn't require changing) On Tue, 03 Nov 1998 17:01:22 +0100, you wrote:

>Hi there,
>
>could anybody give me a C code snipped which calls a stored procedure
>returning values (array IN OUT parameter and return value from PL/SQL) ?
>
>Thanks.
>
>Oliver
>

Well, here is an example:

static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
    VARCHAR AnArray[50][50];
EXEC SQL END DECLARE SECTION;
    int i;

    for( i = 0; i < 50; i++ )
    {

        sprintf( AnArray[i].arr, "Entry %d", i );
        AnArray[i].len = strlen( AnArray[i].arr );
        printf( "%s\n", AnArray[i].arr );

    }

    EXEC SQL EXECUTE
    DECLARE
        type myArrayType is table of varchar2(40) index by binary_integer;

        procedure foobar( foo in out myArrayType )
        is
        begin
            for i in 1 .. foo.count loop
                foo(i) := upper( foo(i) );
            end loop;
        end;
    BEGIN
        foobar( :AnArray );

    END;
    END-EXEC;     for( i = 0; i < 50; i++ )
    {

        printf( "%s\n", AnArray[i].arr );     }
}

Its pretty straightforward -- a C array indexed from 0 .. N will be mapped to a pl/sql table of 1 .. N+1.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Tue Nov 03 1998 - 18:51:13 CET