2

Hello I have created a Database Link from Oracle to SQL Server 2008 using Oracle Gateway.

DB LINK:

create public database link mssql
connect to "user" identified by "password" 
using 'gateway-SID';

When I use a simple query as SELECT * FROM TABLE@MSSQL the results are clearly ok.

The problem occurs when I select a distinct column from a table e.g.

SELECT COLUMN_NAME FROM TABLE@mssql

I get a query error from my SQL Developer saying:

ORA-00904: "CUSTOMERID": invalid identifier
00904. 00000 - "%s: invalid identifier"
Cause:
Action:
Error at Line: 1 Column: 8

Can anyone help me on this please?

VC1
  • 1,660
  • 4
  • 25
  • 42
charilaos13
  • 543
  • 2
  • 7
  • 19

1 Answers1

3

(Disclaimer: I'm no SQL Server expert, but I'll give it a go)

SQL Server is case sensitive - you have to quote your column names, so instead of 

SELECT COLUMN_NAME FROM TABLE@mssql

you need

SELECT "COLUMN_NAME" FROM TABLE@mssql

or even

SELECT "COLUMN_NAME" FROM "TABLE"@mssql

See Oracle forums on SQL Server, Oracle Gateway and ORA-00904

Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
  • actually you don't "have" to quote the object names - it is just that in some cases they may be case-sensitive (in SQL Server), and thus it is not clear what is the referred object - thankfully you can use quotes to make names clearer, like in OS shells ... – hello_earth Oct 13 '14 at 11:28