Discussion :

[shaun_the_sheep]

Bonjour,

je cherche à mettre à jour un enregistrement dans une fonction pl/sql or j'obtiens le message d'erreur suivant:

ORA-14551: cannot perform a DML operation inside a query

que faut il faire ?

y a t'il une autre solution ?

Merci

[orafrance]

sans le code... difficile de réponse

[laurentschneider]

Bonjour,

je cherche à mettre à jour un enregistrement dans une fonction pl/sql or j'obtiens le message d'erreur suivant:

ORA-14551: cannot perform a DML operation inside a query

que faut il faire ?

y a t'il une autre solution ?

Merci

effectivement, seuls les procédures supportent le dml (autre que select), pas les fonctions!

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
 
SQL> create function f return number is begin delete emp; return 0; end;        
  2  /                                                                          
Function created.
SQL> create procedure p is begin delete emp;  end;                              
  2  /                                                                          
Procedure created.
SQL> select f from dual;                                                        
select f from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.F", line 1
 
SQL> exec p                                                                     
PL/SQL procedure successfully completed.
SQL> roll                                                                       
Rollback complete.

[pifor]

Si la fonction PL/SQL démarre une transaction autonome, cela peut marcher. Mais la transaction autonome démarrée ne peut pas voir les modifications de la transaction "appelante" et la fonction doit terminer la transaction qu'elle a démarrée par COMMIT ou ROLLBACK.

[shaun_the_sheep]

Bonjour,

as tu un exemple de transaction autonome ?

Merci

[Xo]

Voici un élément de réponse :

Une procédure stockée peut contenir une transaction, voire plusieurs.
En aucun cas une fonction peut contenir une transaction puisque'elle est susceptible d'être utilisée au sein d'une requête ou d'une procédure.

Le reste : retour ou non n'est que détail car une procédure peut retourner une valeur ou une table, tout comme une fonction.
En outre une fonction peut lancer des ordres SQL et retourner une valeur bidon.

cf ici

[pifor]

Je crois que SQLpro parle de SQL Server et non d'Oracle: 2 SGBD qui respectent un certain niveau de SQL standard mais qui sont très différents à bien des points de vue

Cet exemple marche en Oracle 10 chez moi mais doit aussi marcher en Oracle 8 et 9 puisque je l'ai copié de la page 677 dans ce livre là: http://www.amazon.com/gp/product/159...g=UTF8&s=books

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
 
 
SQL>
SQL> drop table counter;
 
Table dropped.
 
SQL>
SQL> create table counter ( x int, y int default null);
 
Table created.
 
SQL>
SQL> insert into counter values (0, 0);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL>
SQL> create or replace function f return number
  2  as
  3  pragma autonomous_transaction;
  4  l_y int;
  5  begin
  6  update counter set y=1 where x = 0;
  7  select y into l_y from counter where x = 0;
  8  commit;
  9  return l_y;
 10  end;
 11  /
 
Function created.
 
SQL> show errors
No errors.
SQL>
SQL> select f from dual;
 
         F
----------
         1
 
SQL>
SQL> select * from counter;
 
         X          Y
---------- ----------
         0          1
 
SQL>

[orafrance]

J'aimerais bien connaitre le besoin parce que ça ne me semble pas très correcte comme manière de procéder

[pifor]

Un exemple donné par Tom Kyte est la mise en oeuvre d'un audit spécifique sur certaines colonnes de certaines tables pour savoir qui a fait un SELECT sur quoi. C'est un exemple qui date un peu car depuis je crois que le Fine Grain Auditing permet de faire la même chose sans avoir à tout coder soi-même.

[shaun_the_sheep]

mon besoin est simple, je cherche à faire une fonction qui calcul un identifiant selon une régle bien établi.

j'ai une procedure sql qui parcourt une table et fait des inserts dans une autre table.

A chaque insert je calcule un nouvel identifant via ma fonction. or j'ai besoin des calculs précedents pour calculer le nouveau , donc j'ai besoin de stocker mon calcul.

Merci

[orafrance]

c'est bien ce que je pensais, la méthode n'est pas correcte. Soit tu as besoin d'un valeur de retour et tu fais une procédure avec un paramétre OUTPUT soit tu passes par un trigger tout simplement

[shaun_the_sheep]

Bonjour,

la transaction autonome fonctionne c'est deja cela.

mon calcul ne prend pas forcement la valeur precedente pour le calcul suivant, car il est fonction d'un parametre c'est pour cela que je le stocke dans une table de correspondance.

ce qui me permet d'aller rechercher le dernier id correspondant à mon parametre.

Merci

[Xo]

Cet exemple marche en Oracle 10 chez moi mais doit aussi marcher en Oracle 8 et 9 puisque ...

Merci pour l'info, ce code marche avec Oracle 9, je confirme, grâce à l'instruction :

Code :

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

pragma autonomous_transaction;

Sinon, on retombe bien sur l'erreur ORA-14551.

Mais je pense néanmoins que SQLPro parlait bien des SGBD en général, le concept abordé étant commune à tous.