DevOps, CI/CD and Automation

I am trying to access objects thru OCI. In doing so, I am able to get to the first level of object,
but I cannot access the embedded (second level of) object.

I get Error - OCI-22060: argument [2] is an //
// invalid or uninitialized number

Here is the sample of what I am trying to do:

Here is the sql script that creates the objects and the store procedure.

CREATE TYPE Vecdata_Array_t AS TABLE OF RAW(32000);
/
CREATE TYPE VecChunk_t AS OBJECT ( blkSize INTEGER,
dataBlk VECDATA_ARRAY_T);
/
CREATE TYPE Vec_Chunk_Array_t AS TABLE OF VECCHUNK_T;
/
CREATE TYPE VecObject_t AS OBJECT ( vid INTEGER,
vecSize INTEGER,
numChunks INTEGER,
chunks VEC_CHUNK_ARRAY_T);
/
CREATE TYPE VecObject_List_t AS TABLE OF VecObject_t;
/


CREATE TABLE HORIZON_DATA_VEC ( VID INTEGER NOT NULL,
BLK_NO INTEGER NOT NULL,
BLOB_DATA BLOB NULL );
/





CREATE OR REPLACE PROCEDURE ReadVecDataWithWhere( p_VecTableName IN VARCHAR2,
p_WhereCls IN VARCHAR2, p_vecObjList OUT VecObject_List_t) IS

v_Cursor NUMBER;
v_SqlString VARCHAR2(2000);
v_NumRows INTEGER;
v_BatchSize CONSTANT INTEGER := 5;
v_IDs DBMS_SQL.NUMBER_TABLE;
v_blkNos DBMS_SQL.NUMBER_TABLE;
v_blobDescs DBMS_SQL.BLOB_TABLE;
v_ReturnCode INTEGER;
v_cnt INTEGER;
v_blobLen INTEGER;
v_bufLen CONSTANT INTEGER := 32000;
v_loopCnt INTEGER;
v_amt INTEGER;
v_offset INTEGER;

BEGIN

v_Cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_OUTPUT.PUT_LINE('Opened the cursor');

p_vecObjList := VecObject_List_t();

v_SqlString := 'SELECT vid, blk_no, blob_data from ' || p_VecTableName;
v_SqlString := v_SqlString ||' WHERE ' || p_WhereCls;
v_SqlString := v_SqlString || ' ORDER BY vid, blk_no';

BEGIN
DBMS_SQL.PARSE(v_Cursor, v_SqlString, DBMS_SQL.V7);
DBMS_SQL.DEFINE_ARRAY(v_Cursor, 1, v_IDs, v_BatchSize, 1);
DBMS_SQL.DEFINE_ARRAY(v_Cursor, 2, v_blkNos, v_BatchSize, 1);
DBMS_SQL.DEFINE_ARRAY(v_Cursor, 3, v_blobDescs, v_BatchSize, 1);

v_ReturnCode := DBMS_SQL.EXECUTE(v_Cursor);

v_cnt := 1;
LOOP
v_NumRows := DBMS_SQL.FETCH_ROWS(v_Cursor);
IF v_NumRows = 0 THEN
EXIT;
END IF;

DBMS_SQL.COLUMN_VALUE(v_Cursor, 1, v_IDs);
DBMS_SQL.COLUMN_VALUE(v_Cursor, 2, v_blkNos);
DBMS_SQL.COLUMN_VALUE(v_Cursor, 3, v_blobDescs);

FOR i in v_cnt..(v_cnt + v_NumRows - 1) LOOP

IF v_blobDescs(i) IS NULL THEN
DBMS_OUTPUT.PUT_LINE('BLOB is NULL');
ELSE
v_blobLen := DBMS_LOB.GETLENGTH(v_blobDescs(i));
v_loopCnt := v_blobLen/v_bufLen + 1;
v_amt := v_blobLen;

p_vecObjList.EXTEND(1);
p_vecObjList(i) := VecObject_t(v_IDs(i), v_blobLen, v_loopCnt,
Vec_Chunk_Array_t());
v_offset := 1;

FOR j in 1..v_loopCnt LOOP

if ((v_loopCnt > 1) AND (j != v_loopCnt)) THEN
v_amt := v_bufLen;
END IF;

p_vecObjList(i).chunks.EXTEND(1);
p_vecObjList(i).chunks(j) := VecChunk_t(v_amt,
Vecdata_Array_t());

p_vecObjList(i).chunks(j).dataBlk.EXTEND(1);

