Hi,
I am trying to associate a PL/SQL exception with a number and then use that to show my own error message to the caller. I keep on getting the default error message rather than my own error message. I have searched over the last couple of days and tried a number of variations but I still can't display my custom error message. I can't help but feel that I am missing something.
The code snippet below gives the same behaviour as I observe in my own PL/SQL code. I have run this on the LIVESQL link and I get the same behaviour.
Any hints or tips greatly received. I basically want to display 'blah' rather than 'ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error ORA-06512: at line 4'
DECLARE
l_rw_encrypted_string raw(2000):='nowi234';
aha EXCEPTION;
PRAGMA EXCEPTION_INIT(aha, -06502);
l_rw_encryption_key raw(32):='6AA28EBC54ECDBCE68791F14AE1BA6901133365DBF9CF5E16C1A55B3E02F78FD';
l_pi_encryption_type pls_integer:=DBMS_CRYPTO.ENCRYPT_AES256+ DBMS_CRYPTO.CHAIN_CBC+ DBMS_CRYPTO.PAD_PKCS5;
l_rw_decrypted_string raw(2000);
l_rw_output_hash_value raw(32);
BEGIN
l_rw_decrypted_string := DBMS_CRYPTO.DECRYPT(l_rw_encrypted_string, l_pi_encryption_type, l_rw_encryption_key);
dbms_output.put_line('Decrypted value=> '||UTL_I18N.RAW_TO_CHAR(l_rw_decrypted_string, 'AL32UTF8'));
EXCEPTION
WHEN aha THEN
RAISE_APPLICATION_ERROR(-20005,'blah ',TRUE);
END;Thanks,
John
Your error is occurring at line 2.
That is *before* the block (begin-end) has actually commenced, and an exception handler is part of the begin-end block, so we never made it that far.
For example:
SQL> DECLARE
2 l_rw_encrypted_string raw(2000):='nowi234';
3
4 aha EXCEPTION;
5 PRAGMA EXCEPTION_INIT(aha, -06502);
6
7 l_rw_encryption_key raw(32):='6AA28EBC54ECDBCE68791F14AE1BA6901133365DBF9CF5E16C1A55B3E02F78FD';
8 l_pi_encryption_type pls_integer:=DBMS_CRYPTO.ENCRYPT_AES256+ DBMS_CRYPTO.CHAIN_CBC+ DBMS_CRYPTO.PAD_PKCS5;
9 l_rw_decrypted_string raw(2000);
10 l_rw_output_hash_value raw(32);
11
12 BEGIN
13 l_rw_decrypted_string := DBMS_CRYPTO.DECRYPT(l_rw_encrypted_string, l_pi_encryption_type, l_rw_encryption_key);
14
15 dbms_output.put_line('Decrypted value=> '||UTL_I18N.RAW_TO_CHAR(l_rw_decrypted_string, 'AL32UTF8'));
16 EXCEPTION
17 WHEN aha THEN
18 RAISE_APPLICATION_ERROR(-20005,'blah ',TRUE);
19 END;
20 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at line 2
SQL>
SQL>
SQL> DECLARE
2 l_rw_encrypted_string raw(2000);
3
4 aha EXCEPTION;
5 PRAGMA EXCEPTION_INIT(aha, -06502);
6
7 l_rw_encryption_key raw(32):='6AA28EBC54ECDBCE68791F14AE1BA6901133365DBF9CF5E16C1A55B3E02F78FD';
8 l_pi_encryption_type pls_integer:=DBMS_CRYPTO.ENCRYPT_AES256+ DBMS_CRYPTO.CHAIN_CBC+ DBMS_CRYPTO.PAD_PKCS5;
9 l_rw_decrypted_string raw(2000);
10 l_rw_output_hash_value raw(32);
11
12 BEGIN
13 l_rw_encrypted_string :='nowi234';
14 l_rw_decrypted_string := DBMS_CRYPTO.DECRYPT(l_rw_encrypted_string, l_pi_encryption_type, l_rw_encryption_key);
15
16 dbms_output.put_line('Decrypted value=> '||UTL_I18N.RAW_TO_CHAR(l_rw_decrypted_string, 'AL32UTF8'));
17 EXCEPTION
18 WHEN aha THEN
19 RAISE_APPLICATION_ERROR(-20005,'blah ',TRUE);
20 END;
21 /
DECLARE
*
ERROR at line 1:
ORA-20005: blah
ORA-06512: at line 19
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at line 13
SQL>
SQL>
If you get an exception in the DECLARE section it will be pushed (and caught if coded) to the *caller* (which could be its own PL/SQL block with its own exception handler) or a client application.