Discussion :

[hubsm]

Bonjour,

J'utilise une fonction pipelined dans le package ci-dessous.
Pour exécuter ma fonction et lire les données en retour ça fonctionne bien :
select * from table(my_package.my_function)

Maintenant j'aimerai à l'intérieur de cette fonction lire la table ainsi constituée et l'utiliser dans une requête :

Code :

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

1
2
3
4
    select count(*)
    into i
    from product
    where id_prod not in (select id_prod from table(my_table));

Si je fais ça j'ai l'erreur de compilation :

ORA-00904 (20: 58): PL/SQL: ORA-00904: "MY_TABLE": invalid identifier

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
35
36
37
38
39
create or replace package my_package as
 
  type my_row is record
  ( id_prod varchar2(20)
  , designation varchar2(50) );
 
  type my_table is table of my_row;
 
  function my_function
  return my_table
  pipelined;
 
end;
 
 
create or replace package body my_package as
 
  function my_function
  return my_table
  pipelined
  is
    r my_row;
    i number;
  begin
    r.id_prod := 'REFA';
    r.designation := 'DESI A';
    pipe row(r);
    r.id_prod := 'REFB';
    r.designation := 'DESI B';
    pipe row(r);
 
    select count(*)
    into i
    from product
    where id_prod not in (select id_prod from table(my_table));
 
  end;
 
end;

Comment faire un select sur la table my_table à l'intérieur de cette fonction pipelined ??

Si ça n'est pas possible je me demande si je devrais pas utiliser une table temporaire...

Cordialement.

[vanagreg]

Bonjour,

"my_table" n'est pas une instance d'objet mais un type, donc c'est normal que tu aies l'erreur.
Que cherches-tu à faire exactement?

[hubsm]

Bonjour,

"my_table" n'est pas une instance d'objet mais un type, donc c'est normal que tu aies l'erreur.
Que cherches-tu à faire exactement?

Salut vanagreg,

Dans ma fonction j'utilise les instructions PIPELINED pour empiler les données et les retourner, de sorte d'accéder aux données par un simple select :

Code :

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

select * from table(my_package.my_function)

A un moment dans ma fonction, j'ai besoin d'accéder à ces données et de les comparer dans une requête.
En gros je fais une requête select et j'ai besoin que les données que j'ai déjà enregistré via PIPE ROW soient excluent de ma requête :

Code :

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

1
2
3
4
select count(*)
into i
from product
where id_prod not in (select id_prod from table(???));

Donc par quoi remplacer ??? pour que ça fonctionne ? J'ai bien compris que my_table est un type et non une instance d'objet.
Mais en utilisant PIPE ROW il n'y a pas de variable associée aux données ? Ou un autre moyen d'accéder à ces données dans la fonction ?

Je pense que je vais avoir besoin soit d'une Associative array soit en utilisant une table temporaire.

Cordialement.

[vanagreg]

Bonjour,

Dans ce cas alimente un tableau de type "my_table" pour mémoriser ce qui a déjà été envoyé. Si tu es en 12.2 au moins, tu peux référencer maintenant les collections définies localement directement dans des requêtes SELECT au sein du package:

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
35
36
37
create or replace package body my_package as
 
  function my_function
  return my_table
  pipelined
  is
    r my_row;
    i number;   
    t my_table := my_table();

  begin
    r.id_prod := 'REFA';
    r.designation := 'DESI A';

    t.extend;
    t(t.last).id_prod := r.id_prod;
    t(t.last).designation := r.designation;

    pipe row(r);

    r.id_prod := 'REFB';
    r.designation := 'DESI B';

    t.extend;
    t(t.last).id_prod := r.id_prod;
    t(t.last).designation := r.designation;

    pipe row(r);

    select count(*)
    into i
    from product
    where id_prod not in (select id_prod from table(t)) ;

  end;

end;

[hubsm]

Bonjour,

Dans ce cas alimente un tableau de type "my_table" pour mémoriser ce qui a déjà été envoyé. Si tu es en 12.2 au moins, tu peux référencer maintenant les collections définies localement directement dans des requêtes SELECT au sein du package:

Merci pour ta réponse.

Malheureusement je suis en 11g, erreur à la compilation du package :
PLS-00642 (27: 57): PLS-00642: local collection types not allowed in SQL statements
ORA-22905 (27: 51): PL/SQL: ORA-22905: cannot access rows from a non-nested table item

[EDIT]

J'ai compris, il faut que je déclare my_row et my_table sur la base :

Code :

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

1
2
3
4
5
CREATE OR REPLACE type DEV.my_row as object
  ( id_prod varchar2(20)
  , designation varchar2(50) );
 
CREATE OR REPLACE type DEV.my_table is table of my_row;

Et là ça fonctionne je peux compiler mon package

Merci vanagreg

[vanagreg]

Ok, dans ce cas crée un type permanent en SQL (il en existe peut-être déjà dans ta base?). Puisque tu n'as besoin que de l'id_prod, je le crée en table of varchar2(20) (si tu as besoin des 2 infos, alors il te faudra d'abord créer un type OBJECT que tu utiliseras pour my_type):

Code :

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

create type my_type is table of varchar2(20);

Dans le package body utilise alors my_type:

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
35
create or replace package body my_package as
 
  function my_function
  return my_table
  pipelined
  is
    r my_row;
    i number;   
    t my_type := my_type();
 
  begin
    r.id_prod := 'REFA';
    r.designation := 'DESI A';
 
    t.extend;
    t(t.last):= r.id_prod;
 
    pipe row(r);
 
    r.id_prod := 'REFB';
    r.designation := 'DESI B';
 
    t.extend;
    t(t.last) := r.id_prod;
 
    pipe row(r);
 
    select count(*)
    into i
    from product
    where id_prod not in (select column_value from table(t)) ;
 
  end;
 
end;

[vanagreg]

J'ai compris, il faut que je déclare my_row et my_table sur la base :

Code :

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

1
2
3
4
5
CREATE OR REPLACE type DEV.my_row as object
  ( id_prod varchar2(20)
  , designation varchar2(50) );
 
CREATE OR REPLACE type DEV.my_table is table of my_row;

Et là ça fonctionne je peux compiler mon package

Merci vanagreg

Ok je vois que nos réponses se sont croisées. En effet tu avais déjà deviné ce qu'il fallait faire.