Discussion :

[daliok]

salut ,
j'ai la fonction suivante

Code :

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

1
2
3
4
5
6
7
create or replace function aa(a number)  return number is
  Result number;
begin 
 update test set x = a ;
  result := sql%rowcount;
  return(Result);
end aa;

et en executant cette fonction il me donne le message suivant :

ORA-14551: impossible d'effectuer une opération DML dans une interrogation
ORA-06512: à "AMEN.AA", ligne 5
ORA-06512: à ligne 1

est ce que je peux savoir la cause,
Merci,

[orafrance]

si tu appelles la fonction dans un SELECT, la fonction ne peut pas modifier les données.

[daliok]

Oui j'appele la fonction dans un select ,
est ce que je peux pourquoi elle ne peut pas modifier les donner

[plaineR]

http://www.developpez.net/forums/sho...ight=DML+query
http://www.developpez.net/forums/sho...ight=DML+query
http://www.developpez.net/forums/sho...ight=DML+query
...

http://ora-14551.ora-code.com/?txt_erreur=14551 :

ORA-14551: cannot perform a DML operation inside a query
Cause: DML operation like insert, update, delete or select-for-update cannot be performed inside a query or under a PDML slave.
Action: Ensure that the offending DML operation is not performed or use an autonomous transaction to perform the DML operation within the query or PDML slave.

[pifor]

Voir aussi le Oracle® Database PL/SQL User's Guide and Reference:
http://download-uk.oracle.com/docs/c...ams.htm#i22204

[daliok]

Merci pour vous tous;

[rouardg]

Bonjour Daliok,

Ce que tu veux faire est possible, mais je ne l'encourage pas. En fait, il faut utiliser une transaction autonome dans ta fonction :

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE FUNCTION aa(a number)  RETURN number IS
  pragma autonomous_transaction ;
  Result number;
begin 
 UPDATE test SET x = a ;
 result := sql%rowcount;
 commit ;
 RETURN(Result);
end aa;
/

D'ailleurs, je te remercie d'avoir posé la question, car grâce à toi, j'ai compris pourquoi sur un des projets dont j'ai hérité, les développeurs avaient collé de la transaction autonome dans toutes leurs fonctions PL/SQL.

C'est en fait parce que leurs fonctions sont appelées depuis un Shell sous SQL*Plus, par une commande du type :

Code :

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

select maFonction (param) from dual ;

Comme quoi, même avec des années d'expérience, on en apprend tous les jours !!!

[orafrance]

en effet, c'est une solution mais dans ce cas précis j'encourage à éviter la fonction pour utiliser du SQL simple

[daliok]

je vous remerci encore une fois ,rouardg votre intervention ma beaucoup idé

[plaineR]

en effet, c'est une solution mais dans ce cas précis j'encourage à éviter la fonction pour utiliser du SQL simple

+1
De plus il faut penser à commiter dans la fonction. Et attention au risque d'incohérence de données en cas de rollback à un niveau supérieur. Pour moi l'utilisation des transactions autonome doit rester exceptionnelle et être mûrement réfléchie.

[pifor]

Est-ce qu'il peut y avoir un risque d'incohérence des données avec une transaction autonome ? Je ne crois pas, car la transaction autonome ne peut pas voir les données modifiées par la transaction principale, Oracle ne permet pas les lectures "sales". Que la transaction principale fasse COMMIT ou ROLLBACK ne peut avoir d'influence sur la transaction autonome:
http://download-uk.oracle.com/docs/c....htm#sthref666

[McM]

Ce que voulait dire PlaineR c'est que dans ta procédure principale, tu fais appel à la fonction, puis tu fais un ROLLBACK suite à une erreur.
La fonction a quand même commité ses modifications.

[plaineR]

Est-ce qu'il peut y avoir un risque d'incohérence des données avec une transaction autonome ? Je ne crois pas, car la transaction autonome ne peut pas voir les données modifiées par la transaction principale, Oracle ne permet pas les lectures "sales". Que la transaction principale fasse COMMIT ou ROLLBACK ne peut avoir d'influence sur la transaction autonome:
http://download-uk.oracle.com/docs/c....htm#sthref666

J'ai eu le cas d'incohérence de données à cause de transaction autonome et on a mis du temps à trouver d'où cela venait. On avait une fonction qui renvoyait un numéro unique et sans trou. Pour pouvoir l'appeler à partir d'un select un développeur l'avait passé en autonomous. On faisait un rollback dans le traitement appelant et évidemment le rollback n'était pas fait dans la table de numérotation unique. Nous avons également eu le cas d'une procédure autonome faisant un delete sur le maître (passé en paramètre) alors que l'on rollbackait plus loin le delete du détail.

[orafrance]

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
SQL> create table fred1 (col number);
 
Table créée.
 
SQL> create table fred2 (col number);
 
Table créée.
 
SQL> create function fred_f RETURN number is
  2  pragma autonomous_transaction ;
  3    Result number;
  4  begin 
  5  insert into fred2 values (2);
  6  commit;
  7  return 0;
  8  end;
  9  /
 
Fonction créée.
 
SQL> select fred_f from dual;
 
    FRED_F
----------
         0
 
SQL> select * from fred2;
 
       COL
----------
         2
 
SQL> insert into fred1 values (1);
 
1 ligne créée.
 
SQL>  select fred_f from dual;
 
    FRED_F
----------
         0
 
SQL> 
SQL> rollback;
 
Annulation (rollback) effectuée.
 
SQL> select * from fred1;
 
aucune ligne sélectionnée
 
SQL> select * from fred2;
 
       COL
----------
         2
         2

le COMMIT de la procedure autonome n'est pas propagé ce qui peut poser problème

[pifor]

Le COMMIT ne doit pas être propagé: c'est normal. Une transaction autonome n'est qu'une transaction indépendante, qui le temps de l'exécution suspend l'exécution de la transaction principale, et redonne la main à la transaction principale: le COMMIT de la transaction autonome/indépendante ne concerne que la transaction indépendante et non la transaction principale.

On peut considérer une transaction autonome comme une transaction concurrente de la transaction principale à la différence que les 2 s'exécutent dans la même session.

[orafrance]

certes, mais on ne le soulignera jamais assez

[Wurlitzer]

On peut considérer une transaction autonome comme une transaction concurrente de la transaction principale à la différence que les 2 s'exécutent dans la même session.

Pour rebondir la dessus et sur les bugs bizarres que l'on peut avoir avec les transactions autonomes.

J'ai eu le cas suivant ou une session etait en Deadlock avec .... elle meme

Apres un peu de recherche j'ai vu que mon package faisait un update sur une table puis une peu plus lancait une autre procédure du package en transation autonome qui faisait un autre update sur la meme table. Et bingo, Deadlock ! !