CODEWITHSUNDEEP
sqlsql-serveroracle-databaseoracle-sqldevelopergateway
charilaos13
charilaos13

Reputation: 543

Viewing SQL Server data to Oracle

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?

Upvotes: 2

Views: 2454

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30775

(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

Upvotes: 3

Related Questions