ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: tampon de chaîne de caractères trop pet

[pcouas]

Hi,

I have created an PL/SQL function . This function return an big SQL String for replace PIVOT function into Oracle10

I call it with select DP_LO_GRAPH.F_GET_GRAPH_BY_VESSEL() from dual

I have follwing error message ?

ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: tampon de chaîne de caractères trop pet[/code]

 

Following dynamic query return by function is

Give me following result for IREPORT

 

SELECT  null,label, sum(decode(VESSEL_UID,1,value1,0)) as AKNOUL,sum(decode(VESSEL_UID,2,value1,0)) as ANLWANGARATTA,sum(decode(VESSEL_UID,3,value1,0)) as ANLWYONG,sum(decode(VESSEL_UID,4,value1,0)) as CAPCAMARAT,sum(decode(VESSEL_UID,5,value1,0)) as CAPCANAILLE,sum(decode(VESSEL_UID,6,value1,0)) as CMACGMAFRICAFOUR,sum(decode(VESSEL_UID,7,value1,0)) as CMACGMAFRICAONE,sum(decode(VESSEL_UID,8,value1,0)) as CMACGMAFRICATHREE,sum(decode(VESSEL_UID,9,value1,0)) as CMACGMAFRICATWO,sum(decode(VESSEL_UID,10,value1,0)) as CMACGMALCAZAR,sum(decode(VESSEL_UID,11,value1,0)) as CMACGMAMBER,sum(decode(VESSEL_UID,12,value1,0)) as CMACGMAMERICA,sum(decode(VESSEL_UID,13,value1,0)) as CMACGMAMERIGOVESPUCCI,sum(decode(VESSEL_UID,14,value1,0)) as CMACGMANDROMEDA,sum(decode(VESSEL_UID,15,value1,0)) as CMACGMAQUILA,sum(decode(VESSEL_UID,16,value1,0)) as CMACGMARISTOTE,sum(decode(VESSEL_UID,17,value1,0)) as CMACGMBELLINI,sum(decode(VESSEL_UID,18,value1,0)) as CMACGMBERLIOZ,sum(decode(VESSEL_UID,19,value1,0)) as CMACGMBLUEWHALE,sum(decode(VESSEL_UID,20,value1,0)) as CMACGMCALLISTO,sum(decode(VESSEL_UID,21,value1,0)) as CMACGMCENDRILLON,sum(decode(VESSEL_UID,22,value1,0)) as CMACGMCHATEAUDIF,sum(decode(VESSEL_UID,23,value1,0)) as CMACGMCHOPIN,sum(decode(VESSEL_UID,24,value1,0)) as CMACGMCHRISTOPHECOLOMB,sum(decode(VESSEL_UID,25,value1,0)) as CMACGMCORAL,sum(decode(VESSEL_UID,26,value1,0)) as CMACGMCORTEREAL,sum(decode(VESSEL_UID,27,value1,0)) as CMACGMEIFFEL,sum(decode(VESSEL_UID,28,value1,0)) as CMACGMFIDELIO,sum(decode(VESSEL_UID,29,value1,0)) as CMACGMFIGARO,sum(decode(VESSEL_UID,30,value1,0)) as CMACGMFLORIDA,sum(decode(VESSEL_UID,31,value1,0)) as CMACGMFORTSTGEORGES,sum(decode(VESSEL_UID,32,value1,0)) as CMACGMFORTSTLOUIS,sum(decode(VESSEL_UID,33,value1,0)) as CMACGMFORTSTPIERRE,sum(decode(VESSEL_UID,34,value1,0)) as CMACGMFORTSTEMARIE,sum(decode(VESSEL_UID,35,value1,0)) as CMACGMGEORGIA,sum(decode(VESSEL_UID,36,value1,0)) as CMACGMHERODOTE,sum(decode(VESSEL_UID,37,value1,0)) as CMACGMHOMERE,sum(decode(VESSEL_UID,38,value1,0)) as CMACGMHYDRA,sum(decode(VESSEL_UID,39,value1,0)) as CMACGMIMPALA,sum(decode(VESSEL_UID,40,value1,0)) as CMACGMJAMAICA,sum(decode(VESSEL_UID,41,value1,0)) as CMACGMJUNIORS,sum(decode(VESSEL_UID,43,value1,0)) as CMACGMLASCALA,sum(decode(VESSEL_UID,44,value1,0)) as CMACGMLATOUR,sum(decode(VESSEL_UID,45,value1,0)) as CMACGMLATRAVIATA,sum(decode(VESSEL_UID,46,value1,0)) as CMACGMLAMARTINE,sum(decode(VESSEL_UID,47,value1,0)) as CMACGMLAPEROUSE,sum(decode(VESSEL_UID,48,value1,0)) as CMACGMLEO,sum(decode(VESSEL_UID,49,value1,0)) as CMACGMLIBRA,sum(decode(VESSEL_UID,50,value1,0)) as CMACGMMAGELLAN,sum(decode(VESSEL_UID,51,value1,0)) as CMACGMMANET,sum(decode(VESSEL_UID,53,value1,0)) as CMACGMMATISSE,sum(decode(VESSEL_UID,54,value1,0)) as CMACGMMAUPASSANT,sum(decode(VESSEL_UID,55,value1,0)) as CMACGMMEDEA,sum(decode(VESSEL_UID,56,value1,0)) as CMACGMMOZART,sum(decode(VESSEL_UID,57,value1,0)) as CMACGMMUSCA,sum(decode(VESSEL_UID,58,value1,0)) as CMACGMNABUCCO,sum(decode(VESSEL_UID,59,value1,0)) as CMACGMNEWJERSEY,sum(decode(VESSEL_UID,60,value1,0)) as CMACGMNORMA,sum(decode(VESSEL_UID,61,value1,0)) as CMACGMOKAPI,sum(decode(VESSEL_UID,62,value1,0)) as CMACGMORAN,sum(decode(VESSEL_UID,64,value1,0)) as CMACGMOTELLO,sum(decode(VESSEL_UID,65,value1,0)) as CMACGMPEGASUS,sum(decode(VESSEL_UID,66,value1,0)) as CMACGMPLATON,sum(decode(VESSEL_UID,67,value1,0)) as CMACGMPUCCINI,sum(decode(VESSEL_UID,68,value1,0)) as CMACGMPUGET,sum(decode(VESSEL_UID,69,value1,0)) as CMACGMRABAT,sum(decode(VESSEL_UID,70,value1,0)) as CMACGMRIGOLETTO,sum(decode(VESSEL_UID,71,value1,0)) as CMACGMROSSINI,sum(decode(VESSEL_UID,72,value1,0)) as CMACGMSAMBHAR,sum(decode(VESSEL_UID,73,value1,0)) as CMACGMSIMBA,sum(decode(VESSEL_UID,74,value1,0)) as CMACGMSIWA,sum(decode(VESSEL_UID,75,value1,0)) as CMACGMSTRAUSS,sum(decode(VESSEL_UID,76,value1,0)) as CMACGMSWORDFISH,sum(decode(VESSEL_UID,77,value1,0)) as CMACGMTARPON,sum(decode(VESSEL_UID,78,value1,0)) as CMACGMTHALASSA,sum(decode(VESSEL_UID,79,value1,0)) as CMACGMTOSCA,sum(decode(VESSEL_UID,80,value1,0)) as CMACGMUTRILLO,sum(decode(VESSEL_UID,81,value1,0)) as CMACGMVERDI,sum(decode(VESSEL_UID,82,value1,0)) as CMACGMVIRGINIA,sum(decode(VESSEL_UID,83,value1,0)) as CMACGMVIVALDI,sum(decode(VESSEL_UID,84,value1,0)) as CMACGMWAGNER,sum(decode(VESSEL_UID,85,value1,0)) as CMACGMWHITESHARK,sum(decode(VESSEL_UID,86,value1,0)) as DELMASKETA,sum(decode(VESSEL_UID,87,value1,0)) as DELMASSWALA,sum(decode(VESSEL_UID,88,value1,0)) as GASCOGNE,sum(decode(VESSEL_UID,89,value1,0)) as JULIEDELMAS,sum(decode(VESSEL_UID,90,value1,0)) as KUMASI,sum(decode(VESSEL_UID,91,value1,0)) as MARIEDELMAS,sum(decode(VESSEL_UID,92,value1,0)) as MARIONDUFRESNE,sum(decode(VESSEL_UID,93,value1,0)) as MOROTAI,sum(decode(VESSEL_UID,94,value1,0)) as OUEDEDDAHAB,sum(decode(VESSEL_UID,95,value1,0)) as OUEDZIZ,sum(decode(VESSEL_UID,96,value1,0)) as SORAYA,sum(decode(VESSEL_UID,97,value1,0)) as VILLEDAQUARIUS,sum(decode(VESSEL_UID,98,value1,0)) as VILLEDORION,sum(decode(VESSEL_UID,102,value1,0)) as CMACGMCOLUMBA,sum(decode(VESSEL_UID,103,value1,0)) as FASPROVENCE,sum(decode(VESSEL_UID,104,value1,0)) as CMACGMDALILA,sum(decode(VESSEL_UID,106,value1,0)) as CMACGMALMAVIVA,sum(decode(VESSEL_UID,107,value1,0)) as CMACGMTITUS,sum(decode(VESSEL_UID,108,value1,0)) as CMACGMCENTAURUS,sum(decode(VESSEL_UID,109,value1,0)) as CMACGMTITAN,sum(decode(VESSEL_UID,110,value1,0)) as CMACGMCASSIOPEIA,sum(decode(VESSEL_UID,111,value1,0)) as CMACGMGEMINI,sum(decode(VESSEL_UID,112,value1,0)) as CMACGMLYRA,sum(decode(VESSEL_UID,113,value1,0)) as ELISADELMAS,sum(decode(VESSEL_UID,114,value1,0)) as FASVAR,sum(decode(VESSEL_UID,115,value1,0)) as FLORADELMAS,sum(decode(VESSEL_UID,116,value1,0)) as CMACGMKAILAS,sum(decode(VESSEL_UID,117,value1,0)) as LAURADELMAS,sum(decode(VESSEL_UID,118,value1,0)) as LUCIEDELMAS,sum(decode(VESSEL_UID,119,value1,0)) as NALADELMAS,sum(decode(VESSEL_UID,120,value1,0)) as NICOLASDELMAS,sum(decode(VESSEL_UID,121,value1,0)) as ROSADELMAS,sum(decode(VESSEL_UID,122,value1,0)) as SAINTROCH,sum(decode(VESSEL_UID,124,value1,0)) as MIDTEST,sum(decode(VESSEL_UID,132,value1,0)) as CMACGMMARCOPOLO,sum(decode(VESSEL_UID,134,value1,0)) as CMACGMALEXANDERVONHUMBOLDT,sum(decode(VESSEL_UID,135,value1,0)) as CMACGMJULESVERNE,sum(decode(VESSEL_UID,141,value1,0)) as CMACGMDANUBE,sum(decode(VESSEL_UID,142,value1,0)) as CMACGMELBE,sum(decode(VESSEL_UID,143,value1,0)) as CMACGMLOIRE,sum(decode(VESSEL_UID,144,value1,0)) as CMACGMRHONE,sum(decode(VESSEL_UID,145,value1,0)) as CMACGMTAGE,sum(decode(VESSEL_UID,147,value1,0)) as CMACGMTHAMES,sum(decode(VESSEL_UID,148,value1,0)) as CMACGMLITANI,sum(decode(VESSEL_UID,149,value1,0)) as CMACGMTIGRIS,sum(decode(VESSEL_UID,150,value1,0)) as CMACGMURAL,sum(decode(VESSEL_UID,152,value1,0)) as CMACGMVOLGA,sum(decode(VESSEL_UID,154,value1,0)) as CMACGMKERGUELEN,sum(decode(VESSEL_UID,155,value1,0)) as CMACGMGEORGFORSTER,sum(decode(VESSEL_UID,158,value1,0)) as CMACGMVASCODEGAMA,sum(decode(VESSEL_UID,167,value1,0)) as CMACGMCAYENNE,sum(decode(VESSEL_UID,168,value1,0)) as CMACGMMARSEILLE FROM

  (SELECT TO_CHAR(modif_date,'YYYYMM') ,

    TO_CHAR(modif_date,'MON-YYYY') label,

    NVL(SUM(QUANTITY_DELIV),0) value1, VESSEL_UID

  FROM "SCE_PROD"."LO_VALUATION"

  GROUP BY TO_CHAR(modif_date,'YYYYMM'),

    TO_CHAR(modif_date,'MON-YYYY'), VESSEL_UID

  ORDER BY TO_CHAR(modif_date,'YYYYMM'),

    TO_CHAR(modif_date,'MON-YYYY'), VESSEL_UID

) GROUP BY label order by label

 

 

How could i avoid error message  ?

Regards

Phil