Prévoir un Rollback dans une procédure

[SALMHSN]

Bonjour mes amis ,

je vous sollicite votre aide SVP

en fait j'ai une procédure plsql

Code :

1
2
3
4
5
6
7
8
CREATE OR REPLACE PROCEDURE sp_Insert(idt varchar2)
IS
BEGIN
         INSERT INTO tab1  
         SELECT * FROM tab  WHERE code =idt ;
         COMMIT ;
 
END;

cette procédure comme vous le remarquez permet d'inserer dans une table
je veux si jamais une exception s'est montée pouvoir effectuer un rollback et annuler l'insertion comment dois je faire cela ??

[AbouZaid]

Code :

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE PROCEDURE sp_Insert(idt varchar2)
IS
BEGIN
         INSERT INTO tab1  
         SELECT * FROM tab  WHERE code =idt ;
         COMMIT ;
         EXCEPTION WHEN OTHERS THEN
                  ROLLBACK;
END;

[mnitu]

D'une manière générale ne fait pas de commit dans votre procédure.
Dans votre procédure si une erreur arrive rien n'est inséré dans la table donc faire rollback ne sert strictement à rien.

[SALMHSN]

mais si je commite pas donc je vais pas trouver mes données insérées dans la table non ?!!!!

[mnitu]

Faite le commit à l’extérieur de votre procédure.

[Pomalaix]

Envoyé par mnitu

D'une manière générale ne fait pas de commit dans votre procédure.

Je suis assez d'accord avec ça.
Si une procédure provoque une fin de transaction, il faut vraiment le documenter de manière explicite.

Envoyé par mnitu

Dans votre procédure si une erreur arrive rien n'est inséré dans la table donc faire rollback ne sert strictement à rien.

Aïe aïe aïe, je n'ose pas imaginer toutes les mauvaises interprétations qui risquent d'être faites ! Du genre "en PL/SQL, dès qu'il y a une erreur, ça fait un ROLLBACK".

[mnitu]

Envoyé par Pomalaix

...
Aïe aïe aïe, je n'ose pas imaginer toutes les mauvaises interprétations qui risquent d'être faites ! Du genre "en PL/SQL, dès qu'il y a une erreur, ça fait un ROLLBACK".

Bref, si tu pense qu'il y a un risque ajoutons des explications "How Oracle Does Implicit Rollbacks"

[skuatamad]

Mnitu je suis tout à fait d'accord avec tes premières réponses mais je trouve la doc derrière ton lien obscure...
Mais c'est peut être juste un problème de compréhension de l'anglais...

Before executing an INSERT, UPDATE, or DELETE statement, Oracle marks an implicit savepoint (unavailable to you). If the statement fails, Oracle rolls back to the savepoint. Usually, just the failed SQL statement is rolled back, not the whole transaction

Code :

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
SQL> DROP TABLE t1
  2  /
 
TABLE dropped.
 
SQL> CREATE TABLE t1 (c varchar2(1))
  2  /
 
TABLE created.
 
SQL> 
SQL> begin
  2    INSERT INTO t1 (c) VALUES ('a');
  3    INSERT INTO t1 (c) VALUES ('aa');
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-12899: value too large FOR COLUMN "SKUATAMAD"."T1"."C" (actual: 2, maximum:
1)
ORA-06512: at line 3
 
 
SQL> SELECT * FROM t1
  2  /
 
no rows selected
 
SQL>

Pour moi la transaction a été rollbackée.

If the statement raises an unhandled exception, the host environment determines what is rolled back.

Je ne sais pas bien comment inerpréter cette phrase, mais avec sqlplus la transation est rollbackée, idem avec SqlDeveloper et je pense bien que c'est la même chose avec Toad.
Donc il me semble douteux que certains "host environment" se comporte différemment des environements de développement...
A moins qu'il ne fasse simplement référence à :

Code :

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
SQL> INSERT INTO t1 (c) VALUES ('a')
  2  /
 
1 row created.
 
SQL> INSERT INTO t1 (c) VALUES ('aa')
  2  /
INSERT INTO t1 (c) VALUES ('aa')
                           *
ERROR at line 1:
ORA-12899: value too large FOR COLUMN "SKUATAMAD"."T1"."C" (actual: 2, maximum:
1)
 
 
SQL> SELECT * FROM t1
  2  /
 
C
-
a
 
SQL> rollback
  2  /
 
Rollback complete.
 
SQL>

Mais à mon sens (peut être je me trompe) ces 2 inserts représentent 2 transactions.

Idem cette phrase me semble étrange :

If you exit a stored subprogram with an unhandled exception, PL/SQL does not assign values to OUT parameters, and does not do any rollback.

Code :

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
SQL> CREATE OR REPLACE procedure p1 (p_val varchar2) AS
  2    begin
  3      INSERT INTO t1 (c) VALUES (p_val);
  4    end;
  5  /
 
Procedure created.
 
SQL> 
SQL> begin
  2    p1 ('a');
  3    p1 ('aa');
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-12899: value too large FOR COLUMN "SKUATAMAD"."T1"."C" (actual: 2, maximum:
1)
ORA-06512: at "SKUATAMAD.P1", line 3
ORA-06512: at line 3
 
 
SQL> SELECT * FROM t1
  2  /
 
no rows selected
 
SQL>

