Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004

    Unanswered: Two Clobs in a table, selecting and OCI Error


    When we have one clob in the database the following code works fine to retrieve data, but as soon as we add a second we get an error when trying to get the size of the first clob. We do the following for each column:

    OCILobLocator * clobLocator;
    retVal = OCIDescriptorAlloc( (dvoid *)envHndlr,(void**)&clobLocator,OCI_DTYPE_LOB,0,0) ;

    // DatabaseValue takes ownership of clobLocator
    returnValues.push_back( new DatabaseValue( clobLocator, errHndlr, svcCtxHndlr ) );

    // bind locator to its associated return value
    retVal = OCIDefineByPos( sqlStmntHndlr, &defnp ,errHndlr,(index+1),(dvoid *) &clobLocator,(sb4) -1,SQLT_CLOB ,(sb2 *) &indicVar[index],(ub2 *) 0,(ub2 *) &retCodes[index],OCI_DEFAULT);

    Then we fetch the first row:

    int numRows = 1;
    retVal = OCIStmtFetch(sqlStmntHndlr, errHndlr, (ub4) numRows,OCI_FETCH_NEXT, OCI_DEFAULT );

    Then for each column we do:

    DatabaseValue const * const dbValue = returnValues[ nColIndex ];
    if ( dbValue->clobLocator == NULL ) {
    } else {
    // how big is the clob?
    ub4 lobLength;
    sword const gotLength =
    OCILobGetLength( dbValue->svcCtxHndlr,
    dbValue->errHndlr,dbValue->clobLocator,&lobLength );
    if ( gotLength != OCI_SUCCESS ) {
    return gotLength;

    Sorry about the bad formatting it doesn't seem to want to put my spaces/tabs in!!!

    OCILobGetLength returns -2 and the oracle error is ORA-01403: no data found.

    Any ideas?? Do we need to something extra when there are two clobs in a table? Or is this code just plain wrong.

    Oracle 8.1.7.

    Thanks in advance,

    Jo Silva.

  2. #2
    Join Date
    Dec 2003
    I'm almost positive you cannot have more than one LOB object in a table.

    Ah, i was thinking of LONGs.Sorry bout that
    Last edited by RhythmAddict; 08-20-04 at 12:46.

  3. #3
    Join Date
    May 2004
    I am not sure but i think in Oracle 8 it works just with one clob column and in Oracle 9 this restriction is waived.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts