0

I need to query data from SQL Server in Oracle using an ODBC database link. I have created the necessary configuration in Oracle to allow me to access the SQL Server database and query the data.

When I do:

select * from table@ODBC_LINK

i get all rows returned.

When I do:

select * from table@ODBC_LINK WHERE [Field] = 'Some Value'

I get an

ORA-000904 error; "Field" invalid identifier.

The field name is a valid field in the SQL-source database/table but it does not matter what I do, I am unable to use a WHERE clause as this always gives me the same ORA-error.

Using Oracle XE 11g and SQL Server 2012.

What is causing my problem?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
der_roedie
  • 27
  • 7
  • 2
    Oracle doesn't use brackets to escape names like SQL Server does. Omit them, or use double quotes if your names might contain special characters. – Jeroen Mostert Jun 06 '18 at 10:09
  • Uhm, `Oracle XE 11g SQL = SQL Server 2012` ? Nope! – LukStorms Jun 06 '18 at 10:11
  • @der_roedie - tag the correct database here. – Sudipta Mondal Jun 06 '18 at 10:40
  • Tags are editted – der_roedie Jun 06 '18 at 11:17
  • @der_roedie . . . There was no need to edit the tags. The commenter obviously didn't read the question, because you are using two different database products. – Gordon Linoff Jun 06 '18 at 11:57
  • How does the column heading for Field look in the results, *exactly*, when you do the successful query without the `WHERE` clause? I'm wondering if there's some case-sensitivity going on as well as the other possible issues. Mixing Oracle, ODBC and SQL Server may well give some slightly odd restrictions and translations along the way... (And does `SELECT Field FROM table@ODBC_LINK` work?) – Matt Gibson Jun 06 '18 at 12:23
  • @MattGibson; as long as I do not use a where clause I get results returned but when ever I enter a where clause the error is shown. I have checked for case-sensitivity and notation of the field names in the select part; this only works when i type `select "Field" from table@ODBC_LINK'. When I leave the qoutes away I get the same error when I add the quotes in the select and add a where the error appears again. If I add the Quotes to the where clause aswell, still the same error. – der_roedie Jun 06 '18 at 13:18

1 Answers1

0

Your query is being run in Oracle not SQL Server. The table comes from SQL Server, but not the parser.

So, first try no escape characters:

select * from table@ODBC_LINK WHERE Field = 'Some Value'

If you need to escape the name, use the Oracle escape character:

select * from table@ODBC_LINK WHERE "Field" = 'Some Value'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hi Gordon, first query results in ORA-000904. Second query gives: ORA-02070: database QACOMMENTS does not support some function in this context 02070. 00000 - "database %s%s does not support %s in this context" *Cause: The remote database does not support the named capability in the context in which it is used. *Action: Simplify the SQL statement. Error at Line: 7 Column: 53 – der_roedie Jun 06 '18 at 11:19
  • sorry for the formatting..... SO-n00b for posting questions and comments :) – der_roedie Jun 06 '18 at 11:21
  • @der_roedie . . . Does the query work when run natively on the remote server? – Gordon Linoff Jun 06 '18 at 11:40
  • Yes it does, no problems there. I have another database link on the Oracle XE host that uses ODBC to a different instance on the same SQL server that is working fine with select-queries and WHERE clauses... Thats whats driving me up the wall. – der_roedie Jun 06 '18 at 11:47
  • @der_roedie . . . What if you use a subquery? `select * from (select . . . ) x where field = . . . `. – Gordon Linoff Jun 06 '18 at 11:58
  • Tried: `select * from (select * from table@ODBC_LINK) x where x.field='some value'` This gives the same ORA-000904 error... – der_roedie Jun 06 '18 at 12:10
  • @der_roedie . . . I suspect there is something amiss about the field name -- some hidden character, case sensitivity, alternative character set. I'm not sure, but 000904 is related to bad identifiers. Perhaps the identifier is a reserved word in Oracle (or perhaps in SQL Server). – Gordon Linoff Jun 06 '18 at 13:14
  • @Gorden Linoff; I changed the field name I use in the where clause in the underlying SQL-table but still no luck, so I think we can rule out special characters of reserverd identifiers. – der_roedie Jun 06 '18 at 13:28