Discussion :

[superfly]

Bonjour,

Voila la question du jour :

Je souhaite créer une fonction qui renvoie une collection, de maniere à pouvoir écrire une requete du style (Select * from ma_fonction)

Alors voila ce que j'ai écris :

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
 
create or replace package PA_COURRIER2 is
 
 
  -- Public type declarations
  type T_REC_ADR_CORRESP_CLIENT is RECORD ( civ  t_clients.cod_civ%type,
                                                      nom  t_clients.lib_nom%type,
                                                      prenom t_clients.lib_prenomcli%type
                                                    );
 
  type tab_T_REC_ADR_CORRESP_CLIENT is table of T_REC_ADR_CORRESP_CLIENT index by binary_integer;
 
  -- Public constant declarations
  --<ConstantName> constant <Datatype> := <Value>;
 
  -- Public variable declarations
  --<VariableName> <Datatype>;
 
  -- Public function and procedure declarations
  function FO_ADR_CORRESPONDANT_CLIENT(pnumcli number) return tab_T_REC_ADR_CORRESP_CLIENT;
 
end PA_COURRIER2;

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
40
41
42
43
44
45
46
 
create or replace package body PA_COURRIER2 is
 
  -- Private type declarations
 -- type <TypeName> is <Datatype>;
 
  -- Private constant declarations
  --<ConstantName> constant <Datatype> := <Value>;
 
  -- Private variable declarations
--  <VariableName> <Datatype>;
 
  -- Function and procedure implementations
  Function FO_ADR_CORRESPONDANT_CLIENT(pnumcli number) 
  return tab_T_REC_ADR_CORRESP_CLIENT 
  is
  t_rec  tab_T_REC_ADR_CORRESP_CLIENT;
  i number := 1;
 
  cursor cur_courrier is select cli.cod_civ civ, cli.lib_prenomcli prenom, cli.lib_nom nom
                          from op.t_clients cli
                          , op.tr_type_voie typvoi
                          , op.tr_pays pay
                          , op.tr_civilite civ
                          where cli.num_cli = 32686
                          and cli.cod_typvoi = typvoi.cod_typvoi (+)
                          and cli.cod_pay = pay.cod_pay (+)
                          and civ.cod_civ (+) = cli.cod_civ ;
 
  begin
 
 
       for rec in cur_courrier loop
           t_rec(i).civ := rec.civ;
           t_rec(i).nom := rec.nom;
           t_rec(i).prenom := rec.prenom;
           i:=i+1;
       end loop;
 
    return(t_rec);
  end FO_ADR_CORRESPONDANT_CLIENT;
 
--begin
  -- Initialization
--  <Statement>;
end PA_COURRIER2;

Et voici l'appel

Code :

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

1
2
 
select * from table (cast (op.FO_ADR_CORRESPONDANT_CLIENT(32685) as tab_T_REC_ADR_CORRESP_CLIENT) )

Biensur sur il merde lorsque je fais l'appel, et me sort l'erreur :

ORA-22905 cannot access rows from a non-nested table item

Et je dois avouer que je me suis inspiré de plusieurs exemples que j'ai trouvé pour écrire mon code et que j'ai l'impression d'etre passé à coté de quelques choses d'essentielles.

Alors si quelqu'un avait une idée je suis preneur.

Merci

[orafrance]

faut vraiment être tordu pour faire un truc pareil

je vais essayer de débugger ça

[orafrance]

Je crois avoir trouvé beaucoup mieux

Essaye ceci :

Code :

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

1
2
3
4
5
6
7
8
create or replace type T_REC_ADR_CORRESP_CLIENT is object ( civ  number,
                                                      nom  varchar2(255),
                                                      prenom varchar2(255)
                                                    )
/
 
  create or replace type tab_T_REC_ADR_CORRESP_CLIENT is table of T_REC_ADR_CORRESP_CLIENT
