ExcelBanter - View Single Post - error message in Microsoft Query - ORA-00903 invalid table name

Jamie Collins

(bert) wrote ...

I receive the error-message for an easy select (Microsoft Excel2000
SP3 - Query)like:
select SYSDATE from dual

Occurs this error due to a wrong odbc-driver???

In a sql-navigator the select works fine.

I assume we are talking about MS Query (Data, Import External Data,
New Database Query...)

In MS Query, try using the GUI tools to build a simple SELECT * FROM
query e.g. choose Table, Add Table, when added double click on the *,
then View, SQL. The resulting syntax is a strange dialect (especially
when aliasing tables) but may give you some clues.

You'll want to ensure MS Query has no hope of mistakenly recognizing
your Oracle SQL as being its own dialect: if it does it will try and
validate it on the client side and will have problems with proprietary
syntax such as SYSDATE. You want MS Query to *fail* to recognize the
syntax, in which circumstance it will use odbc 'pass through'
functionality to pass the sql without validation to be execute on the
server side.

Or it could just be an issue with the machine's set up e.g. have you
installed the Oracle client?

Jamie.

--