This discussion is archived
1 Reply Latest reply: Nov 5, 2012 8:31 AM by clcarter RSS

Query from OCI-11.2.0.3 library to 10g Express DB not returning results

910885 Newbie
Currently Being Moderated
Hi,

I am trying to fetch column data using the following query run on 10g Express DB running on Windows 7.

select a.column_name, a.data_type, a.data_length, a.NULLABLE, nvl(c.CONSTRAINT_TYPE, 0), nvl(e.index_type, 0) from
dba_tab_cols a left join dba_cons_columns b on ( a.OWNER=b.owner and a.TABLE_NAME=b.TABLE_NAME and a.column_name=b.column_name)
left join dba_constraints c on (b.CONSTRAINT_NAME=c.CONSTRAINT_NAME and b.OWNER=c.owner and b.TABLE_NAME=c.TABLE_NAME)
left join dba_ind_columns d on ( a.OWNER=d.table_owner and a.TABLE_NAME=d.TABLE_NAME and a.column_name=d.column_name)
left join dba_indexes e on (d.index_name=e.index_name and a.OWNER=e.owner and a.TABLE_NAME=e.TABLE_NAME)
where a.table_name=:TBLNAME

My VC++ application is linked against "instantclient-sdk-nt-11-2-0-3-0\lib\msvc\oci.lib".

Calls to OCIStmtPrepare2 and following OCIBindByName are succeeding.

text inputval[32] = {'\0'};
ub2 inputvallen = 32;
OCIBind *pbnd = 0;

ret = OCIBindByName(stmthp, &pbnd, errhp, (text*)":TBLNAME",
strlen(":TBLNAME"), (dvoid*)inputval,
(sword)inputvallen, SQLT_STR, (dvoid*)0,
(ub2*)0,(ub2*)0, (ub4)0, (ub4*)0, OCI_DEFAULT);

I am setting the TBLNAME in single quotes in inputval variable before the call to OCIStmtExecute, which is also passing.

But, OCIStmtFetch2 always returns 0 rows (OCI_NO_DATA).

If I change this query to use the tablename explicitly in the above select statement, it works fine and I get rows > 0.

What could be wrong in this call to OCIBindByName? Or is there a compatibility issue between OCI-11.2.0.3 library and 10g Express database?

Please guide.

Thanks and Regards,
- ganesh

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points