Gestione delle eccezioni in PL/SQL

Gia in due precedenti post (questo e questo) ho parlato di come “attrezzare” opportunamente le procedure PL/SQL per facilitare la manutenzione ed individuare facilmente errori o problemi.  Qualche giorno fa però ho letto sul blog di Chen Shapira un post in cui descrive una situazione in cui si è trovata coinvolta a causa di procedure PL/SQL in cui non è stata fatta una gestione delle eccezioni adeguata.

La situazione che Chen descrive ha veramente dell’incredibile,  un DBA ha lavorato per sei mesi a un progetto, il lunedì va in ferie e il mercoledì è previsto un passaggio in produzione, senza alcun passaggio di consegne decente. Naturalmente martedì mattina vengono fuori i problemi. Non pensavo che in aziende grandi e strutturate, dove si usa ITIL e dove ci sono “team” di DBA ecc ecc. succedessero cose simili.

Tralasciando quindi il contesto, mi focalizzerei sul problema, delle procedure PL/SQL che vanno in errore  e  gestiscono l’eccezione in modo generico in questo modo:

EXCEPTION
WHEN OTHERS
THEN
INSERT INTO NIGHT_JOB
(status, last_run_stmp)
VALUES (‘failed’, SYSDATE);
COMMIT;

Ebbene, questo è un bel problema, fra i commenti al post però c’è un suggerimento interessante, mettere un trigger sulla tabella NIGHT_JOB.  La soluzione proposta in realtà ha un errore e, come replica Chen, non va bene l’idea di rilanciare un’eccezione.

Il motivo per cui la soluzione proposta non è corretta  è il fatto che a quanto pare nel corpo del trigger le funzioni SQLCODE e SQLERRM si resettano,  anche se pare il fatto di rilanciare l’eccezione trasmetta anche l’errore originale, ecco qui un test:

SVILUPPO40@perseo10 > drop table test_1
2  /

Tabella eliminata.

SVILUPPO40@perseo10 > create table test_1 (
2   text varchar2(12),
3   datetime timestamp with time zone
4  )
5  /

Tabella creata.

SVILUPPO40@perseo10 >
SVILUPPO40@perseo10 > CREATE OR REPLACE TRIGGER TR_test_exc_01 before INSERT
2  ON test_1
3  FOR EACH ROW
4  begin
5  RAISE_APPLICATION_ERROR(SQLCODE,SQLERRM);
6  END;
7  /

Trigger creato.

SVILUPPO40@perseo10 >
SVILUPPO40@perseo10 > create or replace procedure test_catch_exception is
2   MY_EXCEPTION_IN_LOOP EXCEPTION;
3   PRAGMA EXCEPTION_INIT(MY_EXCEPTION_IN_LOOP, -20001);
4   a varchar2(512);
5  BEGIN
6   FOR i IN 1..10 LOOP
7   IF i = 5 THEN
8     RAISE MY_EXCEPTION_IN_LOOP;
9   END IF;
10  END LOOP;
11  EXCEPTION
12  WHEN OTHERS THEN
13   INSERT INTO TEST_1 VALUES (‘error’,systimestamp);
14  END;
15  /

Procedura creata.

SVILUPPO40@perseo10 >
SVILUPPO40@perseo10 > exec test_catch_exception;
BEGIN test_catch_exception; END;

*
ERRORE alla riga 1:
ORA-21000: numero errore dell’argomento in raise_application_error di  0 e fuori intervallo
ORA-06512: a “SVILUPPO40.TR_TEST_EXC_01”, line 2
ORA-04088: errore durante esecuzione del trigger ‘SVILUPPO40.TR_TEST_EXC_01’
ORA-06512: a “SVILUPPO40.TEST_CATCH_EXCEPTION”, line 13
ORA-20001:
ORA-06512: a line 1

La funzione RAISE_APPLICATION_ERROR vuole come primo argomento un numero compreso tra -20000 e -20999; stampando nel trigger i SQLCODE e SQLERRM si vede:

SQLCODE: 0
SQLERRM: ORA-0000: normal, successful completion

Nello stesso commento però si suggerisce la possibilità di usare le funzioni DBMS_UTILITY.FORMAT_ERROR_STACK and/or DBMS_UTILITY.FORMAT_ERROR_BACKTRACE e la stessa Chen valuta la possibilità invece di rilanciare l’eccezione di salvare le informazioni in una tabella. Le due cose combinate secondo me danno la soluzione ottimale:

SVILUPPO40@perseo10 > drop table test_1
2  /

Tabella eliminata.

SVILUPPO40@perseo10 > create table test_1 (
2   text varchar2(100),
3   datetime timestamp with time zone
4  )
5  /

Tabella creata.

SVILUPPO40@perseo10 > drop table test_tr_2
2  /

Tabella eliminata.

SVILUPPO40@perseo10 > create table test_tr_2 (
2   backtr varchar2(200),
3   stack varchar2(200),
4   errm varchar2(512)
5  )
6  /

Tabella creata.

SVILUPPO40@perseo10 >
SVILUPPO40@perseo10 > CREATE OR REPLACE TRIGGER TR_test_exc_01 before INSERT
2  ON test_1
3  FOR EACH ROW
4  declare
5  a varchar2(2000);
6  BEGIN
7     a := sqlerrm;
8          INSERT INTO test_tr_2
9          VALUES
10          (
11          substr(DBMS_UTILITY.format_error_backtrace(),1,200),
12          substr(DBMS_UTILITY.format_error_stack(),1,200),
13          a);
14          dbms_output.put_line(a);
15  END;
16  /

Trigger creato.

SVILUPPO40@perseo10 >
SVILUPPO40@perseo10 > DECLARE
2   MY_EXCEPTION_IN_LOOP EXCEPTION;
3   PRAGMA EXCEPTION_INIT(MY_EXCEPTION_IN_LOOP, -20001);
4  BEGIN
5   FOR i IN 1..10 LOOP
6   IF i = 5 THEN
7     RAISE MY_EXCEPTION_IN_LOOP;
8   END IF;
9  END LOOP;
10  EXCEPTION
11  WHEN MY_EXCEPTION_IN_LOOP THEN
12   INSERT INTO TEST_1 VALUES (‘error’,systimestamp);
13  END;
14  /
ORA-0000: normal, successful completion

Procedura PL/SQL completata correttamente.

SVILUPPO40@perseo10 > select * from test_tr_2;

BACKTR
——————————————————————————
STACK
——————————————————————————
ERRM
——————————————————————————
ORA-06512: a line 7
ORA-20001:
ORA-0000: normal, successful completion

SVILUPPO40@perseo10 > select * from test_1;

TEXT
——————————————————————————
DATETIME
—————————————————————————
error
21-LUG-09 18:06:57,452000 +02:00

In aggiunta a quanto ho gia’ scritto nei miei vecchi post, pare sia utile l’utilizzo congiunto di format_error_backtrace e di format_error_stack, il primo permette di risalire alla riga di codice che ha generato l’eccezione, però non dice qual’è l’eccezione, il secondo da questa informazione.