1 Reply Latest reply: Nov 5, 2012 10:31 AM by clcarter RSS

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


      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- library and 10g Express database?

      Please guide.

      Thanks and Regards,
      - ganesh