This discussion is archived
3 Replies Latest reply: Apr 28, 2011 4:06 AM by 423410 RSS

OCIBindByPos(),the parameter of alenp,how to deal with go beyond 65535

857616 Newbie
Currently Being Moderated
The alenp is one parameter of the function OCIBindByPos() (Pointer to array of actual lengths of array elements. Each element in alenp is the
length (in bytes, unless the data in valuep is in Unicode, when it is in codepoints) of
the data in the corresponding element in the bind value array before and after the
execute. This parameter is ignored for dynamic binds. If valuep is an OUT parameter,
then you must set alenp to point to 0.), the type of ub2,which is limited between 0 and 65535.
Some time we needed to deal with alenp going beyond 65535.
For instance,inserting static array,with column type is blob,actual length was 71486, value_sz was 100000, and dty was SQLT_BIN.

How to do this? Thank you!
  • 1. Re: OCIBindByPos(),the parameter of alenp,how to deal with go beyond 65535
    423410 Explorer
    Currently Being Moderated
    If the data is more than the capability of ub2 you can use OCILobWrite API. The API takes an ub4 for data amount parameter
    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28395/oci07lob.htm
  • 2. Re: OCIBindByPos(),the parameter of alenp,how to deal with go beyond 65535
    857616 Newbie
    Currently Being Moderated
    Thank you!
    But OCILobWrite API was expensive for inserting blob.
    These were my test results for inserting array of blobs:
    1.OCIBindByPos(), OCIBindArrayOfStruct(), the Parameter of dty taking SQLT_BIN,would takes least time and reduce the round trip.But it had a limited in the capability of ub2.
    2.Using OCIBindByPos() ,with the Parameter of dty taking SQLT_BIN, to insert blob one time just for one record, would takes more round trips, and takes more time, without the limited in the capability of ub2.
    3.Although OCIBindByPos(), OCIBindArrayOfStruct(), OCILobWrite ,the Parameter of dty taking SQLT_BLOB, would reduce the round trip, takes more time than condition 2.
    4.OCILobArrayWrite() might be a good choice,but it was one of these Oracle Database 10g Release 2 functions,and my Customer demand was in platform of Oracle Database 9i.

    How could I improve the performance for inserting blob,at least greater than the condition 2?
  • 3. Re: OCIBindByPos(),the parameter of alenp,how to deal with go beyond 65535
    423410 Explorer
    Currently Being Moderated
    Please go through these links to see if it helps
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:14212348049
    http://www.dba-oracle.com/t_table_blob_lob_storage.htm

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points