/

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
  create or replace function FO_ADR_CORRESPONDANT_CLIENT(pnumcli number) return tab_T_REC_ADR_CORRESP_CLIENT PIPELINED;
  is
 
  cursor cur_courrier is select cli.cod_civ civ, cli.lib_prenomcli prenom, cli.lib_nom nom
                          from op.t_clients cli
                          , op.tr_type_voie typvoi
                          , op.tr_pays pay
                          , op.tr_civilite civ
                          where cli.num_cli = pnumcli 
                          and cli.cod_typvoi = typvoi.cod_typvoi (+)
                          and cli.cod_pay = pay.cod_pay (+)
                          and civ.cod_civ (+) = cli.cod_civ ;
 
  begin
 
 
       for rec in cur_courrier loop
           pipe row (T_REC_ADR_CORRESP_CLIENT(rec.civ,rec.nom,rec.prenom));
       end loop;
 
    return;
  end FO_ADR_CORRESPONDANT_CLIENT;

Code :

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

select * from table(FO_ADR_CORRESPONDANT_CLIENT(32685));

[Yorglaa]

orafrance :
je ne suis pas sûr de comprendre... dans ce cas, comment est utilisé le paramètre "pnumcli" de la fonction FO_ADR_CORRESPONDANT_CLIENT ?

Code :

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

function FO_ADR_CORRESPONDANT_CLIENT(pnumcli number)...

vu que dans le Select où tu utilise cette fonction tu ne donnes pas de paramètres

Code :

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

select * from table(FO_ADR_CORRESPONDANT_CLIENT);

éclaire-moi de ta lumière STP...

[orafrance]

c'était un oubli que j'ai corrigé

C'était pour voir si tu suivais

[superfly]

Tout d'abord merci d'avoir passer du temps sur mon probleme.
J'ai pas encore eu le temps de tester, il parait qu'il y a plus urgent
Mais je regarde cela dans la journée.

merci 8)

[superfly]

ah, je suis un peu confus j'ai oublié de présicer une chose très importante c'est que je suis en 8i.
Et du coup le pipelined ne fonctionne pas.

désolé de t'avoir fait travailler pour rien.

Mais il n'y a t'il pas moyen de faire cela avec des "fonctions tables" qui fonction sont 8i

[orafrance]

Si c'est possible mais en créant les types en base

je l'ai fait hier, ça marche pas mal mais j'ai malheureusement pas gardé le code

[superfly]

Oura, ça marche, je suis heureux.

Merci Orafrance de tout aide je post le code on sait jamais il y a peut-etre un malheureux qui veux faire pareil et ce donne du mal derriere sa machine

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
create or replace type T_ADR_CLI as object
(
    civ      varchar2(100)
  , nom      varchar2(100)
  , prenom   varchar2(100)
)
/
 
create or replace type TT_ADR_CLI is table of T_ADR_CLI
/

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
 
 
create or replace function FO_ADR_CLI (pnumcli integer)
  return TT_ADR_CLI
is
  retval TT_ADR_CLI := TT_ADR_CLI();
 
  cursor cur_courrier is select cli.cod_civ civ, cli.lib_prenomcli prenom, cli.lib_nom nom 
                         from op.t_clients cli 
                         , op.tr_type_voie typvoi 
                         , op.tr_pays pay 
                         , op.tr_civilite civ 
                         where cli.num_cli = pnumcli 
                         and cli.cod_typvoi = typvoi.cod_typvoi (+) 
                         and cli.cod_pay = pay.cod_pay (+) 
                         and civ.cod_civ (+) = cli.cod_civ ; 
 
begin
 
    for rec in cur_courrier loop 
       retval.extend;
       retval (retval.last) := T_ADR_CLI(rec.civ,rec.nom,rec.prenom); 
    end loop; 
 
    return(retval);
 
end FO_ADR_CLI;

Encore Orafrance

[arezki76]

Bonjour Orafrance,

merci pour le code que tu as transmis, il m'a aider à créer ce que je voulais.

néaumoins, j'ai un petit souci, je veux exécuter cette fonction à partir d'un autre serveur (autre base de données).

j'ai crée un synonym sur cette base avec un dblink, mais lorsque j'execute le select, ça me donne ça :

ORA-30626: function/procedure parameters of remote object types are not supported

Peux-tu me dire c'est quoi le problème?

d'avance merci.