PL/SQL Package Compilation Fails With Ora-00942 Using a Link to Dg4odbc (Doc ID 755488.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.4 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms

A PL/SQL package will not compile and gives an ORA-942 error if there is a select with a database link to SQL*Server that is using Database Gateway for ODBC (DG4ODBC).

 

An example is -

SQL> declare
2       CURSOR "AGLTRANSACT_c" IS
3              SELECT *
4                FROM "authors"@dg4odbc ;
5 begin
6      for rec_xx in "AGLTRANSACT_c" loop
7        null;
8      end loop;
9      rollback;
10 end;
11 /
FROM "authors"@dg4odbc ;
*
ERROR at line 4:
ORA-06550: line 4, column 24:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 5:
PL/SQL: SQL Statement ignored

 

A DG4ODBC debug trace will show the following errors -

SQL text from hgopars, id=1, len=31 ...
00: 53454C45 4354202A 2046524F 4D202073 [SELECT * FROM s]
10: 636F7474 202E2061 7574686F 727320 [cott . authors ]
Entered hgopoer at 2008/12/11-14:22:56
hgopoer, line 159: got native error 208 and sqlstate 42S02; message follows...
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'scott.authors'.[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
Exiting hgopoer, rc=0 at 2008/12/11-14:22:56
hgopars, line 367: calling SQLNumResultCols got sqlstate 42S02
Exiting hgopars, rc=942 at 2008/12/11-14:22:56

and

SQL text from hgopars, id=1, len=20 ...
00: 53454C45 4354202A 2046524F 4D202044 [SELECT * FROM D]
10: 55414C20 [UAL ]
Entered hgopoer at 2008/12/11-14:22:59
hgopoer, line 159: got native error 208 and sqlstate 42S02; message follows...
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'DUAL'.[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
Exiting hgopoer, rc=0 at 2008/12/11-14:23:00
hgopars, line 367: calling SQLNumResultCols got sqlstate 42S02
Exiting hgopars, rc=942 at 2008/12/11-14:23:00

 

then finally

SQL text from hgopars, id=1, len=32 ...
00: 53454C45 4354202A 2046524F 4D202050 [SELECT * FROM P]
10: 55424C49 43202E20 61757468 6F727320 [UBLIC . authors ]
Entered hgopoer at 2008/12/11-14:23:03
hgopoer, line 159: got native error 156 and sqlstate 42000; message follows...
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'PUBLIC'.[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
Exiting hgopoer, rc=0 at 2008/12/11-14:23:03
hgopars, line 367: calling SQLNumResultCols got sqlstate 42000
Exiting hgopars, rc=28500 at 2008/12/11-14:23:04 with error ptr FILE:hgopars.c LINE:397 ID:Preprocess number of columns

Issuing the same select from SQLPLUS is successful -

SQL> select * from "authors"@dg4odbc ;

au_id au_lname au_fname
----------- ---------------------------------------- --------------------
phone address city st
------------ ---------------------------------------- -------------------- --
zip contract
----- ----------
172-32-1176 White Johnson
408 496-7223 10932 Bigge Rd. Menlo Park CA
94025 1
...
...
...
998-72-3567 Ringer Albert
801 826-0752 67 Seventh Av. Salt Lake City UT
84152 1


23 rows selected.

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms