Stocker le résultat d'une requête en SQL dynamique

Version imprimable

Bonjour à tous, j'ai le souci suivant: je n'arrive pas à récupérer le résultat (à l'écran ou en spool) produit par un execute immediate au sein d'un bloc PL/SQL.

Pourtant si elle est exécutée directement sous SQL, alors j'ai bien un résultat.
Mais en passant par le sql dynamique aucun retour. Soit la partie appelant
ma requête (la fonction f_trace me permet d'afficher l'ordre avant éxécution)
Code:

1 2 3 4 5 6 7 8 9 10 if ( '&mode' in ('O','o') ) then dbms_output.put_line ('On passe dans la requete 1'); f_trace (requete1); execute immediate requete1 ; else dbms_output.put_line ('On passe dans la requete 2'); f_trace (requete2); execute immediate requete2; end if;
Y aurait-il une option à activer?

21/10/2008, 14h47
SheikYerbouti

Citation:

...je n'arrive pas à récupérer le résultat...

c'est quoi la requête, un curseur ?

Mes requêtes sont construites de cette manière:

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
35
36
37
 
requete1 := '
SELECT nat.idf_agent                                             MATRICULE ,
his.nom_usuel  || '' '' ||  his.Nom_prenom                  NOM_PRENOM,  '
||to_number(to_char(date_travail,'YYYY'))||'-to_number(to_char(ide.dat_naissance,''YYYY'')) AGE,
to_char(ide.dat_naissance,''DD/MM/YYYY'')                   DATE_NAISSANCE
FROM OG.IDENT ide,
     OG.IDENT_HIS his,
     RH.AGTNAT nat 
WHERE  ide.ident_id = his.ident_id
AND    nat.ident_id  = his.ident_id
AND   cod_gestion in (''0'',''2'') 
and idf_agent in (select distinct idf_agent from rh.agtpai)
AND   nat.cod_coll =''toto''
AND '|| to_number( to_char(date_travail,'YYYY' ))|| ' - to_number(to_char(ide.dat_naissance,''YYYY'')) = 65
AND to_date(to_char(his.dat_debut,''DD/MM/YYYY''),''DD/MM/YYYY'') <='''||to_char(date_travail,'DD/MM/YYYY')||
''' AND to_date(to_char(his.dat_fin,''DD/MM/YYYY''),''DD/MM/YYYY'') > '''   ||to_char(date_travail,'DD/MM/YYYY')||'''
ORDER BY MATRICULE ';
 
requete2 := '
SELECT nat.idf_agent                                             MATRICULE ,
       his.nom_usuel  || '' '' ||  his.Nom_prenom                  NOM_PRENOM,
       to_char(sysdate,''YYYY'')-to_char(ide.dat_naissance,''YYYY'') AGE,
       to_char(ide.dat_naissance,''DD/MM/YYYY'')                   DATE_NAISSANCE
FROM OG.IDENT ide,
     OG.IDENT_HIS his,
     RH.AGTNAT nat 
WHERE  ide.ident_id = his.ident_id
AND    nat.ident_id  = his.ident_id
AND   cod_gestion in (''0'',''2'') 
and idf_agent in (select distinct idf_agent from rh.agtpai)
AND   nat.cod_coll =''toto''
AND to_number( to_char(to_date('''|| date_travail|| ''',''DD/MM/YY''),''YYYY'' ) ) - to_number(to_char(ide.dat_naissance,''YYYY''))= 65
AND to_number(to_char(to_date('''||date_travail||''',''DD/MM/YY''),''MM'')) = to_number(to_char(ide.dat_naissance,''MM''))
AND to_date(to_char(his.dat_debut,''DD/MM/YYYY''),''DD/MM/YYYY'') <= '''||date_travail||'''
AND to_date(to_char(his.dat_fin,''DD/MM/YYYY''),''DD/MM/YYYY'') >    '''||date_travail||'''
ORDER BY MATRICULE ';

Donc à priori ce ne sont pas des curseurs (si j'ai tout bien compris).
Au passage, ta fonction est nickel!

21/10/2008, 15h15
SheikYerbouti

Si c'est un ordre Select, c'est forcément un curseur.
Voyez le tuto sur le SQL dynamique, particlièrement le traitement des collections (BULK COLLECT)

Après avoir lu ton tuto j'ai bien avancé et j'obtiens ce que je veux sur mon client Windows . Mais ça plante quand je l'exécute sur mon client solaris ça me génère 1 ORA-01843.

Soit la déclaration de mes types pour la collection:
Code:

1 2 3 4 5 6 7 8 9 10 type T_idf_agent is table of rh.agtnat.idf_agent%type; type T_nom_prenom is table of varchar2(45); type T_age is table of number(2); type T_naissance is table of date; ident_agent T_idf_agent; nom T_nom_prenom ; age T_age; naissance T_naissance;
Voici là ou j'ai mon plantage (cas requête 1)
Code:

1 2 execute immediate requete1 BULK COLLECT INTO ident_agent, nom,age,naissance;
Si je désactive la collection en mettant :
Code:

1 2 execute immediate requete1;
alors pas d'erreur de plantage de ce type.

Pour info voici ce que me donne sur mon client solaris les affichages suivants:
Code:

1 2 3 dbms_output.put_line('Date travail courante: ' ||to_char(date_travail,'DD/MM/YYYY')); dbms_output.put_line('Date travail courante: ' ||to_char(date_travail));
J'ai respectivement:

Citation:

Date travail courante: 22/10/2008
Date travail courante: 22-OCT-08

Ou faut-il que j'intervienne pour qu'il remplisse la collection sans plantage?

22/10/2008, 10h35
skuatamad
Salut,
Code:

1 2 3 4 type T_naissance IS TABLE of date; naissance T_naissance; ... to_char(ide.dat_naissance,''DD/MM/YYYY'') DATE_NAISSANCE
Essaie sans le to_char.

Pour essayer de comprendre la différence entre le client windows et le client solaris regarde les paramètres NLS de session:

Code:

select * from nls_session_parameters;

Le NLS_DATE_FORMAT du client windows doit permettre une conversion implicite du char en date.
22/10/2008, 11h12
pascal_T
Voici ce que me donne les infos entre mon client windows et solaris:

Citation:

windows ==
NLS_DATE_FORMAT DD/MM/RR

puis solaris ==
NLS_DATE_FORMAT DD-MON-RR

J'ai désactivé la fonction du to_char dans ma requête pour ne récupérer que la date:
Code:

1 2 ide.dat_naissance DATE_NAISSANCE
Mais j'ai la même erreur.

Je viens de faire le test et pour moi ça marche bien.

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
SQL> select * from nls_session_parameters;
 
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
 
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
 
17 rows selected.
 
SQL> create table t (c date);
 
Table created.
 
SQL> insert into t values (sysdate);
 
1 row created.
 
SQL> insert into t values (sysdate+1);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> declare
  2  req varchar2(1000);
  3  type t_date is table of date;
  4  col t_date;
  5  begin
  6    req:='select to_char(c,''dd/mm/yyyy'') from t';
  7    execute immediate req bulk collect into col;
  8    for i in col.first..col.last loop
  9        dbms_output.put_line(col(i));
 10    end loop;
 11  end;
 12  /
declare
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 7
 
 
SQL> declare
req varchar2(1000);
type t_date is table of date;
col t_date;
begin
  req:='select c from t';
  execute immediate req bulk collect into col;
  for i in col.first..col.last loop
      dbms_output.put_line(col(i));
  end loop;
end;  2    3    4    5    6    7    8    9   10   11
 12  /
22-OCT-08
23-OCT-08
 
PL/SQL procedure successfully completed.
 
SQL>

Ta colonne ide.dat_naissance est elle bien de type date ?

En fait mon ordre plantait car la requete1 avait d'autre lignes utilisant le to_char, soit ma requête:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
requete1 := '
SELECT nat.idf_agent                                             MATRICULE ,
his.nom_usuel  || '' '' ||  his.Nom_prenom                  NOM_PRENOM,  '
||to_number(to_char(date_travail,'YYYY'))||'-to_number(to_char(ide.dat_naissance,''YYYY'')) AGE,
to_char(ide.dat_naissance,''DD/MM/YYYY'')                   DATE_NAISSANCE
FROM OG.IDENT ide,
     OG.IDENT_HIS his,
     RH.AGTNAT nat 
WHERE  ide.ident_id = his.ident_id
AND    nat.ident_id  = his.ident_id
AND   cod_gestion in (''0'',''2'') 
and idf_agent in (select distinct idf_agent from rh.agtpai)
AND   nat.cod_coll =''toto''
AND '|| to_number( to_char(date_travail,'YYYY' ))|| ' - to_number(to_char(ide.dat_naissance,''YYYY'')) = 65
AND to_date(to_char(his.dat_debut,''DD/MM/YYYY''),''DD/MM/YYYY'') <='''||to_char(date_travail,'DD/MM/YYYY')||
''' AND to_date(to_char(his.dat_fin,''DD/MM/YYYY''),''DD/MM/YYYY'') > '''   ||to_char(date_travail,'DD/MM/YYYY')||'''
ORDER BY MATRICULE ';

Ainsi avec NLS_DATE_FORMAT DD-MON-RR , la requête suivante plantait avec le ora-01840

Code:

1
2
 
execute immediate requete1 BULK COLLECT INTO ident_agent, nom,age,naissance;

Mais en mettant ceci, ça fonctionne et renvoie des résultats.

Code:

1
2
 
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';

De plus j'ai remarqué les choses suivantes

en sql direct, ça fonctionne

Code:

1
2
3
4
 
select to_char(dat_naissance,'DD/MM/YYYY')  from og.ident,rh.agtnat
where agtnat.ident_id=ident.ident_id
and idf_agent=30;

en PL_SQL , plantage ora 01840:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
declare 
req varchar2(1000); 
type t_date IS TABLE of date; 
col t_date;
begin 
req:='select to_char(dat_naissance,''DD/MM/YYYY'') from og.ident,rh.agtnat
where agtnat.ident_id=ident.ident_id
and idf_agent=30';
execute immediate req bulk collect INTO col; 
FOR i IN col.first..col.last loop 
dbms_output.put_line(col(i)); 
end loop;
end;

en mettant , ça fonctionne.

Code:

1
2
 
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';

La par contre je ne comprends pas le sql en direct se comporte de manière différente du PL/SQL.

22/10/2008, 16h04
skuatamad

C'est normal, dans une collection de type date on insère une date et pas une chaîne de caractères !

Si tu insères une chaîne de caractères tu laisses oracle la reconvertir en date par le biais d'une conversion implicite, qui dépend des paramêtres NLS de ta session (ca ne plantait pas dans ta session windows).

Pour le SQL, c'est juste de l'affichage alors c'est normal d'utiliser to_char au niveau du select, par contre c'est tout aussi risqué de comparer une date à une chaîne de caractères dans une clause where.
Il y aura également conversion implicite dépendant des paramêtres NLS de ta session.

Pour conclure je dirais : Toujours fuir les conversions implicites et comparer des données de même type !
22/10/2008, 16h17
pascal_T

Je ne pensais pas que le changement de plateforme pouvait être aussi galère.

C'est la première fois que ça m'arrive, car avec le sql ça marchait implicitement, mais pas avec le PL-SQL qui veut un bon format de date (+ rigoureux en quelque sorte que le sql).

Je vais donc clôturer ce ticket, qui m'a permis d'apprendre quelque petits trucs bien sympas et m'a remis en place certaines idées.

Merci à tous.:yaisse2: