Forum Stats

  • 3,873,020 Users
  • 2,266,496 Discussions
  • 7,911,404 Comments

Discussions

Odbc Driver Errors - OPR4ONWY:U9IM8TAC:OI2DL65P

A materialized view was imported from the database without any apparent errors. Import was after the Oracle client was installed.

But, I am receiving Odbc driver error when using the admin tool to view data in a specific materialized view or when using answers to query data in the same materialized view:

Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 923, message: ORA-00923: FROM keyword not found where expected at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)

Oracle Odbc client is 10.2.0.1 and Oracle Database Server is 9.2.0.8. Odbc connection was created after the install of the Oracle client.

Materialized view has some columns calculated/derived from the base tables. These columns in the materialized view have been cast to specific datatypes.

I have granted select to the Odbc connection. And, I created a private synonym for the Odbc connection.

Materialized view can be queried without any problem from Sqlplus 10.2 or Toad 9.7 using the same connection as Odbc.

Odbc connection can be used to view data in other tables, views or materialized views without any problem.

Any recommendation on resolving issues with the Odbc definition of this materialized view?
Tagged:

Answers

  • rmoff
    rmoff Member Posts: 2,511
    edited Oct 19, 2009 10:22AM
    You could enable logging and check NQQuery.log to see what is being submitted to the database and see if it runs in sqldeveloper.

    Do you get the same error if you View Data from the admin tool?

    See also this thread 3574908 and this reference: http://www.dba-oracle.com/t_ora_00923_from_keyword_not_found_where_expected.htm
  • 609043
    609043 Member Posts: 46
    Good point, but I do not required qualified names. And, the ORA-00923 error is not due to a syntax error in the materialized view definition. So long as I do not use an Odbc connection, data can be queried. Note, the root problem most likely originates in the Odbc definition. The BI tool is using this Odbc definition for a connection to our database. The Odbc connection is from an XP client with service pack 3.
  • rmoff
    rmoff Member Posts: 2,511
    Are you using OCI or ODBC in your connection pool? It should be OCI I think.

    You could enable logging and check NQQuery.log to see what is being submitted to the database and see if it runs in sqldeveloper.

    Do you get the same error if you View Data from the admin tool?
  • 609043
    609043 Member Posts: 46
    We use OCI for our Odbc connections.

    The sqldeveloper tool would return the results without any problem, but it does not use Odbc as far as I know. So, it would not test the Odbc connectivity.

    Usage tracking is turned on and the related sql statement issued by BI looks normal. I do not see any problem with BI besides the Odbc error.

    And, yes. Both the admin tool and answers return the same error.

    I believe the problem originates in the Odbc connection.
  • 609043
    609043 Member Posts: 46
    I fat-fingered a column name for one of the calculated values. I had a dash in a column name instead of an underline character. Seems to work okay at present.
This discussion has been closed.