Si quelqu'un veut bien m'aider à comprendre ce que souhaite mettre en évidence la doc...

[Pomalaix]

Envoyé par skuatamad

...

Je suis complètement d'accord : les passages que vous avez relevés en gras sont pour moi aussi incompréhensibles, tellement ils sont contraires à ce qu'on constate dans la pratique !

(Par contre, dans votre test en SQL pur, il y a forcément une seule transaction puisqu'aucune instruction ne sépare vos 2 INSERT).

[skuatamad]

Effectivement il n'y a qu'une seule transaction, merci Pomalaix pour la précision.

Et je suis rassuré de ne pas être le seul à trouver ce passage à minima très mal expliqué.

[mnitu]

Mais, non. Lisez aussi Statement-Level Rollback. Analysez ce code.

Code :

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
 
Connected TO Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 
Connected AS mni
 
SQL> 
SQL> DROP TABLE t1
  2  /
 
TABLE dropped
SQL> CREATE TABLE t1 (c varchar2(1))
  2  /
 
TABLE created
SQL> begin
  2    INSERT INTO t1 (c) VALUES ('a');
  3    INSERT INTO t1 (c) VALUES ('aa');
  4  end;
  5  /
 
begin
  INSERT INTO t1 (c) VALUES ('a');
  INSERT INTO t1 (c) VALUES ('aa');
end;
 
ORA-12899: valeur trop grande pour la colonne "MNI"."T1"."C" (réelle : 2, maximum : 1)
ORA-06512: à ligne 4
SQL> SELECT Count(*)
  2    FROM t1
  3  /
 
  COUNT(*)
----------
         0
SQL> begin
  2    INSERT INTO t1 (c) VALUES ('a');
  3    INSERT INTO t1 (c) VALUES ('aa');
  4  exception
  5    When Others Then
  6      dbms_output.put_line('Err');
  7  end;
  8  /
 
PL/SQL procedure successfully completed
SQL> SELECT Count(*)
  2    FROM t1
  3  /
 
  COUNT(*)
----------
         1
 
SQL>

[mnitu]

Et encore un test

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
Connected TO Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 
Connected AS mni
 
SQL> SET serveroutput ON
SQL> 
SQL> begin
  2    execute immediate 'begin  insert into t1 values (''a'');  insert into t1 values (''aa''); end;';
  3  exception
  4    when others then
  5      dbms_output.put_line('Err');
  6  end;
  7  /
 
Err
 
PL/SQL procedure successfully completed

Combien des enregistrements sont dans la table t1 ?

[Yanika_bzh]

1?

[mnitu]

Envoyé par Yanika_bzh

1?

Code :

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
 
Connected TO Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 
Connected AS mni
 
SQL> SET serveroutput ON
SQL> SELECT count(*) FROM t1;
 
  COUNT(*)
----------
         0
 
SQL> 
SQL> begin
  2    execute immediate 'begin  insert into t1 values (''a'');  insert into t1 values (''aa''); end;';
  3  exception
  4    when others then
  5      dbms_output.put_line('Err');
  6  end;
  7  /
 
Err
 
PL/SQL procedure successfully completed
 
SQL> SELECT count(*) FROM t1;
 
  COUNT(*)
----------
         0
 
SQL>

[skuatamad]

Envoyé par mnitu

Mais, non. Lisez aussi Statement-Level Rollback. Analysez ce code.
...

Hum tu penses que c'est à ça qu'il pensait...
Je trouve quand même assez bizarre de vouloir préciser un comportement avec gestion d'erreur non raise en précisant :

If the statement raises an unhandled exception

D'ailleurs j'ai trouvé ailleurs dans Transaction Control

If your program fails in the middle of a transaction, Oracle detects the error and rolls back the transaction, restoring the database to its former state.

Ce qui me semble en contradiction avec le précédent lien et donc également assez mal formulé, ou au moins un peu léger.

Bon comme souvent tom kyte est bien plus didactique dans sa réponse :
http://asktom.oracle.com/pls/asktom/...43818437682131

By default all statements are ATOMIC, the procedure you call either entirely succeeds or entirely fails - UNLESS you

1) put a commit/rollback in there - then game over, the caller of your procedure must deal with the MESS you've left them in - which is an unknown database state (maybe they called 50 other routines before your code - it is UP TO THEM whether they should a) commit, b) rollback, c) retry some operation)

2) put a when others not followed by a raise - then the work that is partially done "stays" and the work never gotten to - is never gotten to. Again, an unknown database state.

[mnitu]

J'ai fini par trouver sur internet Procedure-Level Atomicity dans Expert Oracle Database par Tom Kyte.

[SALMHSN]

tout d'abord je vous remercie tous pour votre collaboration ,

en fait je vais essayer de vous expliquer un peu mon probléme ,
j'ai des procedures comme je vous ai déjà montré dans ma premiére intervention mais je veux en fait si jamais une erreur est survenue dans la procédure pouvoir afficher un message d'erreur et annuler la transaction courante !

comment je peux faire çà au niveau de ma procédure plsql

[Waldar]

Vous n'avez rien à faire, c'est déjà ce que fait Oracle.

[SALMHSN]

effectivement et tout à fait d'accord mais moi je veux pouvoir afficher l'erreur lors de l'appel de mes procédures au niveau de mon application web

[mnitu]

Exécutez la procédure et s'il y a une erreur affichez-la.