How to ignore compilation error in Oracle pl/sql when script is executed by certain types of user

Question

I wanted a simple method of automatically confirming which customer a schema belongs to when my team members log into SQLPlus, in order to avoid mistakes.

I configured glogin.sql as follows and it works great when logged in as a schema owner:

set echo off
set serveroutput on
set linesize 200

Declare
   sysDesc varchar2(255);
BEGIN

  if USER like '%SCHEMAOWNER%' then
    select DESCRIPTION into sysDesc from SCHEMA_INFO;
  else
    sysDesc := '(DBA User)';
  end if; 

  dbms_output.put_line('*******************************************************************');
  dbms_output.put_line('WARNING - THIS IS A CUSTOMER SYSTEM!!!');
  dbms_output.put_line('*******************************************************************' || chr(13) || chr(10));
  dbms_output.put_line('- Description:   ' || SysDesc);
  dbms_output.put_line('- User:  ' || USER);
  dbms_output.put_line('- Database:      ' || sys_context('USERENV','DB_NAME') || chr(13) || chr(10));
  dbms_output.put_line('*******************************************************************');

END;
/

Sometimes, however, we need to log in as a DBA user. DBA users can't see the SCHEMA_INFO table, so the user gets this error instead:

ERROR at line 9:
ORA-06550: line 9, column 43:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 9, column 5:
PL/SQL: SQL Statement ignored

I tried to add an Exception handler but then realised that it was a compilation error that I was getting so this was of no use. I also tried to use execute immediate, but couldn't find a way to use the output of the select statement.

Does anyone know how I can ignore the compilation error or run a different script/block depending on the user type?

MT0 · Accepted Answer · 2020-11-26 18:31:47Z

2

Create a custom exception for a non-existent table exception and then catch it if it occurs:

set echo off
set serveroutput on
set linesize 200

Declare
   sysDesc varchar2(255);
BEGIN

  if USER like '%SCHEMAOWNER%' then
    DECLARE
      table_or_view_does_not_exist EXCEPTION;
      PRAGMA EXCEPTION_INIT( table_or_view_does_not_exist, -942 );
    BEGIN
      EXECUTE IMMEDIATE 'select DESCRIPTION from SCHEMA_INFO' into sysDesc;
    EXCEPTION
      WHEN table_or_view_does_not_exist THEN
        -- You should validate that the user is a DBA here.
        sysDesc := '(DBA User)';
    END;
  else
    sysDesc := '(DBA User)';
  end if; 

  dbms_output.put_line('*******************************************************************');
  dbms_output.put_line('WARNING - THIS IS A CUSTOMER SYSTEM!!!');
  dbms_output.put_line('*******************************************************************' || chr(13) || chr(10));
  dbms_output.put_line('- Description:   ' || SysDesc);
  dbms_output.put_line('- User:  ' || USER);
  dbms_output.put_line('- Database:      ' || sys_context('USERENV','DB_NAME') || chr(13) || chr(10));
  dbms_output.put_line('*******************************************************************');

END;
/

edited Nov 26, 2020 at 18:31

answered Nov 26, 2020 at 14:45

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andy Over a year ago

Thanks for trying. Unfortunately I'm getting

ERROR at line 21: ORA-06550: line 21, column 45: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 21, column 7: PL/SQL: SQL Statement ignored

MT0 Over a year ago

@Andy Updated to use EXECUTE IMMEDIATE so that the existence of the table is not checked at compile time and so the exception would be raise at run time.

Collectives™ on Stack Overflow

How to ignore compilation error in Oracle pl/sql when script is executed by certain types of user

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related