Renvoi d'un select par une fonction (via FETCH BULK_COLLECT)

Bonjour à tous,

Je m'excuse d'avance pour la potentielle "newbitude" de mon problème ;) Mon besoin est simple : je souhaiterais construire un select dynamique à partir d'une fonction pl. L'argument de cette fonction serait une chaine de caractères qui représenterait la clause where du select. J'aimerais arriver à qqchose comme ceci :

Code:

---

select * from table(my_pkg.get_employee_by_where('e_mail like ''%pop%'''));

---

Voici le bout de code que j'ai implémenté (je passe par un "bête" bulk collect):

Code:

---

1 2 3 4 5 6 7 8 9 10 11

function get_employee_by_where(aWhere varchar2) return n_employee is type myRefCur is ref cursor; myCur myRefCur; myNEmp n_employee; begin open myCur for 'select objid from table_employee where ' || aWhere; fetch myCur bulk collect into myNEmp; close myCur; return myNEmp; end get_employee_by_where;

---

table_employee.objid est de type number. Si je crée le type n_employee comme une table de number, tout se déroule correctement : la requête du dessus me renvoie des résultats, jusque là tout va bien !

Code:

---

create or replace type n_employee as table of number;

---

Par contre, dès que j'essaie de renvoyer plus d'éléments dans mon tableau avec la création d'un type spécifique (t_employee), le bulk collect me renvoie une erreur "ORA-00932 expected UDT got number".

Code:

---

1 2	create or replace type t_employee as object (objid number); create or replace type n_employee as table of t_employee;

---

Et là j'avoue que je sèche... Je n'arrive pas bien à voir la différence entre les deux implémentations.
Je vois qu'il y a plusieurs manières de construire un tableau mais cette méthode me semblait etre la plus facile.

Merci d'avance !
++
Pop

NB : Pour la version Oracle.

Code:

---

1 2	select * from v$version where rownum = 1; Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

---

Arf, il faut croire que je ne sais pas utiliser Google correctement :?
Je viens de trouver ca qui répond à mon besoin.

Citation:

---

-----------------
Step 1 : creation d un type qui correspond a ce qu il faut renvoyer
-----------------

Code:

---

1 2 3 4 5 6 7

CREATE TYPE TRC_MEASURE AS OBJECT ( PROGRAM VARCHAR2(255), ACTION VARCHAR2(255), ELLAPSED_TIME NUMBER(11) ) ;

---

-----------------
Step 2 : creation d un type qui correspond a une structure de type table
-----------------

Code:

---

1 2	CREATE TYPE TRC_MEASURE_RESULTS IS TABLE OF TRC_MEASURE ;

---

-----------------
Step 3 : creation de la fonction
-----------------
Points importants
Il faut rajouter PIPELINED a la declaration de la fonction
Il faut renvoyer les records a l aide de l instruction PIPE ROW
Il ne faut pas faire de return d une structure ou d une valeur
--

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

FUNCTION TRC_GET_RESULT (p_Camp IN NUMBER) RETURN TRC_MEASURE_RESULTS PIPELINED IS TYPE MyCursorType IS REF CURSOR; MyCursor MyCursorType; MyCounter NUMBER DEFAULT 1; COL1 Varchar2(255); COL2 Varchar2(255); COL3 Number(11); MySQLstmt VARCHAR(200); BEGIN   MySQLstmt := ; MySQLstmt := MySQLstmt || select prg,lib,deltassd*86400+deltassh*3600+deltassm*60+deltasss ellapsed_time from TRC_DATA ; MySQLstmt := MySQLstmt || where campid= ; MySQLstmt := MySQLstmt || p_Camp; MySQLstmt := MySQLstmt || and deltassd*86400+deltassh*3600+deltassm*60+deltasss is not null ; MySQLstmt := MySQLstmt || order by id ;   OPEN MyCursor FOR MySQLstmt; LOOP FETCH MyCursor INTO COL1,COL2,COL3 ; EXIT WHEN MyCursor%NOTFOUND; MyCounter := MyCounter+1; PIPE ROW(TRC_MEASURE (COL1,COL2,COL3) ); END LOOP; CLOSE MyCursor; -- RETURN instruction should not be used as it has been done through the pipe -- RETURN MyListOfRecords; RETURN; END ; / show errors; /

---

-----------------
Step 4 : Utilisation dans ton outil SQL prefere (TOAD !!!!)
-----------------
A l aide de l instruction TABLE on peut alors recuperer le resultat de la fonction

Code:

---

SELECT * from TABLE(TRC_GET_RESULT(1))

---

---

Même si je trouve ca moins beau, je dois parcourir ligne par ligne :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

function get_employee_by_where(aWhere varchar2) return n_employee pipelined is type myRefCur is ref cursor; myCur myRefCur; myObjid number; begin open myCur for 'select objid from table_employee where ' || aWhere; loop fetch myCur into myObjid; exit when myCur%notfound; pipe row(t_employee(myObjid)); end loop; close myCur; return; end get_employee_by_where;

---

Par contre, je serais curieux de savoir pourquoi le bulk collect ne fonctionne pas sur plusieurs colonnes ?
Merci en tout cas !

J'allais justement vous proposer une fonction pipelined.

Concernant votre 2e solution avec l'objet, il faut adapter votre SELECT pour qu'il retourne un objet t_employee et non pas un nombre, d'où l'erreur : Oracle attend un UDT (User-Defined Type), pas un NUMBER.

Code:

---

open myCur FOR 'select t_employee(objid) from table_employee where ' || aWhere;

---

Puis pour accéder aux champs de l'objet dans le SELECT d'au-dessus il me semble qu'on accède à une ligne du tableau par Value, puis ensuite aux attributs de l'objet (à vérifier) :

Code:

---

SELECT T.Value.objid FROM TABLE(my_pkg.get_employee_by_where('e_mail like ''%pop%''')) T;

---