plsql Handling connexion error exceptions


Example

Each standard Oracle error is associated with an error number. Its important to anticipate what could go wrong in your code. Here for a connection to another database it can be:

  • -28000 account is locked
  • -28001 password expired
  • -28002 grace period
  • -1017 wrong user / password

Here is a way to test what goes wrong with the user used by the database link:

declare
  v_dummy number;
begin
  -- testing db link
  execute immediate 'select COUNT(1) from dba_users@pass.world' into v_dummy ;
  -- if we get here, exception wasn't raised: display COUNT's result
  dbms_output.put_line(v_dummy||' users on PASS db');

EXCEPTION
  -- exception can be referred by their name in the predefined Oracle's list
    When LOGIN_DENIED 
    then  
        dbms_output.put_line('ORA-1017 / USERNAME OR PASSWORD INVALID, TRY AGAIN');
    When Others 
    then 
  -- or referred by their number: stored automatically in reserved variable SQLCODE    
        If  SQLCODE = '-2019'
        Then    
          dbms_output.put_line('ORA-2019 / Invalid db_link name');
        Elsif SQLCODE = '-1035'
        Then
          dbms_output.put_line('ORA-1035 / DATABASE IS ON RESTRICTED SESSION, CONTACT YOUR DBA');        
        Elsif SQLCODE = '-28000'
        Then
          dbms_output.put_line('ORA-28000 / ACCOUNT IS LOCKED. CONTACT YOUR DBA');
        Elsif SQLCODE = '-28001'
        Then
          dbms_output.put_line('ORA-28001 / PASSWORD EXPIRED. CONTACT YOUR DBA FOR CHANGE');
        Elsif SQLCODE  = '-28002'
        Then
          dbms_output.put_line('ORA-28002 / PASSWORD IS EXPIRED, CHANGED IT');
        Else
   -- and if it's not one of the exception you expected
          dbms_output.put_line('Exception not specifically handled');
          dbms_output.put_line('Oracle Said'||SQLCODE||':'||SQLERRM);
        End if;
END;
/