DBMS_LOB.READ(v_blobDescs(i), v_amt, v_offset,
p_vecObjList(i).chunks(j).dataBlk(1));

v_offset := v_offset + v_amt;
if v_offset >= v_blobLen THEN
EXIT;
END IF;

END LOOP;

END IF;
END LOOP;

v_cnt := v_cnt + v_NumRows;
EXIT WHEN v_NumRows < v_BatchSize;

END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_Cursor);

EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_Cursor);
RAISE;
END;

END ReadVecDataWithWhere;


Note: In the code below, mus is the session handle in our application:

Here is the the code c code that uses OCI to execute the above stored procedure:

#include "oci.h"
#include "readwithwhere.h

long readVecData(void *mus)
{
long ier = SUCCESSFUL;
sword rv;
int retval;
int vidCount=0;
int i, j;
char l_msg[512];
char funcname[] = "readVecData()";

OCIEnv *oraEnvHandle = (OCIEnv *)NULL;
OCIError *oraErrorHandle = (OCIError *)NULL;
OCISvcCtx *oraSrvcHandle = (OCISvcCtx *)NULL;

OCIStmt *l_oraStmtHandle;
OCILobLocator *l_blobLoc = (OCILobLocator *)NULL;
OCIBind *bndhp[6];
OCIDefine *dfnhp[6];

OCIType *vecDataArrayTDO;
OCIType *vecObjectTDO;
OCIType *vecChunkArrayTDO;
vecObject_List_t *vecObjectList;
Vecdata_Array_t *vecData;

char l_tableName[80];
char whereCls[512];

char execProcStmt[] = "BEGIN ReadVecDataWithWhere(:l_tableName, :whereCls, :v
ecObjectList); END;";


float *l_dataBuf = NULL;
OCIRaw **oneRawElem = (OCIRaw**) 0;
OCINumber *oneNumElem = (OCINumber *) 0;
vecObject_t *vecObj;
vecChunk_t *dataChunk;
ub1 *databuf;

int l_vecSize;
int l_vid;
int l_numchunks;
int l_blkSize;
int existFlag = 0;
int numofVecObjects=0;

strcpy(whereCls, "vid = 205 OR vid = 206");
strcpy(l_tableName, "horizon_data_vec");

/////////////////////////////////////////////////////////////////////
// Get the pointer to the active database and then using that
// handle, get all the handles necessary for the OCI environment
// NOTE: These are inhouse functions that are used to get access to
// Oracle connection and OCI environment handles
// Also all the error checking code has been taken out to
// keep things simple.
/////////////////////////////////////////////////////////////////////
db = vecDBMU( mus );

((DsiOracleDb *)db)->dsiOraGetEnvHandles(&oraEnvHandle, &oraSrvcHandle,
&oraErrorHandle);


