Re: Dyn Method 4, Pro*C, Stored Procedures, Host Arrays

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 18 Aug 1999 19:19:13 GMT
Message-ID: <37cb04fe.202361770@newshost.us.oracle.com>

A copy of this was sent to Ken Tough <ken_at_objectech.co.uk> (if that email address didn't require changing) On Wed, 4 Aug 1999 16:35:58 +0100, you wrote:

>
>[a (slightly diff) reply also posted]
>
>Apparently Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>
>>You would have to use OCI to bind table types.
>
>>To return large amounts of data from a stored procedure to a pro*c application -
>>have you considered ref cursors?
>
>I will have a look at that. I was hoping for a very 'generic'
>solution, where parameters could take/return either single or
>array data, without the caller knowing what it is, other than
>parameter position. [long story]
>
>>Another workaround others have used is to pass easily parsable strings back in
>>32k chunks.
>
>That could be a very good solution, as all parms will be dealt with
>as strings anyway. I suppose manipulation in PL/SQL would use
>things like concatenation [ retbuf = retbuf || '|' || selval ] ?
>
>>OCI is pretty easy to use to do this -- you can leave your entire app in Pro*C
>>except for this one part (executing a procedure with plsql tables). let me know
>>if you need a small example.
>
>That sounds like a reasonable option. The example would certainly
>help to get an idea of how much stuff needs to be done with it..
>
>Thanks again,

Sorry so long -- forgot to followup on this. Here is an example. The plsql is:

create or replace procedure do_something( p_table in out owa.vc_arr ) as
begin

        for i in 1 .. p_table.count loop
                p_table(i) := upper(p_table(i));
        end loop;
        p_table( p_table.count+1 ) := 'New Entry';

end;
/

and the OCI to run that would be:
#include <stdio.h>

#include <oratypes.h>
#include <ocidfn.h>
#include <ociapr.h>
#include <ocidem.h>

static char * USERID = "scott/tiger";

#define SQLCA_INIT
EXEC SQL INCLUDE sqlca;

static void sqlerror_hard()
{

    EXEC SQL WHENEVER SQLERROR CONTINUE;     printf("\nORACLE error detected:");     printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

    EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);
}

void oci_error(Lda_Def lda, Cda_Def cda) {

    text msg[600];
    sword rv;

    rv = oerhms(&lda, cda.rc, msg, 600);

    printf("\n\n%.*s", rv, msg);
    printf("Processing OCI function %s\n", oci_func_tab[cda.fc]);

    if (oclose(&cda))
        printf("Error closing cursor!\n");
    if (ologof(&lda))
        printf("Error logging off!\n");

    exit(1);
}

#define MAX_TABLE_SIZE 1200

static void process()
{
Lda_Def lda;
Cda_Def cda;
char stmt[255];
char ename[40];
char data[50][255];

short   data_l[50];
short   data_i[50];
short   data_rc[50];
int     data_nelements;
int     i;

    sqllda( &lda );

    if ( oopen(&cda,&lda,NULL,-1,01,NULL,-1) ) oci_error(lda,cda);

    strcpy( stmt, "begin do_something( :p_table ); end;" );

    if ( oparse(&cda, stmt, -1, 0, 2 ) ) oci_error(lda,cda);

    /* init all array elements to NULL and set their max length */     for( i = 0; i < sizeof(data)/sizeof(data[0]); i++ )     {

        data_i[i] = -1;
        data_l[i] = sizeof(data[0]);

    }

    /* for the elements we have data for, fix the NULL indicator and

       put some data in there */
    for( data_nelements = 0; data_nelements < 15; data_nelements++ )     {

        data_i[data_nelements] = 0;
        sprintf( data[data_nelements], "Some data %d", data_nelements );

    }

    /* Bind it */
    if (

    obndra( &cda,               /* cursor data area */
           ":p_table",          /* bind variable name */
            -1,                 /* indicates prev. parameter is C String */
            (void*)data,        /* data array (plsql table) */
            sizeof(data[0]),    /* sizeof each array element */
            STRING_TYPE,        /* binding C strings in table */
            -1,                 /* scale (not used) */
            data_i,             /* indicators for the i'th tbl entry */
            data_l,             /* lengths of the i'th tbl entry */
            data_rc,            /* return codes for the i'th tbl entry */
            sizeof( data )/sizeof(data[0]), /* MAXsize of array */
            &data_nelements,    /* CURRENT size of array */
            (void*)0,           /* not used */
            -1,                 /* not used */
            -1 )                /* not used */
            ) oci_error(lda,cda);

    /* run it */
    if( oexec( &cda ) ) oci_error(lda,cda);

    /* show it... */
    for( i = 0; i < data_nelements; i++ )

        printf( "Element(%d), Length = %d ind = %d = %s\n",
                  i, data_l[i], data_i[i], data[i] );

}

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;     /* Connect to ORACLE. */
    strcpy( oracleid.arr, USERID );
    oracleid.len = strlen(USERID);

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

    EXEC SQL CONNECT :oracleid;
    printf("\nConnected to ORACLE as user: %s\n\n", oracleid.arr);

    process();

    /* Disconnect from ORACLE. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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 Wed Aug 18 1999 - 14:19:13 CDT