oracle-gateway-ora-00904-invalid-identifier-how-fixed-it

oracle-gateway-ora-00904-invalid-identifier-how-fixed-it

1. symptoms:
SQL> select count(*) from dbo.tab01@dl_dg4mssqldb where pkey = 101;
Error starting at line : 15 in command –
select count(*) from dbo.tab01@dl_dg4mssqldb where pkey = 101
Error at Command Line : 15 Column : 61
Error report –
SQL Error: ORA-00904: “pkey”: invalid identifier
00904. 00000 – “%s: invalid identifier”
*Cause:
*Action:
SQL>
SQL> select pkey from dbo.tab01@dl_dg4mssqldb;
select pkey from dbo.tab01@dl_dg4mssqldb
*
ERROR at line 1:
ORA-00904: “pkey”: invalid identifier

SQL> select col2 from dbo.tab01@dl_dg4mssqldb;
select col2 from dbo.tab01@dl_dg4mssqldb
*
ERROR at line 1:
ORA-00904: “col2”: invalid identifier
SQL> SELECT * FROM tab02@dl_dg4mssqldb where gkey=631;
SELECT * FROM tab02@dl_dg4mssqldb where gkey=631
*
ERROR at line 1:
ORA-00904: “GKEY”: invalid identifier
SQL> select count(*) from “dbo.tab01″@dl_dg4mssqldb where “pkey” = 101;
select count(*) from “dbo.tab01″@dl_dg4mssqldb where “pkey” = 101
*
ERROR at line 1:
ORA-00942: table or view does not exist
[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid object name ‘dbo.tab01’. {42S02,NativeErr = 208}[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL
Server]The metadata could not be determined because every code path results in an error; see previous errors for some of these. {HY000,NativeErr = 11529}
ORA-02063: preceding 2 lines from dl_dg4mssqldb

2. Cause:
https://www.orafaq.com/forum/t/204251/
https://community.oracle.com/tech/developers/discussion/606191/invalid-identifier-with-sql-from-dblink-table

https://community.oracle.com/tech/developers/discussion/2299633/oracle-gateway-ora-00904-invalid-identifier-how-fixed-it
https://community.oracle.com/mosc/discussion/3816088/gtw-error-ora-00904-calling-a-sql-server-2008-function
DG4ODBC Returns ORA-904 Selecting From SQL*Server Using FreeTDS ODBC Driver (Doc ID 978055.1)

• SQL*Server is case sensitive for object names so you need to surround the column names with double quotes to preserve the case sent to SQL*Server –
select “empId”,”description” from rvwHLoanApp@dg4msql;
• Depending on the SQL*Server configuration you may also have to surround the table name in quotes also
select “empId”,”description” from “rvwHLoanApp”@dg4msql;
• There is no setting that would allow you to drop the double quotes round the object names as the translation to upper case is made by the Oracle database.
The only solution would be to create views in Oracle that translate the names to upper case, for example –
CREATE VIEW EMP (EMPNO, ENAME, SAL, HIREDATE)
AS SELECT “empno”, “ename”, “sal”, “hiredate”
FROM “emp”@DG4MSQL;
and then select from the view –

SELECT EMPNO, ENAME FROM EMP;
• This is discussed in the documentation – Oracle® Database Gateway for SQL Server User’s Guide, 11g Release 2 (11.2) , in the section – SQL Server Gateway Features and Restriction – Case Sensitivity
https://docs.oracle.com/en/database/oracle/oracle-database/19/gmswn/database-gateway-sqlserver-features.html#GUID-CCAB7265-35ED-42FD-84B8-D1BA0C30B1C4

3. solution:
col col2 format a25
col col3 format a10
col col4 format a10
SELECT * FROM “tab02″@dl_dg4mssqldb where “gkey”=631;SQL> SQL> SQL>
gkey col2 col3 col4 create_ti changed cdhlite_t
———- ————————- ———- ———- ——— ——— ———
631 Changed to RRRRRRRRRR NNNNNNNNNN HHHHHHHHHH 10-FEB-21 10-FEB-21 10-FEB-21
SQL> SELECT “gkey” FROM “tab02″@dl_dg4mssqldb where “gkey”=631;
gkey
———-
631
SQL> select “col2” from “tab01″@dl_dg4mssqldb where “pkey” = 101;
col2
———-
76562

SQL> select count(*) from dbo.”tab01″@dl_dg4mssqldb where “pkey” = 101;
COUNT(*)
———-
1
select * from “dbo”.”tab01″@dl_dg4mssqldb where “pkey” = 101;

Leave a Reply

Your email address will not be published. Required fields are marked *