Gestione delle eccezioni in PL/SQL

martedì 21 luglio 2009 alle 21:11 | Pubblicato su PL/SQL | 6 commenti
Tag: , ,

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.

6 commenti »

RSS feed for comments on this post. TrackBack URI

  1. Ovviamente solo in 10g e NON in 9i🙂

    • hai ragione, nella fretta non ho riportato le indicazioni precise.

  2. Solo un dubbio: se nel corpo del trigger metto l’INSERT con l’errore in tabella, non devi poi rilanciare l’eccezione, altrimenti potresti perderla! Però ciò potrebbe provocare effetti indesiderati!
    Mi spiego: supponiamo di avere uno script che compie delle azioni sulla tabella su cui sta il trigger. Se nel trigger scatta un’eccezione, a volte è necessario informarne lo script in modo che prenda le sue “decisioni”, magari a volte va fatto un ROLLBACK, che rigetta anche l’INSERT dell’errore in tabella. In casi del genere ho un’eccezione in un trigger che viene “nascosta” al chimante e nessuna traccia nella tabella di LOG!!
    Lo script potrebbe terminare con successo e senza righe in tabella di LOG… ma sul DB non ci sono i dati che mi aspetto! Supponi che debba partire un altro script a valle di questo basandosi sul suo esito… si inceppa tutto e prima di capire quale anello della catena è il colpevole ce ne passa….

    • Io sui trigger cerco di seguire la filosofia di Tom Kyte, usarli il meno possibile. In questo caso lo uso, ma faccio solo un insert, quindi le probabilità di avere eccezioni sono decisamente basse e se c’è un’eccezione in questo caso viene propagata alla procedura principale. Quello che tu descrivi mi pare un po’ più complesso.

  3. E’ vero, nel 99% dei casi l’errore non si verifica. Però in generale, se il trigger opera una DML e nel corso della transazione c’è un errore, mi aspetto che probabilmente (discretamente alta probabilità) nel flusso principale ci sia una ROLLBACK, che sega via anche la INSERT nel trigger….
    Per abitudine preferisco sempre far risalire l’eccezione al chiamante principale che scrive i dettagli nel file di LOG…

    Scusami se avevo complicato un pò le cose, mi sono lasciato trasportare dai casi reali contro cui combatto ogni giorno…🙂

    Complimenti per questo ottimo blog!

    • Ricorda che normalmente, a meno che tu non chiami una procedura con una transazione autonoma, il trigger è transazionale, quindi normalmente succede quanto dici tu.
      Grazie per i complimenti!


Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

Crea un sito o un blog gratuitamente presso WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: