Discussion :

[dingo200]

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 :

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

1
2
3
4
5
 
texte :='	
rep_L := '||compte_livraison||'.livraison(
'||idCommand||','||refprod||','||nom||','||qty||')';
execute immediate texte;

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

[SheikYerbouti]

Voici aussi comment faire plus propre:

Code :

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

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

[dingo200]

merci, malheureusement, il s'agit d'une fonction et non d'une procédure,
je dois récuperer la valeur de retour

[SheikYerbouti]

http://sheikyerbouti.developpez.com/execute_immediate/

[dingo200]

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

Code :

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

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

[mnitu]

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

Code :

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

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.

[dingo200]

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

[mnitu]

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

Code :

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

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;

[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.

[mnitu]

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

Oui, le OUT suffit.

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

Peux tu me montrer stp ?

[SheikYerbouti]

Code :

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

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

[mnitu]

Code :

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

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 ?

[SheikYerbouti]

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

[mnitu]

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 :

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

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

[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.

[mnitu]

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.

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

Code :

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

1
2
3
4
5
6
 
declare
  l_date Date;
Begin
  l_date := sysdate;
End;

On peut écrire

Code :

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

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!