Exception handling with ORA_RSA

The ORA_RSA package offers a customized exception handling strategy inside the standard Pl/SQL EXCEPTION section. The reason for this approach is the lack of standard way to expose custom exceptions from within Java stored procedures.

In this section we are going to demonstrate the various exception cases exposed by the package and their meaning. The exception handling code when invoking methods from the package must be place when handling ORA_RSA.RSA_EXCEPTION:

BEGIN
  -- invoking ORA_RSA method here
EXCEPTION
   -- ORA_RSA exception handling 
   WHEN ORA_RSA.RSA_EXCEPTION THEN
     BEGIN
       IF ORA_RSA.GET_RSA_ERROR() = ORA_RSA.RSA_WRONG_PASSWORD_ERR THEN
         DBMS_OUTPUT.PUT_LINE('The password for the private key is not matching: ' || SQLERRM);
       ELSIF ORA_RSA.GET_RSA_ERROR() = ORA_RSA.RSA_KEY_ERR THEN
         DBMS_OUTPUT.PUT_LINE('The provided key is not a valid RSA key.');
       ELSIF ORA_RSA.GET_RSA_ERROR() = ORA_RSA.RSA_ENCRYPTION_ERR THEN
         DBMS_OUTPUT.PUT_LINE('Error when performing RSA operation: ' || SQLERRM);
       ELSIF ORA_RSA.GET_RSA_ERROR() = ORA_RSA.RSA_GENERAL_IO_ERR THEN
         DBMS_OUTPUT.PUT_LINE('I/O error: ' || SQLERRM);
       END IF;
     END;
 
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('General error : ' || SQLERRM );    	
END;

As you can see from the example PL/SQL code above, the exact reason for the error is returned from the ORA_RSA.GET_RSA_ERROR() function. The possible cases recognized by the package are as follows:

  • ORA_RSA.RSA_WRONG_PASSWORD_ERR – the password for unlocking a private key is wrong
  • ORA_RSA.RSA_KEY_ERR – the supplied public or private RSA key is corrupted
  • ORA_RSA.RSA_ENCRYPTION_ERR – general RSA encryption/decryption error
  • ORA_RSA.RSA_GENERAL_IO_ERR – general I/O error

Summary

This article contains an example section illustrating the provided by the DidiSoft ORA_RSA package exception handling strategy. Whenever you need a robust solution that may take recovery actions, this mechanism could be useful for you. It can be applied to all the methods offered by the package.