[PL/SQL] ORA-06502 chaîne de caractères trop petite

**Pownee** · 18/03/2009, 16h38

Bonjour,

J'ai créer un procédure PL/SQL qui me créer une vue avec deux paramètres.
Voici le code

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
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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
create or replace
procedure p_testcutoff(date1 IN DATE, date2 IN DATE) AS
  rq LONG (10000);
BEGIN
  rq := 'CREATE OR REPLACE VIEW v_testcutoff AS
select cd_client code_client, ka_nom||''_''||ka_prenom Nom, Requete.cd_dossier code_dossier, dos_nom||''_''||dos_prenom dos_nom, cd_segm 
  ,dos_dt_deb debut_sejour,dos_dt_fin fin_sejour,fac_date date_facture,cd_ori||''_''||cd_orisec Orientation,code_prestation,type_prestation, ser_cpt compte_general,ana_compte compte_analytique,tva_tx taux_tva
  ,Nb_Jour_Effectif_Total,nombre Nb_Jour_Effectif ,lig_mt_tot montant_total_ligne,montant,Indicateur
   from(
  -- FAE Factures A Editer 
  select d_dos.cd_dossier,dos_nom, dos_prenom,d_dos.cd_segm,dos_dt_deb,dos_dt_fin,fac_date,dos_duree Nb_Jour_Effectif_Total,nombre
  , lig_tp_lig type_prestation
  ,coalesce(cd_soin,cd_forf,cd_bout,cd_abon,cd_reg) code_prestation
  ,cd_service,cd_analy,cd_tva
  ,lig_mt_tot
   , case when (lig_tp_lig=''S'' or lig_tp_lig=''O'') or (lig_tp_lig=''F'' and cd_forf=''*FD'') then lig_mt_tot 
  else case when lig_tp_lig=''F'' then round(lig_mt_tot * nombre / dos_duree,2)
  end
   end montant,''FAE'' Indicateur,f_lig.cd_facid
   from d_dos, z_liens z1,z_liens z2, f_fac, f_lig, f_lig_srv,zd_seg
  ,(select count(*) nombre, cd_dossier from(
   select cd_dossier,to_timestamp(dos_dt_deb,''DD/MM/YYYY'')+zp_cre.cd_numero datecal
 
   from d_dos d,zp_cre,zd_seg
   where 
   dos_dt_int is null
   and d.dos_dt_deb between ' || date1 || 'and ' || date2 || '
   and dos_dt_fin>' || date2 || '
   and d.cd_segm=zd_seg.CD_SEGM
   and to_timestamp(dos_dt_deb,''DD/MM/YYYY'')+zp_cre.cd_numero<=' || date2 || '
   and decode(seg_dimanc,''1'',to_char(to_timestamp(dos_dt_deb,''DD/MM/YYYY'')+zp_cre.cd_numero,''D''),''1'')<>decode(seg_dimanc,''1'',1,''2'')
   and decode(seg_ferie,''1'',to_char(to_timestamp(dos_dt_deb,''DD/MM/YYYY'')+zp_cre.cd_numero,''ddmm''),''1'') <>decode(seg_ferie,''1'',''01/05'',''2'')
   and decode(seg_ferie,''1'',to_char(to_timestamp(dos_dt_deb,''DD/MM/YYYY'')+zp_cre.cd_numero,''ddmm''),''1'') <>decode(seg_ferie,''1'',''14/07'',''2'')
   and decode(seg_ferie,''1'',to_char(to_timestamp(dos_dt_deb,''DD/MM/YYYY'')+zp_cre.cd_numero,''ddmm''),''1'') <>decode(seg_ferie,''1'',''15/08'',''2'')
  ) group by cd_dossier) NbJour  
 
   where d_dos.dos_dt_deb between ' || date1 || ' and ' || date2 || '
   and dos_dt_fin>' || date2 || '
   and d_dos.cd_segm=zd_seg.CD_SEGM
   and seg_sejour<>''2''
   and d_dos.cd_dossier=z1.code
   and z1.utype=''D_DOS''
   and z1.idlien=z2.idlien
   and z2.utype=''F_FAC''
   and z2.CODE=f_fac.cd_facid
   and fac_date is null
   and d_dos.cd_dossier=nbjour.cd_dossier
   and f_fac.cd_facid=f_lig.cd_facid
   and f_lig.lig_tp_lig in(''F'',''S'',''O'',''B'')
   and f_lig.cd_ligne=f_lig_srv.cd_ligne
 
  UNION ALL
  -- PCA hors TPV et PASSAGE 
  select d_dos.cd_dossier,dos_nom, dos_prenom,d_dos.cd_segm,dos_dt_deb,dos_dt_fin,fac_date,dos_duree Nb_Jour_Effectif_Total,nombre
  , lig_tp_lig type_prestation
  ,coalesce(cd_soin,cd_forf,cd_bout,cd_abon,cd_reg) code_prestation
  ,cd_service,cd_analy,cd_tva
  ,lig_mt_tot
   , case when (lig_tp_lig=''S'' or lig_tp_lig=''O'') or (lig_tp_lig=''F'' and cd_forf=''*FD'') then 0
  else case when lig_tp_lig=''F'' then round((lig_mt_tot / dos_duree)*nombre,2)
  end
   end montant,''PCA'' Indicateur,f_lig.cd_facid
   from d_dos, z_liens z1,z_liens z2, f_fac, f_lig, f_lig_srv,zd_seg
  ,(select count(*) nombre, cd_dossier from(
   select cd_dossier,to_timestamp(' || date2 || ',''DD/MM/YYYY'')+zp_cre.cd_numero datecal
 
   from d_dos d,zp_cre,zd_seg
   where 
   dos_dt_int is null
   and d.dos_dt_deb between ' || date1 || ' and ' || date2 || '
   and dos_dt_fin >' || date2 || '
   and d.cd_segm=zd_seg.CD_SEGM
   and to_timestamp(:dt2,''DD/MM/YYYY'')+zp_cre.cd_numero<dos_dt_fin
   and decode(seg_dimanc,''1'',to_char(to_timestamp(' || date2 || ',''DD/MM/YYYY'')+zp_cre.cd_numero,''D''),''1'')<>decode(seg_dimanc,''1'',1,''2'')
   and decode(seg_ferie,''1'',to_char(to_timestamp(' || date2 || ',''DD/MM/YYYY'')+zp_cre.cd_numero,''ddmm''),''1'') <>decode(seg_ferie,''1'',''01/05'',''2'')
   and decode(seg_ferie,''1'',to_char(to_timestamp(' || date2 || ',''DD/MM/YYYY'')+zp_cre.cd_numero,''ddmm''),''1'') <>decode(seg_ferie,''1'',''14/07'',''2'')
   and decode(seg_ferie,''1'',to_char(to_timestamp(' || date2 || ',''DD/MM/YYYY'')+zp_cre.cd_numero,''ddmm''),''1'') <>decode(seg_ferie,''1'',''15/08'',''2'')
  ) group by cd_dossier) NbJour  
 
   where  d_dos.dos_dt_deb between ' || date1 || ' and ' || date2 || '
   and dos_dt_fin >' || date2 || '
   and d_dos.cd_segm=zd_seg.CD_SEGM
   and seg_sejour<>''2''
   and d_dos.cd_dossier=z1.code
   and z1.utype=''D_DOS''
   and z1.idlien=z2.idlien
   and z2.utype=''F_FAC''
   and z2.CODE=f_fac.cd_facid
   and fac_date is not null
   and fac_code=''F''
   and d_dos.cd_dossier=nbjour.cd_dossier
   and f_fac.cd_facid=f_lig.cd_facid
   and f_lig.lig_tp_lig in(''F'',''S'',''O'',''B'')
   and f_lig.cd_ligne=f_lig_srv.cd_ligne
 
   UNION ALL
    -- PCA spécifique pour TPV et PASSAGE 
   select d_dos.cd_dossier,dos_nom, dos_prenom,d_dos.cd_segm,dos_dt_deb,dos_dt_fin,fac_date
  ,to_number(for_dmt) Nb_jour_effectif_total
  ,to_number(for_dmt-1)  - (to_date(' || date2 || ',''DD/MM/YYYY'')-dos_dt_deb) nombre, lig_tp_lig type_prestation
  ,f_lig.cd_forf code_prestation
  ,cd_service,cd_analy,cd_tva
  ,lig_mt_tot
  , round((lig_mt_tot /for_dmt)*
  (to_number(for_dmt-1)  - (to_date(' || date2 || ',''DD/MM/YYYY'')-dos_dt_deb)),2) montant
  ,''PCA'' Indicateur,f_lig.cd_facid
   from d_dos, z_liens z1,z_liens z2, f_fac, f_lig, f_lig_srv,zd_seg,p_forf
 
   where  d_dos.dos_dt_deb between ' || date1 || ' and ' || date2 || '
   and to_timestamp(dos_dt_deb,''DD/MM/YYYY'') + for_dmt > ' || date2 || '
   and d_dos.cd_segm=zd_seg.CD_SEGM
   and seg_sejour=''2''
   and d_dos.cd_dossier=z1.code
   and z1.utype=''D_DOS''
   and z1.idlien=z2.idlien
   and z2.utype=''F_FAC''
   and z2.CODE=f_fac.cd_facid
   and fac_date is not null
   and fac_code=''F''
   and f_fac.cd_facid=f_lig.cd_facid
   and f_lig.lig_tp_lig in(''F'')
   and f_lig.cd_forf=p_forf.cd_forf
   and f_lig.cd_ligne=f_lig_srv.cd_ligne ) Requete,z_liens z1,z_liens z2,k_cli,d_prs_e,zd_ori,zc_ser,zc_ana,zc_tva
   where Requete.cd_dossier=z1.code
   and z1.utype=''D_DOS''
   and z1.idlien=z2.idlien(+)
   and z2.UTYPE(+)=''K_CLI''
   and z2.CODE=K_CLI.cd_client(+)
   and Requete.cd_dossier=d_prs_e.cd_dossier(+)
   and Requete.cd_facid=d_prs_e.cd_facid(+)
   and d_prs_e.cd_numori=zd_ori.cd_numori(+)
   and Requete.cd_service=zc_ser.cd_service
   and Requete.cd_analy=zc_ana.cd_analy
    and Requete.cd_tva=zc_tva.cd_tva
    order by dos_nom';
 
  EXECUTE IMMEDIATE rq;
 
END;

Quand je l'exécute, le message d'erreur suivant s'affiche:

ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: tampon de chaîne de caractères trop petit
ORA-06512: à "ADNCOMPTASAGE.V_TESTCUTOFF", ligne 5
ORA-06512: à ligne 8
Process exited.

Je ne comprend pas. Ma variable 'rq' est pourtant suffisamment grande pour contenir la requête (si le problème vient bien de la...).
Y a t-il une erreur dans mon code?

Merci d'avance pour vos réponses.

**Pownee** · 18/03/2009, 17h17

Re,

Je me suis rendu compte que je compilait pas sur la bonne version de mon code.
Dsl pour le dérangement.

[PL/SQL] ORA-06502 chaîne de caractères trop petite

PL/SQL Oracle

Discussions similaires

Partager

Partager