Discussion :

[PoPmiSiR]

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 :

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

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 :

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

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 :

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

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 :

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

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 :

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

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

[PoPmiSiR]

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

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

Code :

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

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 :

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

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 :

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

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

SELECT * from TABLE(TRC_GET_RESULT(1))

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

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

[rbaraer]

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 :

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

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 :

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

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