OCI Data Type question

From: Rob Diaz <rdiaz_at_ebudgets.com>
Date: Fri, 10 Nov 2000 12:35:52 -0500
Message-ID: <ejWO5.2903$zd5.120288@news-east.usenetserver.com>

Hi. I have a program written in C++ running against Oracle 8. I use OCI to do all of my database work.

I have a question about data types. When I run a query through OCI, I use the OCI Framework to get the data types, sizes and names from the query, since these queries cannot be known at compile time.

Anyway, Oracle (or OCI) seems to return the wrong data type sometimes. Here is an example:

TableA:

ID varchar2
Label varchar2
Jan float(126)
Feb float(126)
Mar float(126)

the query:

select ID, Label, Jan,Feb,Mar, Jan+Feb+Mar Total from TableA;

Using the calls to the OCI functions, the data type for the ID and Label columns comes back as SQLT_STR or OCI_TYPECODE_VARCHAR2. For Jan, Feb, and Mar, the data type is 2 (SQLT_NUM) with a precision of 126. However, the results of the Total column are data type 2 (SQLT_NUM) with a precision of 0. I have debugged the application and FORCED the app to read the value coming back as though it had precision=126 and the data is fine.

Why does OCI think the precision is 0?

The code I use to determine the types is:

OCIParam *p_Parameter;
ub2 lType;
ub4 col_name_len=0;
WORD parm_status;
long lSize=0;

text *namep;
ub1 precision;

parm_status = OCIParamGet(m_pStmt, OCI_HTYPE_STMT, theApp.m_pError,
(void**)&p_Parameter, i+1);

if(parm_status==OCI_SUCCESS)
{

      parm_status = OCIAttrGet(p_Parameter, OCI_DTYPE_PARAM,
(dvoid*)&namep,(ub4*)&col_name_len, OCI_ATTR_NAME,

                 theApp.m_pError);
      parm_status = OCIAttrGet(p_Parameter, OCI_DTYPE_PARAM, &lSize,0,
OCI_ATTR_DATA_SIZE, theApp.m_pError);
      parm_status = OCIAttrGet(p_Parameter, OCI_DTYPE_PARAM, &lType,0,
OCI_ATTR_DATA_TYPE, theApp.m_pError);
      parm_status = OCIAttrGet(p_Parameter, OCI_DTYPE_PARAM, &precision,0,

OCI_ATTR_PRECISION, theApp.m_pError);
}

I have checked the scale as well (seems to always be 0). I've tried to get use OCI_ATTR_TYPECODE to see if that helps, but the call errors out.

Any ideas? Your help is greatly appreciated.

Thanks!
Rob Received on Fri Nov 10 2000 - 11:35:52 CST