Re: Stored Proc - Array Param

From: Geoff <nospam_at_nospam.com>
Date: Thu, 01 Jun 2006 00:32:11 GMT
Message-ID: <fAqfg.10054$y4.5676@newsread2.news.pas.earthlink.net>

Well, I can get a slightly different error now, which is:

ORA-01485: compile bind length different from execute bind length

This is how the params look now (pardon the smalltalk):

paramArray := Array new: 14.
paramArray

  at: 1 put: stmthp;                                " Statement Handle "
  at: 2 put: bindPtr;                               " An address of a bind 
handle "
  at: 3 put: connection errhp;                " Error Handle "
  at: 4 put: ':arr';                                    " Position "
  at: 5 put: ':arr' size;                              " Special value for 
PL/SQL Binding"
  at: 6 put: dataPtr address;                    " An Address of a data 
value "
  at: 7 put: 4;                                         " The size of the 
data value "
  at: 8 put: dataType;                             "  The data type of the 
value being bound "
  at: 9 put: indp address;                        " Pointer to an indicator 
variable or array "
  at: 10 put: alenp address;                     " Pointer to array of 
actual lengths of array elements"
  at: 11 put: rcodep address;                  " Pointer to array of column 
level return codes "
  at: 12 put: 3;                                        " The maximum 
possible number of elements of type datatype "
  at: 13 put: curelep address;                  " A pointer to the actual 

number of elements "
  at: 14 put: AbtOracleOciConstants::OCI_DEFAULT.

. . . the data type (position 8 above) is '3', sqlt_int, the 'alenp' (position 10 above) is an array of 3 elements with a '4' (for 4 bytes) at each location, and curelep (position 13 above) is a pointer to the number '3' (3 elements in the array), the array itself (position 6 above) is 12 bytes but each element is 4 bytes long.

The example file 'cdemo4.c' has an example that is close except for 2 differences, they are using a table of float (I am using table of number) and the C code uses the obsolete oci calls. Here are the bind params from that file (and declarations, etc.):

    float salary[MAX_ARRAY_SIZE];

    sb2 salary_indp[MAX_ARRAY_SIZE];
    ub2 salary_len[MAX_ARRAY_SIZE];
    ub2 salary_rcode[MAX_ARRAY_SIZE];
    ub4 salary_cursiz = (ub4) MAX_ARRAY_SIZE;

    /* initialize the bind arrays */
    for (i = 0; i < MAX_ARRAY_SIZE; i++)     {

        salary_len[i] = sizeof (float);
    }

   /* bind the salary array */
    if (obndra(&cda,

(text *) ":sal",

           -1,


           (ub1 *) salary,

           (sword) sizeof (float),

           FLOAT_TYPE,
           -1,
           salary_indp,
           salary_len,
           salary_rcode,


           (ub4) MAX_ARRAY_SIZE,

           &salary_cursiz,


           (text *) 0,

           -1,
           -1))

. . . since oracle does not publish information on the obsolete calls anymore, I am guessing at how to translate it to the oci calls. Any ideas on what I am doing wrong?

Thanks.

-g Received on Wed May 31 2006 - 19:32:11 CDT