// Allocate the blob descriptor
rv = OCIDescriptorAlloc((dvoid *)oraEnvHandle, (dvoid **) &l_blobLoc,
(ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0);

// Allocate the statment handle
rv = OCIHandleAlloc((dvoid *)oraEnvHandle, (dvoid **)&l_oraStmtHandle,
(ub4)OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);

// Get the OCI type information for vecObject_List_t
retval = OCITypeByName(oraEnvHandle, oraErrorHandle, oraSrvcHandle,
(text *) 0, 0, (text *) "VECOBJECT_LIST_T",
strlen("VECOBJECT_LIST_T"), (text *) 0, 0,
OCI_DURATION_DEFAULT, OCI_TYPEGET_ALL, &vecObjectTDO);

// Create instance of vecObject_List_t
retval = OCIObjectNew(oraEnvHandle, oraErrorHandle, oraSrvcHandle,
OCI_TYPECODE_TABLE, (OCIType *)vecObjectTDO,
(dvoid *)0, OCI_DURATION_DEFAULT, TRUE,
(dvoid**) &vecObjectList);

// Get the OCI type information for vec_Chunk_Array_t
retval = OCITypeByName(oraEnvHandle, oraErrorHandle, oraSrvcHandle,
(text *) 0, 0, (text *) "VEC_CHUNK_ARRAY_T",
strlen("VEC_CHUNK_ARRAY_T"), (text *) 0, 0,
OCI_DURATION_DEFAULT, OCI_TYPEGET_ALL,
&vecChunkArrayTDO);

// Create instance of vec_Chunk_Array_t
retval = OCIObjectNew(oraEnvHandle, oraErrorHandle, oraSrvcHandle,
OCI_TYPECODE_TABLE, (OCIType *)vecChunkArrayTDO,
(dvoid *)0, OCI_DURATION_DEFAULT, TRUE,
(dvoid**) &chunks);


// Prepare the statment to be executed
retval = OCIStmtPrepare(l_oraStmtHandle, oraErrorHandle,
(text *)execProcStmt, strlen(execProcStmt)+1,
OCI_NTV_SYNTAX, OCI_DEFAULT);

// Bind the arguments of stored procedure to be executed.
bndhp[0] = (OCIBind *)0;
bndhp[1] = (OCIBind *)0;
bndhp[2] = (OCIBind *)0;
bndhp[3] = (OCIBind *)0;
bndhp[4] = (OCIBind *)0;
bndhp[5] = (OCIBind *)0;

retval = OCIBindByName(l_oraStmtHandle, &bndhp[0], oraErrorHandle,
(text *) ":l_tableName", -1, (dvoid *)l_tableName,
strlen(l_tableName)+1, SQLT_STR, (dvoid *)0,
(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);

retval = OCIBindByName(l_oraStmtHandle, &bndhp[1], oraErrorHandle,
(text *) ":whereCls", -1, (dvoid *)whereCls,
strlen(whereCls)+1, SQLT_STR, (dvoid *)0,
(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);

retval = OCIBindByName(l_oraStmtHandle, &bndhp[3], oraErrorHandle,
(text *) ":vecObjectList", -1, (ub1 *)&vecObjectList,
(sword)sizeof(vecData), SQLT_NTY, (dvoid *)0,
(ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);

retval = OCIBindObject(bndhp[3], oraErrorHandle, vecObjectTDO,
(dvoid **)&vecObjectList, (ub4 *) -1, (dvoid**)0,
(ub4) 0);

// Execute the store procedure
retval = OCIStmtExecute(oraSrvcHandle, l_oraStmtHandle, oraErrorHandle, 1, 0,
0, 0, OCI_DEFAULT);

retval = OCITableSize(oraEnvHandle, oraErrorHandle, vecObjectList,
&numofVecObjects);

for (i = 0; i < numofVecObjects; i++)
{
retval = OCICollGetElem(oraEnvHandle, oraErrorHandle, vecObjectList, i,
&existFlag, (dvoid **)&vecObj, (dvoid **)0);

retval = OCINumberToInt(oraErrorHandle, &vecObj->vid, 4,
OCI_NUMBER_UNSIGNED, &l_vid);

retval = OCINumberToInt(oraErrorHandle, &vecObj->vecsize, 4,
OCI_NUMBER_UNSIGNED, &l_vecSize);

retval = OCINumberToInt(oraErrorHandle, &vecObj->numchunks, 4,
OCI_NUMBER_UNSIGNED, &l_numchunks);
for (j = 0; j < l_numchunks; j++)
{
retval = OCICollGetElem(oraEnvHandle, oraErrorHandle, vecObj->chunks,
j, &existFlag, (dvoid **)&dataChunk,
(dvoid **)0);

retval = OCINumberToInt(oraErrorHandle, &dataChunk->blksize, 4,
OCI_NUMBER_UNSIGNED, &l_blkSize);

//****************************************************//
// Here I AM NOT ABLE TO GET THE BLOCKSIZE //
// I get Error - OCI-22060: argument [2] is an //
// invalid or uninitialized number //
// When I try to display dataChunk in debugger I see //
// following: //
// //
// (dbx) print *dataChunk //
// *dataChunk = { //
// vecChunk_t::blksize = { //
// OCINumber::OCINumberPart = "" //
// } //
// vecChunk_t::datablk = 0x30a324c //
// } //
// //
// If I do following in the code //
// retval = OCITableSize(oraEnvHandle, //
// oraErrorHandle, dataChunk->datablk, //
// &l_blkSize); //
// then, //
// I get OCI_ERROR - errcode = 21500 //
// internal error code, arguments: [kocgpn129], [2], [], [], [], [],...//
// OCI-21500: internal error code, arguments: [kope2upic954], [], ...//
// OCI-21500: internal error code, arguments: [kope2upic954], [], ...//
// //
// In debugger the dataChunk does not seem to be NULL //
// Yet the retval indicates that null or bad data //
// passed. //
//****************************************************//
}
}

retval = OCIObjectFree(oraEnvHandle, oraErrorHandle, (dvoid*) vecObjectList,
OCI_OBJECTFREE_FORCE);

retval = OCIObjectUnpin(oraEnvHandle, oraErrorHandle, (dvoid*) vecObjectTDO);

return(SUCCESSFUL);
}

Kamlesh Todai
Landmark Graphics
Houston, TX
(713) 839-3251