Discussion :

[donny]

Bonjour,

J'ai une procédure qui execute succèssivement plusieurs SQL dynamique.
J'ai mis un bloc exception à la fin de procédure pour gérer ses erreurs mais le soucis, c'est que je n'ai aucun moyen de savoir à quelle moment la procédure tombe en échec.
Si j'ai un message table ou vue inexistante,
je ne peux pas savoir si cela vient du truncate,update ou insert ?

Je ne vois pas comment faire pour gérer plusieurs erreur oracle.
J'ai essayé de rajouter un bloc exception après le truncate mais ça ne compile pas.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
BEGIN
 
FOR curs IN cursor1 LOOP
 
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || curs.A1 ||'';
 
EXECUTE IMMEDIATE 'INSERT INTO ' || curs.A1 ||' SELECT * FROM TABLE_ALIM';
COMMIT;
 
upds := 'UPDATE ' || curs.A1 ||' SET LINEX=''PL5'' WHERE USI = :1';
EXECUTE IMMEDIATE upds USING usine;
COMMIT; 
 
END LOOP; 
 
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
RAISE;
 
END;
/

Merci à tous

[skuatamad]

J'ai mis un bloc exception à la fin de procédure pour gérer ses erreurs mais le soucis, c'est que je n'ai aucun moyen de savoir à quelle moment la procédure tombe en échec.

C'est parce que le bloc exception que vous avez rajouté ne sert qu'à masquer la ligne en erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
SQL> create or replace procedure p (usine number) as
  2  upds varchar2(500);
  3  BEGIN
  4
  5  FOR curs IN (select 'X' as a1 from dual) loop
  6
  7  EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || curs.A1 ||'';
  8
  9  EXECUTE IMMEDIATE 'INSERT INTO ' || curs.A1 ||' SELECT * FROM TABLE_ALIM';
 10  COMMIT;
 11
 12  upds := 'UPDATE ' || curs.A1 ||' SET LINEX=''PL5'' WHERE USI = :1';
 13  EXECUTE IMMEDIATE upds USING usine;
 14  COMMIT;
 15
 16  END LOOP;
 17  end;
 18  /
 
Procedure created.
 
SQL> exec p(5);
BEGIN p(5); END;
 
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SKUATAMAD.P", line 7
ORA-06512: at line 1
 
 
SQL> create or replace procedure p (usine number) as
  2  upds varchar2(500);
  3  BEGIN
  4
  5  FOR curs IN (select 'X' as a1 from dual) loop
  6
  7  EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || curs.A1 ||'';
  8
  9  EXECUTE IMMEDIATE 'INSERT INTO ' || curs.A1 ||' SELECT * FROM TABLE_ALIM';
 10  COMMIT;
 11
 12  upds := 'UPDATE ' || curs.A1 ||' SET LINEX=''PL5'' WHERE USI = :1';
 13  EXECUTE IMMEDIATE upds USING usine;
 14  COMMIT;
 15
 16  END LOOP;
 17
 18  EXCEPTION
 19  WHEN OTHERS THEN
 20  DBMS_OUTPUT.PUT_LINE (SQLERRM);
 21  RAISE;
 22  end;
 23  /
 
Procedure created.
 
SQL> exec p(5);
BEGIN p(5); END;
 
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SKUATAMAD.P", line 21
ORA-06512: at line 1
 
 
SQL>

Sans le bloc EXCEPTION, on a l'information ligne 7 (le truncate), c'est utile.
Avec, c'est la ligne 21 qui correspond au raise, donc inutile.

Le bloc EXCEPTION sert à catcher des erreurs que l'on sait gérer, sinon il suffit de laisser oracle renvoyer l'erreur.

Sinon il est possible d'encapsuler les différentes actions dans des begin end et de catcher l'erreur ORA-00942 pour chaque action afin de personaliser le message d'erreur, exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SQL> create or replace procedure p (usine number) as
  2  upds varchar2(500);
  3  table_not_exists exception;
  4  pragma exception_init (table_not_exists, -942);
  5  BEGIN
  6
  7  FOR curs IN (select 'X' as a1 from dual) loop
  8    begin
  9      EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || curs.A1 ||'';
 10    exception
 11      when table_not_exists then
 12        raise_application_error (-20001,q'[Erreur dans l'opération TRUNCATE : La table ]' || curs.A1 || q'[ n'existe pas.]');
 13    end;
 14  EXECUTE IMMEDIATE 'INSERT INTO ' || curs.A1 ||' SELECT * FROM TABLE_ALIM';
 15  COMMIT;
 16
 17  upds := 'UPDATE ' || curs.A1 ||' SET LINEX=''PL5'' WHERE USI = :1';
 18  EXECUTE IMMEDIATE upds USING usine;
 19  COMMIT;
 20
 21  END LOOP;
 22
 23  EXCEPTION
 24  WHEN OTHERS THEN
 25  DBMS_OUTPUT.PUT_LINE (SQLERRM);
 26  RAISE;
 27  end;
 28  /
 
