Appel d'une fonction dans une procédure

Version imprimable

04/11/2008, 15h20
dingo200

Appel d'une fonction dans une procédure
bonjour à tous,
voilà j'ai un problème bête, j'essaye d'appeler une fonction PL/SQL dans une procédure (PL/SQL aussi)
mais à l'execution ça ne passe pas

ORA-00900: invalid SQL statement
Code:

1 2 3 4 5 texte :=' rep_L := '||compte_livraison||'.livraison( '||idCommand||','||refprod||','||nom||','||qty||')'; execute immediate texte;
04/11/2008, 15h29
SheikYerbouti

Lorsque vous jouez avec le SQL dynamique, affichez le contenu de la chaîne a exécuter à l'écran ou dans une table afin de le jouer ensuite sous Sql*plus, Toad ou Sql Developer. vous verrez tout de suite les erreurs de syntaxe (souvent un espace manquant)

Voici aussi comment faire plus propre:

Code:

1
2
plsql_block := 'BEGIN ma_procedure(:a, :b, :c); END;';
EXECUTE IMMEDIATE plsql_block USING 'a', 'b', 'c';

04/11/2008, 15h39
dingo200

merci, malheureusement, il s'agit d'une fonction et non d'une procédure,
je dois récuperer la valeur de retour
04/11/2008, 15h51
SheikYerbouti

http://sheikyerbouti.developpez.com/execute_immediate/
06/11/2008, 02h14
dingo200
finalement, il fallait faire un select dans la requete,
puis un into <variable> dans l'exécution :
Code:

1 2 3 texte := 'select '||compte_livraison||'.livraison('''||idCommand||''','''||refprod||''','''||nom||''','||qty||') from dual'; execute immediate texte into rep_L;
ne pas oublier un petit :
pragma autonomous_transaction;
dans la fonction appelée (ici livraison) pour éviter les conflits et le tour est joué

enfin en aura fallut du temps pour débloquer ça :s
06/11/2008, 09h09
mnitu
Citation:
Envoyé par dingo200

finalement, il fallait faire un select dans la requete,
puis un into <variable> dans l'exécution :

Code:

1 2 3 texte := 'select '||compte_livraison||'.livraison('''||idCommand||''','''||refprod||''','''||nom||''','||qty||') from dual'; execute immediate texte into rep_L;

ne pas oublier un petit :
pragma autonomous_transaction;
dans la fonction appelée (ici livraison) pour éviter les conflits et le tour est joué

enfin en aura fallut du temps pour débloquer ça :s
Non c'est solution est mauvaise, null besoin de faire du SELECT ... INTO ... FROM dual! Il n'y rien qui empêche d'ecrire le code correctement.
06/11/2008, 22h04
dingo200

je ne comprend pas...
Qu'est-ce qu'il faudrait faire alors ?

Citation:

Envoyé par dingo200

je ne comprend pas...
Qu'est-ce qu'il faudrait faire alors ?

Code:

1
2
3
4
5
6
7
8
9
 
Declare
  l_num Number;
  plsql_block Varchar2(1000); 
Begin
  plsql_block := 'BEGIN :1 := Ma_Fonction(:2); End;';
  EXECUTE IMMEDIATE plsql_block Using IN OUT l_num, In 'TOTO';
  Dbms_Output.put_line(To_Char(l_num));
End;

07/11/2008, 11h26
SheikYerbouti

le IN OUT n'est pas nécessaire. Vous pouvez retourner le résultat de la fonction avec la syntaxe : execute immediate ... into variable.
07/11/2008, 11h51
mnitu

Citation:

Envoyé par SheikYerbouti

le IN OUT n'est pas nécessaire...

Oui, le OUT suffit.

Citation:

Envoyé par SheikYerbouti

lVous pouvez retourner le résultat de la fonction avec la syntaxe : execute immediate ... into variable.

Peux tu me montrer stp ?

Code:

1
2
3
4
5
6
DECLARE
  n NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'select test() from dual' INTO n ;
  DBMS_OUTPUT.PUT_LINE(n);
END;

07/11/2008, 12h10
mnitu
Citation:
Envoyé par SheikYerbouti

Code:

1 2 3 4 5 6 DECLARE n NUMBER; BEGIN EXECUTE IMMEDIATE 'select test() from dual' INTO n ; DBMS_OUTPUT.PUT_LINE(n); END;
Et tu pense vraiment qu'utiliser Select ... Fonction() From Dual c'est la solution d'appel dynamique d'une fonction ? 8O
07/11/2008, 13h12
SheikYerbouti

tu as l'exemple sous les yeux. Qui a t-il à redire ?

Citation:

Envoyé par SheikYerbouti

tu as l'exemple sous les yeux. Qui a t-il à redire ?

Bref j'ai testé les deux solutions sur une base Oracle 9

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
 
CREATE OR REPLACE Function ma_fonction(p_in number) Return Number
Is
Begin
  Return p_in;
End;
/
Declare
  l_num Number;
  plsql_block Varchar2(1000); 
Begin
  plsql_block := 'BEGIN :1 := Ma_Fonction(:2); End;';
  runStats_pkg.rs_start;
  For i In 1..10000 Loop
    EXECUTE IMMEDIATE plsql_block USING IN OUT l_num, IN i;
  End Loop; 
  --
  runStats_pkg.rs_middle; 
  --
  For i In 1..10000 Loop
    EXECUTE IMMEDIATE 'select Ma_Fonction(:1) from dual' INTO l_num Using i;
  End Loop;
  --
  runStats_pkg.rs_stop(200);
End;
/

et voila le résultat.

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
 
Run1 ran in 238 hsecs
Run2 ran in 389 hsecs
run 1 ran in 61,18% of the time
 
Name                                  Run1        Run2        Diff
LATCH.shared pool                   80,190      80,409         219
STAT...redo size                    60,728      62,180       1,452
STAT...session pga memory                0       8,512       8,512
STAT...session pga memory max            0       8,512       8,512
STAT...no work - consistent re          10       9,982       9,972
STAT...table scans (short tabl           0      10,000      10,000
STAT...table scan blocks gotte           0      10,000      10,000
STAT...buffer is not pinned co          20      10,020      10,000
STAT...table scan rows gotten            0      10,000      10,000
LATCH.library cache pin             80,101     100,341      20,240
STAT...calls to get snapshot s           6      30,006      30,000
STAT...session logical reads           526      30,579      30,053
STAT...consistent gets                  12      30,065      30,053
LATCH.library cache                 90,273     130,662      40,389
LATCH.cache buffers chains           2,557      62,999      60,442
 
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
     254,152     376,422     122,270     67.52%

S’il est vrai qu’on peut "utiliser execute immediate ... into variable" il semblerait que ce n'est pas la meilleure solution ni en termes de performance ni en termes des ressources consumés.

07/11/2008, 14h27
SheikYerbouti

En tout cas, cela permet d'utiliser les fonctions stockées "telles quelles", sans devoir les transformer en pseudo-procédures affublées de paramètre OUT.
07/11/2008, 16h03
mnitu
Citation:

Envoyé par SheikYerbouti

En tout cas, cela permet d'utiliser les fonctions stockées "telles quelles", sans devoir les transformer en pseudo-procédures affublées de paramètre OUT.

:roll:
Les "fonctions stockées" sont écrites en PL/SQL! Il n'y a aucune "transformation en pseudo-procédures". On écrit
Code:

1 2 3 4 5 6 declare l_date Date; Begin l_date := sysdate; End;
On peut écrire
Code:

1 2 3 4 5 6 7 8 declare l_date Date; Begin Select sysdate Into l_date From dual; End;
Les deux solutions sont correctes! Le problème avec la deuxième solution est tout simplement qu'elle consomme plus des ressources et qu'elle prend plus de temps.
Franchement je ne vous trouve pas raisonnable sur ce point!