Skip to Main Content
  • Questions
  • How to find current_schema from within PL/SQL


Question and Answer

Connor McDonald

Thanks for the question, Michael.

Asked: March 01, 2024 - 1:37 pm UTC

Last updated: March 21, 2024 - 12:28 am UTC

Version: 19.3

Viewed 1000+ times

You Asked

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?


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:

  execute immediate ('begin afterservererror; end;');
exception when others then
  -- ignore any further exceptions to prevent infinite loop

create or replace procedure sys.afterServerError

  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;


  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')
         ||' * M:'||SYS_CONTEXT('USERENV', 'MODULE')
         ||' * A:'||SYS_CONTEXT('USERENV', 'ACTION')
         ||' * 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);

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

and Chris said...

Sorry, I don't know of a way to do this. Triggers are run with definer's rights. And within a definer's rights program, CURRENT_SCHEMA reports the name of its owner.

Some other things you could check:

- The database raises ORA-942 when you don't have permission to access the table in question. Check for users without grants on the table in question and/or have access via roles. If users have access via roles, it could be that the role is temporarily disabled when you get this error
- If there's a synonym or view with the same name that's changed when this happens.
- If you're using Edition-based Redefinition, that the object exists in the current edition


  (2 ratings)



Mikhail Velikikh, March 12, 2024 - 11:54 am UTC

> Is there a way?

SQL> select sys_context('userenv', 'sessionid') from dual;


SQL> alter session set current_schema=mddata;

Session altered.

SQL> select * from abc;
select * from abc
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select xmltype.createxml(cursor(select * from dba_audit_exists where sessionid=120043)) from dual;

<?xml version="1.0"?>

    <SQL_TEXT>select * from abc</SQL_TEXT>

It is possible to obtain the "current" schema even in a trigger in your scenario if you are ready to resort to X$KGLLK/DBA_KGLLOCK.
Chris Saxon
March 12, 2024 - 6:24 pm UTC

Thanks for sharing - though it relies on traditional auditing. This is desupported in 23c so won't work from that release onwards.

if you are ready to resort to X$KGLLK/DBA_KGLLOCK.

Accessing undocumented objects like this is unsupported, so I'd advise against this!

Re: audit not exist

Narendra, March 13, 2024 - 7:46 am UTC

Hello Chris/Connor,

Any reason why unified auditing doesn't support audit not exist feature of standard auditing?
I have to admit I was not aware of this option before reading this question but your response appears to suggest it is not available in unified auditing.
Sounds like a good option to have

Thanks in advance,
Connor McDonald
March 21, 2024 - 12:28 am UTC

I spoke to the audit team - It is on the road map

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library