Re: oci stored procedure parameter bind

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 18 Jun 2001 04:46:14 -0700
Message-ID: <9gkpm602cu4@drn.newsguy.com>

In article <bfd9866f.0106180053.32c895cc_at_posting.google.com>, jbullock_at_neo.rr.com says...
>
>I am trying to get a standard C application to call an existing
>database stored procedure using the OCI library. The program uses
>function "obndra" to bind a parameter declared as "IN OUT" and "TABLE
>OF VARCHAR2(16)" to a two dimensional C char array. When the
>application is run, the following error is reported:
>
>error 1485 "compile bind length different from execute bind length"
>
>If I modify the stored procedure parameter to be OUT only, the C app
>works fine.
>
>Can anyone tell me if you can bind to an "IN OUT" parameter this way?
>
>I would like to know if it's even possible to bind to an "IN OUT"
>procedure parameter using "obndra". I have not included copies of the
>software here because the application can be made to work, although it
>would be more effective to leave the existing stored procedure as is.
>I will supply stored procedure and C code upon request if more
>information is required.
>
>Thanks in advance for any help you can give.

01485, 00000, "compile bind length different from execute bind length"

// *Cause:  You bound a buffer of type DTYVCS (VARCHAR with the two byte 
//          length in front) and at execute time the length in the first two 
//          bytes is more than the maximum buffer length (given in the bind 
//          call).  The number of elements in the array and the current number 
//          of elements in the array cannot be more than the maximum size of
//          the array.

that typically means the data in the array on the way in was un-initialized. It is customary to set the length fields to the maximum width for uninitialized data on the way in.

Here is an example from a pro*c app that needed to dynamically call a procedure with a PLSQL table type:

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

}

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Jun 18 2001 - 06:46:14 CDT