Discussion :

[samworld]

Bonjour,

Au sein d'une fonction PL/SQL qui doit retourner un ensemble de résultats, je devais paramétrer une requête en fonction d'une variable.
Je me suis donc tourné vers l'utilisation de la commande "execute immediate" afin de produire le résultat de la requête.
Or cette requête doit retourner un ensemble de résultats, et j'obtiens toujours une erreur à la ligne de l'"execute", quelle que soit la syntaxe que j'utilise.

Voici la formation de ma requête et l'application de la commande sensée produire le résultat. Ce sont les dernières lignes de la fonction.

Code :

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

1
2
3
4
5
6
LC$Requete := 'select distinct blabla from blablabla' || sSQLPart || 'etc. where param=:1';
  LC$Requete := 'select cast(multiset(' ||
    LC$Requete ||
  ') as table_RESULT) into tResult from dual';
  dbms_output.put_line(LC$Requete);
  execute immediate LC$Requete using paramID BULK COLLECT into tResult;

Où :
La fonction retourne un objet du type table_RESULT.
paramID est un paramètre d'entrée de la fonction.
tResult est de type table_RESULT.
LC$Requete varchar2(512);
sSQLPart varchar2(18);

Comment retourner simplement l'ensemble résultat ?
Merci.

Sam;

[mnitu]

Bonjour,

Au sein d'une fonction PL/SQL qui doit retourner un ensemble de résultats, je devais paramétrer une requête en fonction d'une variable.
Je me suis donc tourné vers l'utilisation de la commande "execute immediate" afin de produire le résultat de la requête.
Or cette requête doit retourner un ensemble de résultats, et j'obtiens toujours une erreur à la ligne de l'"execute", quelle que soit la syntaxe que j'utilise.

Voici la formation de ma requête et l'application de la commande sensée produire le résultat. Ce sont les dernières lignes de la fonction.

Code :

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

1
2
3
4
5
6
LC$Requete := 'select distinct blabla from blablabla' || sSQLPart || 'etc. where param=:1';
  LC$Requete := 'select cast(multiset(' ||
    LC$Requete ||
  ') as table_RESULT) into tResult from dual';
  dbms_output.put_line(LC$Requete);
  execute immediate LC$Requete using paramID BULK COLLECT into tResult;

Où :
La fonction retourne un objet du type table_RESULT.
paramID est un paramètre d'entrée de la fonction.
tResult est de type table_RESULT.
LC$Requete varchar2(512);
sSQLPart varchar2(18);
...

Il y en a plusieurs erreurs dans le code que t'as posté. Mais pour l'instant je ne vois pas l'intérêt d’utiliser du Sql dynamique.

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
 
Declare
  l_emp_tab   Emp_tab;
  l_deptno    Number := 10;
Begin
  Select Cast(Multiset(
                        Select * 
                          From emp 
                         Where deptno = l_deptno
                       ) As emp_tab
             )
    Into l_emp_tab
    From dual;
  For i in 1..l_emp_tab.count() 
  Loop
    Dbms_Output.put_line('Empno = '||To_Char(l_emp_tab(i).empno)||' Ename = '||l_emp_tab(i).ename);
  End Loop;
End;
/
Empno = 7782 Ename = CLARK
Empno = 7839 Ename = KING
Empno = 7934 Ename = MILLER

Comment retourner simplement l'ensemble résultat ?

En utilisant une fontion/procédure qui renvoie un ref cursor.

[samworld]

Il y en a plusieurs erreurs dans le code que tu a posté.

Ah oui et où sont-elles ?

En utilisant une fontion/procédure qui renvoie un ref cursor.

Comment la fonction s'utilise ensuite ?

Et je reste sur l'utilisation du SQL dynamique, ton exemple ne montre qu'un affichage de requête.

Sam;

[mnitu]

Ah oui et où sont-elles ?

Voilà le même exemple utilisant SQL dynamique. En l'analysant tu trouvera tes erreurs.

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
 
Declare
  l_str       varchar2(512);
  l_emp_tab   Emp_tab;
  l_deptno    Number := 10;
Begin
  l_str := 'SELECT Cast(Multiset( '
         ||'               SELECT * ' 
         ||'              FROM emp  '
         ||'                WHERE deptno = :v1 '
         ||'            ) AS emp_tab '
         ||'  ) '
         ||' FROM dual ';
  Execute Immediate l_str Into l_emp_tab Using l_deptno;       
  FOR i IN 1..l_emp_tab.count() 
  Loop
    Dbms_Output.put_line('Empno = '||To_Char(l_emp_tab(i).empno)||' Ename = '||l_emp_tab(i).ename);
  End Loop;
End;

...
Comment la fonction s'utilise ensuite ?
...

Comme toute fonction qui renvoie un ref cursor.

Et je reste sur l'utilisation du SQL dynamique, ton exemple ne montre qu'un affichage de requête.

Sam;

Je pense que tu n'as pas encore compris mon exemple.

[samworld]

Je vais encore résoudre mon problème tout seul.

J'ai réécrit les dernières lignes :

Code :

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

1
2
3
 
execute immediate LC$Requete into tResult using paramID; -- j'avais inverti into et using, mais ce n'était pas la raison de mon blocage, car j'avais déjà testé dans le bon sens.
return tResult;  --j'avais omis ça

Dans la première ligne de mon exemple de code, le premier 'blabla' du select doit comporter autant de champs que de colonnes dans la structure table_RESULT ! J'en avais extrait un seul, alors que table_RESULT comporte deux champs.
Correction apportée : j'ai completé le premier 'select blabla' en extrayant une valeur nulle pour le second champ.

J'ajouterai que retourner une table est donc possible !

Bonne soirée;
Sam;

[mnitu]

Je vais encore résoudre mon problème tout seul.

J'ai réécrit les dernières lignes :

Code :

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

1
2
3
 
execute immediate LC$Requete into tResult using paramID; -- j'avais inverti into et using, mais ce n'était pas la raison de mon blocage, car j'avais déjà testé dans le bon sens.
return tResult;  --j'avais omis ça

Dans la première ligne de mon exemple de code, le premier 'blabla' du select doit comporter autant de champs que de colonnes dans la structure table_RESULT ! J'en avais extrait un seul, alors que table_RESULT comporte deux champs.
Correction apportée : j'ai completé le premier 'select blabla' en extrayant une valeur nulle pour le second champ.

J'ajouterai que retourner une table est donc possible !

Bonne soirée;
Sam;

Et pour quoi ne poste pas tu un exemple complet ?

[samworld]

Je poste un exemple complet à condition que tu retires ton titre de "membre expert".

Sam;