Procedure created.
 
SQL> exec p(5);
BEGIN p(5); END;
 
*
ERROR at line 1:
ORA-20001: Erreur dans l'opération TRUNCATE : La table X n'existe pas.
ORA-06512: at "SKUATAMAD.P", line 26
ORA-06512: at line 1
 
 
SQL>

[donny]

Merci beaucoup pour tes réponses.
Du coup si je comprends bien, l'exception est privilégié pour catcher certaines erreur du style la requête ne renvoit rien,le montant est < 5000 .....

et pour toutes les autres erreurs (ORA), on laisse oracle le gérer alors.

Supposons que je souhaite catcher toutes les erreurs oracle,
il aurait fallu mettre un when others de partout comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
CREATE OR REPLACE procedure p (usine number) AS
  upds varchar2(500);
  table_not_exists exception;
  pragma exception_init (table_not_exists, -942);
  BEGIN
 
  FOR curs IN (SELECT 'X' AS a1 FROM dual) loop
 
begin
      EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || curs.A1 ||'';
    exception
      when others then
        raise_application_error (-20001,q'[Erreur dans l'opération TRUNCATE : La TABLE ]' || curs.A1 || q'[ n'existe pas.]');
end;
 
begin
      EXECUTE IMMEDIATE 'INSERT INTO ' || curs.A1 ||' SELECT * FROM TABLE_ALIM';
    exception
      when others then
        raise_application_error (-20001,q'[Erreur dans l'opération INSERT : TABLE ]' || curs.A1 );
end;
   COMMIT;
 
begin
  upds := 'UPDATE ' || curs.A1 ||' SET LINEX=''PL5'' WHERE USI = :1';
  EXECUTE IMMEDIATE upds USING usine;
    exception
      when others then
        raise_application_error (-20001,q'[Erreur dans l'opération UPFATE : TABLE ]' || curs.A1 );
end;      
  COMMIT;
 
  END LOOP;
 
  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE (SQLERRM);
  RAISE;
  end;
  /

[skuatamad]

Ben dans ce cas quelle que soit l'erreur le message sera que la table n'existe pas, or il pourrait y avoir un problème d'extend par exemple.

Donc vous pouvez traiter l'erreur ORA-00942 parce que vous savez qu'elle peut arriver, et encore mieux vous savez peut être quoi faire quand elle arrive. Et raise les autres erreurs non prévus dans WHEN OTHERS :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
begin
      EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || curs.A1 ||'';
    exception
      when table_not_exists then
        raise_application_error (-20001,q'[Erreur dans l'opération TRUNCATE : La TABLE ]' || curs.A1 || q'[ n'existe pas.]');
      when others then
        raise;
end;

Sauf que WHEN OTHERS THEN RAISE, c'est le comportement par défaut avec le bon numéro de ligne, donc WHEN OTHERS THEN RAISE ne sert à rien! (et pire si on oublie RAISE, on ignore juste les erreurs)
Ce qui est possible c'est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
WHEN OTHERS THEN 
  log erreur;
  RAISE;

Mais les logs se font au niveau le plus élevé de l'appel, le "client" trace les erreurs et sait quoi faire des erreurs.

Par exemple si vous avez une application java ou autre (le client) qui appelle du PL/SQL, le PL/SQL n'aura pas à gérer WHEN OTHERS, commit, rollback, c'est en java que vous tracez, validez ou invalidez les transactions.
Comme le client peut être du PL/SQL, il est nécessaire de pouvoir gérer les erreurs pour les tracer, mais ça doit se faire au niveau le plus élevé pas pour chaque instruction ou procédure.

Cette discussion (et bien d'autres sur le site asktom) en parle :
http://asktom.oracle.com/pls/apex/f?...:1155066278457

[donny]

Merci tu réponds à ma question.