OCIAttrGet column names after OCIStmtExecute

Hi

I have a problem regarding getting column names by using the implicit describe from the OCIStmtExecute.

I notice that my column name I retrieve inside the text * field sometimes not only contains the name of the current column, but the next column as well.

For example, sys.all_views would give me:

column= OWNER datatype=1 width: 30 
column= VIEW_NAME datatype=1 width: 30 
column= TEXT_LENGTH datatype=2 width: 22 
column= TEXTTYPE_TEXT_LENGTHTYPE_TEXT datatype=8 width: 0 

where the 5th and 6th columns are joined with the 4th.

(FYI

SQL> desc sys.all_views

 Name                                      Null?    Type
 ----------------------------------------- --------
--------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 VIEW_NAME                                 NOT NULL VARCHAR2(30)
 TEXT_LENGTH                                        NUMBER
 TEXT                                               LONG
 TYPE_TEXT_LENGTH                                   NUMBER
 TYPE_TEXT                                          VARCHAR2(4000)
 OID_TEXT_LENGTH                                    NUMBER
 OID_TEXT                                           VARCHAR2(4000)
 VIEW_TYPE_OWNER                                    VARCHAR2(30)
 VIEW_TYPE                                          VARCHAR2(30)
 SUPERVIEW_NAME                                     VARCHAR2(30)

)

To reproduce this problem, I went into ora92/oci/samples/cdemosa.c and modified the file to include the following code inside the describe_table function. I have pasted that code below - the OCIDescribeAny within cdemodsa.c works fine and the column names are retrieved correctly.

sword retval;
OCIParam *parmp, *collst;
ub4 parmcnt;
ub2 numcols;
ub4 objid = 0;
OCIStmt *stmthp;
int i_width = 0;
int width = 0;
sb4 parm_status;
OCIParam *mypard = NULL;

ub2 dtype = 0;
text *col_name;
ub4 col_name_len = 0;
int counter = 1;

printf ("\nDescribing the table..\n");

if ((retval = OCIHandleAlloc((dvoid *) envhp, (dvoid **) &stmthp, OCI_HTYPE_STMT, (size_t) 0,
(dvoid **) 0)) != OCI_SUCCESS)

{
checkerr(errhp, status);
return;
}

if((retval = OCIStmtPrepare(stmthp, errhp, (text *) "select * from sys.all_views where 1=0",
(ub4) strlen((char *) "select * from sys.all_views where 1=0"),
OCI_NTV_SYNTAX, OCI_DEFAULT)) != OCI_SUCCESS) {
return;
}
if (retval = OCIStmtExecute (svchp, stmthp, errhp, (ub4)0, (ub4)0,
(OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT)!= OCI_SUCCESS)
{
checkerr(errhp, retval);
return;
}

parm_status = OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp,
(dvoid **) &mypard,(ub4) counter);

/* Loop only if a descriptor was successfully retrieved for current position, starting at 1 */
while (parm_status==OCI_SUCCESS)
{
counter ++;

/* Retrieve the column name attribute */ checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
(dvoid*) &col_name,(ub4 *) &col_name_len, (ub4) OCI_ATTR_NAME,
(OCIError *) errhp ));

/* Retrieve the data type attribute */
checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
(dvoid*) &dtype,(ub4 *) 0, (ub4) OCI_ATTR_DATA_TYPE,
(OCIError *) errhp ));

//col_name[col_name_len] = '\0';
checkerr (errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, &i_width, (ub4 *) 0,
(ub4) OCI_ATTR_DATA_SIZE, (OCIError *)errhp));

/*checkerr(errhp,OCIAttrGet((CONST dvoid *)paramhp,(ub4)OCI_DTYPE_PARAM,
(dvoid *)&nullok,(ub4 *)0,
(ub4)OCI_ATTR_IS_NULL,(OCIError *)errhp));
*/
printf("column= %s, datatype=%d, width=%d\n", col_name, dtype, i_width);

parm_status = OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp,
(dvoid **) &mypard,(ub4) counter);

} Received on Wed Jul 16 2003 - 19:33:45 CDT