René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Revealing the offending sql statement for an ORA-00942

Here's an (admittedly esoteric) way to use diagnostic events to create a trace file when a certain error occurs.
A few tables are created: 
create table table_one   (a number, b number);
create table table_two   (a number, b number);
create table table_three (a number, b number);
create table table_four  (a number, b number);
create table table_five  (a number, b number);
Some selects are issued against the tables. 
set feedback off

select * from table_one;
select * from table_two;
select * from table_htree;
select * from table_four;
select * from table_five;
The third select statement will obviously fail with a ORA-00942 because the table name was misspelled.
Now, let's prepend that these selects are issued within an application over whose source coude we don't have any control. Further, the only thing that the application reports, is the ORA-00942. We're left in the dark what table (or view) is actually the problem.
So, the following event is set which will write a trace file whenever an ORA-00942 occurs. 
alter system set events '942 trace name errorstack level 1';
The selects are re-executed.
Now, there is an entry in the alert log: 
Errors in file d:\oracle\product\10.1.0\db_1\rdbms\trace\ora10_ora_696.trc:
ORA-00942: table or view does not exist
The entry comes with a file name. The beginning of the file reads:
d:\oracle\product\10.1.0\db_1\rdbms\trace\ora10_ora_696.trc
Dump file d:\oracle\product\10.1.0\db_1\rdbms\trace\ora10_ora_696.trc
Fri Mar 25 00:36:40 2005
ORACLE V10.1.0.2.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU             : 1 - type 586
Process Affinity: 0x00000000
Memory (A/P)    : PH:516M/1022M, PG:2058M/2459M, VA:1822M/2047M
Instance name: ora10

Redo thread mounted by this instance: 1

Oracle process number: 10

Windows thread id: 696, image: ORACLE.EXE (SHAD)


*** ACTION NAME:() 2005-03-25 00:36:40.609
*** MODULE NAME:(SQL*Plus) 2005-03-25 00:36:40.609
*** SERVICE NAME:(SYS$USERS) 2005-03-25 00:36:40.609
*** SESSION ID:(21.9) 2005-03-25 00:36:40.609
*** 2005-03-25 00:36:40.609
ksedmp: internal or fatal error
ORA-00942: table or view does not exist
Current SQL statement for this session:
select * from table_htree
----- Call Stack Trace -----

Using a servererror trigger

Of course, it's probably easier to use a servererror trigger to find offending sql statements.