ORA-942 When Select Excel Table via DG4ODBC In A Procedure

(Doc ID 1380538.1)

Last updated on JANUARY 18, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 and later
Microsoft Windows (32-bit)

Symptoms

Using the Database Gateway for ODBC 11.1.0.7 to connect to MS Excel , a SELECT statement  executed in SQL*PLUS session is successful:

SELECT ROWNUM  FROM "Sheet1$"@EXCEL;

But  when it is executed  from a procedure or a package it fails with ORA-00942 error:


SQL> declare p integer;
  2  begin
  3  select rownum into p from "Sheet1$"@excel;
  4  end;
  5  /
select rownum into p from "Sheet1$"@excel;
                          *
ERROR at line 3:
ORA-06550: line 3, column 27:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored


SQL> begin
2 SELECT ROWNUM  "Sheet1$"@EXCEL;
3 end;
4 /
SELECT ROWNUM into p from "Sheet1$"@EXCEL;
*
ERROR at line 2:
ORA-06550: line 2, column 38:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 1:
PL/SQL: SQL Statement ignored

instead of the normal  error

ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement

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