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

user_2173762 0
Viewed 280

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?

sqlplus plsql oracle
1 Answers

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;
/
user_15092640
Related