OCI: problem with passing new object (table) to PL/SQL (for bulk insert/direct path loading)

From: Alexandru Sclearuc <asclearuc_at_yahoo.com>
Date: 16 Jul 2003 10:05:41 -0700
Message-ID: <b212587c.0307160905.c05ad79@posting.google.com>

Hi,

I would like to call a PL/SQL procedure from OCI program, and this procedure receives a new type (table of char(30)) as a parameter. The client (OCI) application just crashes with access violation on OCIStmtExecute() stage.
The procedure that should be called from client application, works from SQL*plus smoothly.

I'm quite new to OCI, so could you please tell me what I'm doing wrong (or at least if my way doing things is absolutely wrong then advise smth else = preferrably with examples;).

I have:
Personal Oracle8i 8.1.7 Release

• Source: SQL part --------------- -- type definition & table definition CREATE OR REPLACE TYPE table_char IS TABLE OF char(30); CREATE TABLE tab1_collection_avs_char30 ( id CHAR(30) NOT NULL );
  • PL/SQL procedure to be called create or replace package body TEST_COLLECTION as procedure INSERT_FORALL_AVS_CHAR30 ( in_char IN table_char )IS BEGIN

forall b in in_char.first .. in_char.last

   INSERT INTO tab1_collection_avs_char30 VALUES(in_char(b) );

END INSERT_FORALL_AVS_CHAR30 ;
end TEST_COLLECTION;
/

• Source: OCI part ---------------
  // here I provide only some basics,
  // a lot is skipped (like declaration of variables and error checking)
  string procCall("BEGIN TEST_COLLECTION.INSERT_FORALL_AVS_CHAR30(:1); END;"); string oracleTypeName("TABLE_CHAR"); string data("Test"); sword errCode;

// statement preparation

errCode = OCIHandleAlloc(ociEnv, (dvoid **) &ociStmt,

                         OCI_HTYPE_STMT, 0, 0);
errCode = OCIStmtPrepare(ociStmt, ociError, 
                         (CONST text*)procCall.c_str(),
                         procCall.length(),
                         OCI_NTV_SYNTAX, OCI_DEFAULT);

// binding - 1st stage

errCode = OCIBindByPos(ociStmt, &ociBind, ociError,

                         1, 0, 0, SQLT_NTY, 0, 0,
                         0, 0, 0, OCI_DEFAULT);
errCode = OCITypeByName(ociEnv, ociError, ociSvcCtx, 0, 0,
                         (CONST text*)oracleTypeName.c_str(), 
                         oracleTypeName.size(),
                         0, 0, OCI_DURATION_SESSION, 
                         OCI_TYPEGET_HEADER, &tdo);

// object creation (to bind it using OCIBindObject)
OCITable *ociTable;
errCode = OCIObjectNew(ociEnv, ociError, ociSvcCtx,

                       OCI_TYPECODE_TABLE,  tdo,    
                       0, OCI_DURATION_DEFAULT, 
                       TRUE, (dvoid **)&ociTable);

OCIString *ociString = 0;
errCode = OCIStringAssignText(ociEnv, ociError,

                       (text*)data.c_str(), data.size(), 
                       &ociString);

sb2 ociCollIndexNull = OCI_IND_NULL;

errCode = OCICollAppend(ociEnv, ociError, 
                       (CONST dvoid*)ociString, 
                       (CONST dvoid*)&ociCollIndexNull, 
                       ociTable);

sb4 dataSize;
errCode = OCICollSize(ociEnv, ociError,

                      ociTable, &dataSize);

// binding - 2nd stage

errCode = OCIBindObject(ociBind, ociError, tdo,

                      (dvoid **)ociTable, 
                      (ub4*)&dataSize, 0, 0);



// execution

//         VVVVVVVVVVVVV - crash over here

errCode = OCIStmtExecute(ociSvcCtx, ociStmt, ociError, 
                         1, 0, 0, 0, OCI_DEFAULT);

if((OCI_OK(errCode) || errCode == OCI_NO_DATA) ) {

    errCode = OCITransCommit(ociSvcCtx, ociError, OCI_DEFAULT); } else {

   OCITransRollback(ociSvcCtx, ociError, OCI_DEFAULT); }

Thanks,
Alexandru Sclearuc Received on Wed Jul 16 2003 - 12:05:41 CDT