CODEWITHSUNDEEP
sqlsql-serversql-server-2008oracle11g
Hans Vallee
Hans Vallee

Reputation: 79

OLE DB provider [OraOLEDB.Oracle] for linked server [ORA] returned message ORA-00936: missing expression

I am having some issues with OPENQUERY.

I have this simple Linked Server SQL:

SELECT [ITEM_SERIAL].[SERIAL_NO]
       , [ITEM_SERIAL].[SUB_SEQ_NO_FIRM]
       , [ITEM_SERIAL].[ITEM_NO]
FROM [ORA]..[SSDIGICO].[ITEM_SERIAL]
ORDER BY [ITEM_SERIAL].[SERIAL_NO]

This runs smooth, no problem...

Now I am trying to use the OPENQUERY approach to improve performance such as:

SELECT * FROM OPENQUERY(ORA,'
    SELECT [ITEM_SERIAL].[SERIAL_NO]
       , [ITEM_SERIAL].[SUB_SEQ_NO_FIRM]
       , [ITEM_SERIAL].[ITEM_NO]
    FROM [SSDIGICO].[ITEM_SERIAL]
    ORDER BY [ITEM_SERIAL].[SERIAL_NO]
')

And for some reason I get the following error message:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORA" returned message "ORA-00936: missing expression". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "

Does any one got a clue as to why I get this error?

THANKS.

Upvotes: 0

Views: 5339

Answers (1)

Taryn
Taryn

Reputation: 247880

Oracle does not use square brackets around the tables or columns. The code should be:

SELECT * FROM OPENQUERY(ORA,'
    SELECT ITEM_SERIAL.SERIAL_NO
       , ITEM_SERIAL.SUB_SEQ_NO_FIRM
       , ITEM_SERIAL.ITEM_NO
    FROM SSDIGICO.ITEM_SERIAL
    ORDER BY ITEM_SERIAL.SERIAL_NO
')

Upvotes: 1

Related Questions