[Pl/Sql] Insert ou Update dans une fonction

Version imprimable

18/04/2006, 12h11
shaun_the_sheep

[Pl/Sql] Insert ou Update dans une fonction

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
18/04/2006, 12h28
orafrance

sans le code... difficile de réponse :roll:

Citation:

Envoyé par beegood

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:

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.

18/04/2006, 13h14
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.
18/04/2006, 13h54
shaun_the_sheep

Bonjour,

as tu un exemple de transaction autonome ?

Merci
18/04/2006, 13h59
Xo

Voici un élément de réponse :

Citation:

Envoyé par SQLPro

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

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:

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>

18/04/2006, 14h46
orafrance

J'aimerais bien connaitre le besoin parce que ça ne me semble pas très correcte comme manière de procéder :?
18/04/2006, 14h52
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.
18/04/2006, 14h55
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
18/04/2006, 14h58
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 ;)
18/04/2006, 15h08
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
18/04/2006, 15h28
Xo

Citation:

Envoyé par pifor

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:

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.