Discussion :

[PoichOU]

Bonjour,

je voudrai crée une procédure stockée qui supprime des données dans plusieurs tables, seulement je voudrais passer l'autocommit à faux au début de la procèdure puis faire un commit à la fin histoire que les suppressions ne soient pas pris en compte en cas de problème.

Pouvez me dire comment je peux faire ça ?

Pour le moment ma procédure ressemble à ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
create PROCEDURE PR_DEL
(
  id      IN INTEGER
)
IS
BEGIN
  DELETE FROM T_USER WHERE ID_USER=id;
  DELETE FROM T_GROUPE WHERE ID_USER=id;
  DELETE FROM T_NEWSLETTER WHERE ID_USER=id;
END PR_DEL;

merci

PoichOU

[pifor]

Quelle est la version d'Oracle utilisée ?

Je ne connais pas d'option autocommit en PL/SQL mais il y a une option dans SQL*Plus et parfois une option qui dépend de l'API client (par exemple JDBC). Quelle est l'API en question ?

[remi4444]

En effet, contrairement à Sybase, SQL-Server ou MySql, Oracle place par défaut tout les traitements dans une seule transaction, donc tant que le client ne fait pas commit, il n'y en aura pas et les données resteront consistantes.

[PoichOU]

La procédure stockée dépendrait de la version d'oracle ?
présentement j'utilise Oracle9i Release 9.2.0.6.0, mais je voudrais faire une procédure qui ne dépend pas de la version.

Et effectivement j'utilise l'api JDBC. D'habitude je gère l'autocommit avec java mais je voudrais savoir s'il est possible de le gérer directement dans ma procèdure stockée ?

Merci

PoichOU

[pifor]

La procédure stockée dépendrait de la version d'oracle ?

2 versions majeures d'Oracle différentes ont toujours des fonctionnalités différentes, même pour le PL/SQL.

D'habitude je gère l'autocommit avec java mais je voudrais savoir s'il est possible de le gérer directement dans ma procèdure stockée ?

A ma connaissance ce n'est pas possible.

[remi4444]

De toutes façons ça tombe bien parceque dans une procédure stockée, il n'y a pas d'auto-commit et c'est ce que tu veux.

Si par exemple, une coupure réseau intervient pendant qu'un de tes delete tourne, un rollback général sera effectué sur toutes les tables.

[PoichOU]

merci de vos réponses

remi4444 : tu veux dire que si un problème arrive pendant l'execution de :

Code :

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

DELETE FROM T_NEWSLETTER WHERE ID_USER=id;

alors ma table T_USER et T_GROUPE ne seront pas vider ?


L'instruction PRAGMA AUTONOMOUS_TRANSACTION ne sert pas à ce genre de chose ?

[remi4444]

merci de vos réponses

remi4444 : tu veux dire que si un problème arrive pendant l'execution de :

Code :

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

DELETE FROM T_NEWSLETTER WHERE ID_USER=id;

alors ma table T_USER et T_GROUPE ne seront pas vider ?

OUI!

Je t'invite à faire toi meme le test car il ne faut jamais faire confiance à priori à des anonymes sur les forums

- Tu ouvres 2 SQLPLUS

- dans le premier tu locke ta table T_NEWSLETTER par

Code :

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

lock table T_NEWSLETTER  in exclusive mode

- dans le 2ieme sqlplus tu appelle ta procédure:

Code :

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

1
2
3
4
BEGIN
PR_DEL;
END;
/

ça ne va pas te rendre la main car il bloquera sur la dernière table

- tu killes de manière brutale ton 2ieme sqlplus.

- tu vérifies dans ton 1ier sqlplus que tes données sont toujours là....

Autre test plus simple, tu peux forcer une erreur à la fin de ta procédure et vérifier que c'est bien l'ensemble de la transaction qui est anulée:

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
 
SQL> create table TOTO_1 as select * from cat where rownum <= 10;
 
Table créée.
 
SQL> create table TOTO_2 as select * from cat where rownum <= 10;
 
Table créée.
 
SQL> commit;
 
Validation effectuée.
 
SQL>  create procedure P_DEL is
  2  i number;
  3  BEGIN
  4  DELETE FROM TOTO_1;
  5  DELETE FROM TOTO_2;
  6  i := 1 / (1 -1);
  7  END;
  8  /
 
Procédure créée.
 
SQL> select count(*) from toto_1;
 
  COUNT(*)
----------
        10
 
SQL> BEGIN
  2  P_DEL;
  3  END;
  4  /
BEGIN
*
ERREUR à la ligne 1 :
ORA-01476: divisor is equal to zero
ORA-06512: at "PPARTGEST.P_DEL", line 6
ORA-06512: at line 2
 
 
SQL> commit;
 
Validation effectuée.
 
SQL> select count(*) from toto_1;
 
  COUNT(*)
----------
        10
SQL>

L'instruction PRAGMA AUTONOMOUS_TRANSACTION ne sert pas à ce genre de chose ?

Cette instruction sert à rendre autonome ta procédure de celle qui l'a appelé, exactement comme si tu la lançait dans un autre sqlplus. L'interret est que par exemple tu peut faire un "commit" dans la procédure autonome meme si tu fais un rollback dans la procédure appelante. C'est par exemple très pratique lorsque tu veux placer des traces dans une table de suivi meme quand la procédure principale de vautre. Tu met l'insertion de trace dans la procédure autonome que tu commite, ainsi, meme lorsque la transaction générale rollbacke, la trace reste....
A noter qu'oracle t'oblige dans une procédure autonome à te décider entre un rollback et un commit, si par exemple la procédure se termine sans l'un ni l'autre, alors l'erreur ORA-06519 est renvoyée. En d'autres termes, dans le cas d'une procédure autonome, il n'y a ni d'auto-commit, ni d'auto-rollback