pb execution de fonction

Version imprimable

19/02/2007, 16h37
daliok

pb execution de fonction
salut ,
j'ai la fonction suivante
Code:

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 :

Citation:

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,
19/02/2007, 16h41
orafrance

si tu appelles la fonction dans un SELECT, la fonction ne peut pas modifier les données.
19/02/2007, 16h43
daliok

Oui j'appele la fonction dans un select ,
est ce que je peux pourquoi elle ne peut pas modifier les donner
19/02/2007, 16h48
plaineR

:recherch:
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 :

Citation:

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.
19/02/2007, 16h50
pifor

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

Merci pour vous tous;

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:

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:

select maFonction (param) from dual ;

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

20/02/2007, 09h24
orafrance

en effet, c'est une solution mais dans ce cas précis j'encourage à éviter la fonction pour utiliser du SQL simple :)
20/02/2007, 10h01
daliok

je vous remerci encore une fois ,rouardg votre intervention ma beaucoup idé
20/02/2007, 10h08
plaineR

Citation:

Envoyé par Fred_D

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.
20/02/2007, 10h22
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
20/02/2007, 10h40
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.
20/02/2007, 10h47
plaineR

Citation:

Envoyé par 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

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.

exemple :

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
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 ;)

20/02/2007, 11h31
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.
20/02/2007, 11h34
orafrance

certes, mais on ne le soulignera jamais assez ;)
22/02/2007, 10h43
Wurlitzer

Citation:

Envoyé par pifor

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 ! !