I'm trying to figure out why my application occasionally receives the error ORA-00942: table or view does not exist.

As the object in question clearly _does_ exist, I assume that the application changes the current_schema to some other schema which doesn't have said object.

In order to prove that, I have created a after servererror on database trigger which I would like to write the current_schema to alert.log

I first tried to use SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') to get the schema name, but that always returns the schema holding the trigger (SYS in that case)

My second guess was to use the SQL query select SCHEMANAME from V$SESSION where SID = SYS_CONTEXT('USERENV', 'SID') but that also only returned SYS instead of the current schema of the session.

Then I tried AUTHID CURRENT_USER but that also didn't solve it.


Is there a way?

Thanks!


Here's is what I tried:

1. connect to my application schema "DSCAN"

2. switch the session to some arbitrary other schema:

alter session set current_schema = MDDATA;

3. try to select from some non existing table:

select * from abc;

Trigger output in alert.log:

(what I'd like to see is SN:MDDATA instead of SN:SYS)

2024-03-01 14:23:23 2024-03-01T13:23:22.415858+00:00
2024-03-01 14:23:23 PDB1(3):*** SERVERERROR ORA-00942 U:DSCAN SN:SYS #453 * T:unknown * P:DataGrip * M:DataGrip * A: * ID: * CI: ***
2024-03-01 14:23:23 PDB1(3):select * from abc
2024-03-01 14:23:23 PDB1(3):--------------------------------------------------------
2024-03-01 14:23:23 PDB1(3):ORA-00942: table or view does not exist
2024-03-01 14:23:23 PDB1(3):********************************************************

Here's my trigger code:

CREATE OR REPLACE TRIGGER SYS.AFTER_SERVERERROR after servererror on database
begin
  execute immediate ('begin afterservererror; end;');
exception when others then
  -- ignore any further exceptions to prevent infinite loop
  null;
end;

create or replace procedure sys.afterServerError
is

  a       ora_name_list_t;
  s       varchar2(32767 byte); -- SQL text
  s1      varchar2(32767 byte); -- SQL text
  s2      varchar2(32767 byte); -- SQL text
  sn      varchar2(32767 byte); -- schema name
  m       varchar2(32767 byte); -- Error Message
  n       number;

begin

  if ORA_SERVER_ERROR(1) = 22289 then return; end if;

  execute immediate 'select SCHEMANAME from V$SESSION where SID = SYS_CONTEXT(''USERENV'', ''SID'')' INTO SN;

  s := s ||'*** '||ORA_SYSEVENT||' ORA-'||ltrim(to_char(ORA_SERVER_ERROR(1), '00000'))
         ||' U:'||user
         ||' SN:'||SN
         ||' #'||SYS_CONTEXT('USERENV', 'SID')
         ||' * T:'||SYS_CONTEXT('USERENV', 'TERMINAL')
         ||' * P:'||SYS_CONTEXT('USERENV', 'CLIENT_PROGRAM_NAME')
         ||' * M:'||SYS_CONTEXT('USERENV', 'MODULE')
         ||' * A:'||SYS_CONTEXT('USERENV', 'ACTION')
         ||' * ID:'||SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER')
         ||' * CI:'||SYS_CONTEXT('USERENV', 'CLIENT_INFO')||' ***' || chr(10);

  n := ora_sql_txt(a);
  if n > 0 then
    for i in 1..n loop
      s1 := s1 || replace(a(i), chr(0), '');
    end loop;
    s1 := s1 || chr(10);
    s := s || s1;
  end if;

  n := ora_original_sql_txt(a);
  if n > 0 then
    for i in 1..n loop
      s2 := s2 || replace(a(i), chr(0), '');
    end loop;
    s2 := s2 || chr(10);
    if s2 != s1 then
      s := s || '*** ora_original_sql_txt: ******************************' || chr(10) || s2;
    end if;
  end if;

  n := ora_server_error_depth;
  if n > 0 then
    s := s || '--------------------------------------------------------' || chr(10);
    for i in 1..n loop
      s := s || ora_server_error_msg(i);
    end loop;
  end if;

  s := s || '********************************************************';

  dbms_system.ksdwrt(2, s);
  dbms_system.ksdfls;

exception
  when others then
    dbms_system.ksdwrt(2, '*** another error ************************************');
    dbms_system.ksdfls;
end;