Procédure stockée PL/SQL

[justinedr71]

Bonjour,

Je travaille avec Talend Open Studio version 4.1.2 et sur une base Oracle 11g.

J'ai une procédure stockée que je souhaite appeler pour récupérer les champs des tables de ma base afin de pouvoir ensuite faire des traitements dessus...

J'aurai souhaité savoir comment appeler cette procédure stockée dans mon job Talend :

Code sql :

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
 
"var r refcursor
declare joblist varchar2(4000);
begin
  SELECT listagg( ''''||libelle||''' as "'||libelle||'"',',') within GROUP (ORDER BY libelle) 
    INTO joblist FROM (SELECT DISTINCT substr(ccp_libelle,1,30) as libelle FROM t_cdecpt);
 
      open :r FOR 'SELECT *
FROM (select pol_numpol, sor_ident, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pol_datresil, pol_datechpro, substr(ccp_libelle,1,30) as libelle,
gad_prime_nette, pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl,
in_type
from f_polices, f_version_police, f_mouvement, f_sit_objet_risque, f_garantie_dyn, f_produitass, f_prd_risque, f_sit_assure, f_p_c_assure, f_p_personne, t_sit_maritale, f_intermediaire,
f_tarif_ass, t_intercalaire, f_tarif_gar, t_cdecpt
where gad_ptrsorid = sor_ident
and sor_ptrpolid = pol_ident
and sor_ident = mvt_ptrsuivantid
and mvt_ptrverid = ver_ident
and pol_ptrpasid = pas_ident
and pas_ident = pri_ptrpasident
and sor_ident = sar_ptrsorid (+)
and sar_ptrassid = bpass_ident (+)
and bpass_ptrbppident = bpp_ident (+)
and bpp_ptrmarid = mar_ident (+)
and pol_ptrinid = in_ident (+)
and sor_datetarif = tas_dateffet
and tas_codeintercalaire = ta_code
and ta_cle = tga_cletarif
and tga_code_compta = ccp_code
and gad_code = tga_code
and tga_genre = ''GAR''
and gad_prime_nette is not null and gad_prime_nette != 0
and (pol_datresil is null or pol_datresil >= (sysdate - to_yminterval(''03-00'')))
and ((sor_datedebut != sor_datefin) or (sor_datefin is null and sor_datedebut != pol_datresil) 
or (sor_datefin is null and pol_datresil is null and sor_datedebut != pol_datechpro))
group by pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, sor_ident, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, gad_prime_nette, substr(ccp_libelle,1,30), pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type ) 
pivot (sum(gad_prime_nette) for (libelle) in ('||joblist||'))
order by pol_numpol asc, ver_compteur asc, sor_ident asc';    
end;
/
print r"

Sachant que le schéma de mes données dépendra de cette procédure car le nombre et les intitulés des 'libelle' que j'aurai en colonne varie...

Merci pour votre aide!

[justinedr71]

PROBLEME!!!


Je travaille aussi sur des bases client en 10g, du coup cette requête n'est plus compatible...

Code sql :

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
 
var r refcursor
declare joblist varchar2(4000);
begin
  SELECT listagg( ''''||libelle||''' as "'||libelle||'"',',') within GROUP (ORDER BY libelle) 
    INTO joblist FROM (SELECT DISTINCT substr(ccp_libelle,1,30) AS libelle FROM t_cdecpt);
 
      open :r FOR 'SELECT *
FROM (select pol_numpol, sor_ident, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pol_datresil, pol_datechpro, substr(ccp_libelle,1,30) as libelle,
gad_prime_nette, pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl,
in_type
from f_polices, f_version_police, f_mouvement, f_sit_objet_risque, f_garantie_dyn, f_produitass, f_prd_risque, f_sit_assure, f_p_c_assure, f_p_personne, t_sit_maritale, f_intermediaire,
f_tarif_ass, t_intercalaire, f_tarif_gar, t_cdecpt
where gad_ptrsorid = sor_ident
and sor_ptrpolid = pol_ident
and sor_ident = mvt_ptrsuivantid
and mvt_ptrverid = ver_ident
and pol_ptrpasid = pas_ident
and pas_ident = pri_ptrpasident
and sor_ident = sar_ptrsorid (+)
and sar_ptrassid = bpass_ident (+)
and bpass_ptrbppident = bpp_ident (+)
and bpp_ptrmarid = mar_ident (+)
and pol_ptrinid = in_ident (+)
and sor_datetarif = tas_dateffet
and tas_codeintercalaire = ta_code
and ta_cle = tga_cletarif
and tga_code_compta = ccp_code
and gad_code = tga_code
and tga_genre = ''GAR''
and gad_prime_nette is not null and gad_prime_nette != 0
and (pol_datresil is null or pol_datresil >= (sysdate - to_yminterval(''03-00'')))
and ((sor_datedebut != sor_datefin) or (sor_datefin is null and sor_datedebut != pol_datresil) 
or (sor_datefin is null and pol_datresil is null and sor_datedebut != pol_datechpro))
group by pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, sor_ident, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, gad_prime_nette, substr(ccp_libelle,1,30), pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type ) 
pivot (sum(gad_prime_nette) for (libelle) in ('||joblist||'))
order by pol_numpol asc, ver_compteur asc, sor_ident asc';    
end;
/
print r

Quel serait son équivalent en 10g. Toujours en sachant donc que je ne connais ni le nombre, ni les valeurs de mes ccp_libelle...

Merci pour votre aide!

[justinedr71]

Voila ma nouvelle requête, utilisant DECODE :

Code sql :

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
 
SELECT pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, sor_ident, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type,
         max( decode( ccp_libelle, 'Vol', gad_prime_nette, NULL ) ) Vol,
         max( decode( ccp_libelle, 'Incendie', gad_prime_nette, NULL ) ) Incendie
    FROM ( SELECT pol_numpol, sor_ident, ver_compteur, ver_libelle, ver_dateoperation, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pol_datresil, pol_datechpro, ccp_libelle,
gad_prime_nette, pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl,
in_type
FROM f_polices, f_version_police, f_mouvement, f_sit_objet_risque, f_garantie_dyn, f_produitass, f_prd_risque, f_sit_assure, f_p_c_assure, f_p_personne, t_sit_maritale, f_intermediaire,
f_tarif_ass, t_intercalaire, f_tarif_gar, t_cdecpt
WHERE gad_ptrsorid = sor_ident
AND sor_ptrpolid = pol_ident
AND sor_ident = mvt_ptrsuivantid
AND mvt_ptrverid = ver_ident
AND pol_ptrpasid = pas_ident
AND pas_ident = pri_ptrpasident
AND sor_ident = sar_ptrsorid (+)
AND sar_ptrassid = bpass_ident (+)
AND bpass_ptrbppident = bpp_ident (+)
AND bpp_ptrmarid = mar_ident (+)
AND pol_ptrinid = in_ident (+)
AND sor_datetarif = tas_dateffet
AND tas_codeintercalaire = ta_code
AND ta_cle = tga_cletarif
AND tga_code_compta = ccp_code
AND gad_code = tga_code
AND tga_genre = 'GAR'
AND gad_prime_nette IS NOT NULL AND gad_prime_nette != 0
AND (pol_datresil IS NULL OR pol_datresil >= (sysdate - to_yminterval('03-00')))
AND ((sor_datedebut != sor_datefin) OR (sor_datefin IS NULL AND sor_datedebut != pol_datresil) 
OR (sor_datefin IS NULL AND pol_datresil IS NULL AND sor_datedebut != pol_datechpro))
GROUP BY pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, sor_ident, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, gad_prime_nette, ccp_libelle, pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type )
   GROUP BY pol_numpol, pol_datresil, pol_datechpro, ver_compteur, ver_libelle, ver_dateoperation, sor_ident, sor_datedebut, sor_datefin, sor_datedeb_fact, sor_datefin_fact, pas_nb_jours_prorata, pri_type_risque, bpp_type, bpp_pp_sexe, bpp_pp_nais_date, bpp_pp_nais_pay_code, mar_libelle, bpp_pp_nb_enfants, bpp_pp_handicape, in_code_etabl, in_type

Ormis le fait qu'ici, je connais déjà les deux garanties 'Vol' et 'Incendie'... Comment je pourrais faire dans le cas où je ne les connais pas? Récupérer l'ensemble de mes garanties dans une variable globale et l'injecter dans la requête?

Merci pour vos réponses