Problem obtaining complex object in OCI from procedure call (type OUT)

From: Alexandru Sclearuc <asclearuc_at_yahoo.com>
Date: 19 Aug 2003 11:34:54 -0700
Message-ID: <b212587c.0308191034.2ad2f0af@posting.google.com>

Hello,

Could somebody help me with next

I have:

• my type: CREATE OR REPLACE TYPE table_number IS TABLE OF number;
• procedure that works with this type: procedure INSERT_FORALL_AVS_NUMBER_RET ( in_number IN table_number, out_number OUT table_number );

With all these I work from OCI (using statement preparation).

I successfully pass parameters into the procedure (they are inserted into the table).

The same parameters should be passed out of the procedure (using SQL*Plus I have tested that the parameters are passed out correctly).

But when I try to reach the out parameters from OCI, I got some problems. The statement is executed with no errors, I can even obtain size of output parameter (and it is correct) - but when I try to obtain data I get invalid data.

So, the output instead of correct one:

----------------------- Output Data start -----------------------

11
22
33
44
55

------------------------ Output Data end ------------------------

is incorrect:

----------------------- Output Data start -----------------------
-858993460
-858993460
-858993460
-858993460

-858993460

------------------------ Output Data end ------------------------

If I work with record that contains lets say only NUMBER and CHAR -> all is OK (out parameter is get correctly, and I can view it). The problem is only when I work objects that have collections inside (tables, arrays, nested tables...).

I haven't found anywhere that OCI could not work with complex OUT procedure parameters, so I assume that I do smth wrong.

Could somebody help me either by pointing where is my mistake (a little bit simplified code is below) or by pointing me to good working code source that extracts collection data from output parameter:

// statement itself

char *stmt="BEGIN TEST_COLLECTION.INSERT_FORALL_AVS_NUMBER_RET(:1, :2); END;";
// statement prepare... - skipped

// bind of input parameters

string oracleTypeName("TABLE_NUMBER");

OCIBind    *ociBind    = 0;
OCIType    *tdo        = 0;
OCITable   *ociTable   = 0;
OCINumber   ociNumber;
vector<int> data;        // filled in somewhere else

checkerr( OCIBindByPos(ociStmt, &ociBind, ociError, 1, 0, 0,
                   SQLT_NTY, 0, 0, 0, 0, 0, OCI_DEFAULT));
checkerr( OCITypeByName(ociEnv, ociError, ociSvcCtx, 0, 0,
                   (CONST text*) oracleTypeName.c_str(),
oracleTypeName.size(),
                   0, 0, OCI_DURATION_SESSION, OCI_TYPEGET_HEADER,

&tdo));

checkerr( OCIObjectNew(ociEnv, ociError, ociSvcCtx, OCI_TYPECODE_TABLE,

                   tdo, 0, OCI_DURATION_DEFAULT, TRUE, (dvoid

**)&ociTable));

for(register int i=0; i<data.size(); ++i) {

    checkerr( OCINumberFromInt(ociError,(CONST dvoid*)&data[i], sizeof(data[i]),

                               OCI_NUMBER_SIGNED, &ociNumber));

    checkerr( OCICollAppend(ociEnv, ociError, (CONST dvoid*)&ociNumber,

                               (CONST dvoid*)0, ociTable));

}     

checkerr(OCIBindObject(ociBind, ociError, tdo, (dvoid **)&ociTable,

                       (ub4*)0, 0, 0));

// binding output parameter

OCIDefine  *ociDefine   = 0;
OCIBind    *ociBindOut  = 0;
OCITable   *ociTableOut = 0;
checkerr( OCIBindByPos(ociStmt, &ociBindOut, ociError, 2, 0, 0,
                       SQLT_NTY, 0, 0, 0, 0, 0, OCI_DEFAULT));
checkerr( OCIObjectNew(ociEnv, ociError, ociSvcCtx,
OCI_TYPECODE_TABLE,                                 tdo, 0,
OCI_DURATION_DEFAULT, TRUE,
                       (dvoid **) &ociTableOut));
checkerr( OCIBindObject(ociBindOut, ociError, tdo, 
                       (dvoid **)&ociTableOut, (ub4*)0, 0, 0));
checkerr( OCIDefineByPos(ociStmt, &ociDefine, ociError, 2, 0, 0,
                       SQLT_NTY, 0, 0, 0, OCI_DEFAULT));
checkerr( OCIDefineObject(ociDefine, ociError, tdo, 
                       (dvoid **)&ociTableOut, (ub4*)0, 0, 0));

// execute statement

checkerr( OCIStmtExecute(ociSvcCtx, ociStmt, ociError, 1, 0, 0, 0, OCI_DEFAULT));
// show result
// the problematic part of code.......

OCINumber resultRow;
sb4 size;
// returned size is OK (5 in my case)

checkerr( OCICollSize(ociEnv, ociError, ociTableOut, &size));

cout << "-------------------- Output Data start --------------------"

<< endl;
for (int i=0; i<size; ++i) {

        boolean exists = 0;
        checkerr( OCICollGetElem(ociEnv, ociError, ociTableOut, i,
&exists,
                                (dvoid**)&resultRow, 0));
        int tmpInt;
        // !!!!!! here instead of normal number I get -858993460
        // !!!!!! (or 0xCCCCCCCC)
        checkerr( OCINumberToInt(ociError, &resultRow, sizeof(tmpInt),
                                OCI_NUMBER_SIGNED, &tmpInt));
        cout << tmpInt << endl;

}

cout << "--------------------- Output Data end ---------------------"

<< endl;

// commit transaction

checkerr( OCITransCommit(ociSvcCtx, ociError, OCI_DEFAULT));

Looking forward to your replies,
Alexandru Received on Tue Aug 19 2003 - 13:34:54 CDT