Home » SQL & PL/SQL » SQL & PL/SQL » Formatting the flat file output using UTL_File (orale 10g)
Formatting the flat file output using UTL_File [message #413130] |
Tue, 14 July 2009 09:17  |
purohg
Messages: 10 Registered: June 2005 Location: Kolkata
|
Junior Member |
|
|
Hi,
I have a requirement to have a fixed length flat file from the table and having some special format like ...
COLUMN ORGU_NUMB format 09999999
COLUMN SALE_DATE format a8
COLUMN SALE_HOUR format 09
I have written the following code, but could not manage to have a leading 0 in the output..
declare
vs_SQL varchar2(440);
vs_SQL1 varchar2(9540);
vt_FileHandle UTL_FILE.file_type;
pis_DirectoryName VARCHAR2(50) := 'DUMP_DATA';
pis_FileName VARCHAR2(50) :='acd.dat';
pis_RecordDelimiter VARCHAR2(1) := CHR(10);
TYPE t_BigStringTab IS TABLE OF VARCHAR2(32000);
vt_TableRowList t_BigStringTab;
BEGIN
vs_SQL := 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYYMMDD''';
EXECUTE IMMEDIATE vs_SQL;
vt_FileHandle := UTL_FILE.fopen(pis_DirectoryName,pis_FileName,'w');
vs_SQL1:= 'SELECT lpad(RPAD(to_char(ORGU_NUMB),7), ||
RPAD(SALE_DATE, ||
lpad(RPAD(SALE_HOUR,2),3) ||
lpad(RPAD(SALE_MINUTE,3),4) ||
lpad(RPAD(BSKT_NUMB,11),12) ||
lpad(RPAD(BITM_NUMB,7), ||
lpad(RPAD(END_YRWK,6),7) ||
lpad(RPAD(END_YEAR,4),5) || lpad(RPAD(WEEK,2),3) ||
case when QTY <0 then rpad(to_char(QTY),6) else '' ''|| rpad(to_char(QTY),5) end ||
case when ITM_PRC_PD <0 then rpad(to_char(ITM_PRC_PD),9) else '' ''|| rpad(to_char(ITM_PRC_PD), end
|| case when WEIGHT <0 then rpad(to_char(WEIGHT),9) else '' ''|| rpad(to_char(WEIGHT), end
|| case when ITM_PREM_PNTS <0 then rpad(to_char(ITM_PREM_PNTS),6) else '' ''|| rpad(to_char(ITM_PREM_PNTS),5) end
|| RPAD(coalesce(MRKDWN_IND,'' ''),1) ||
case when SCANCODE <0 then rpad(to_char(SCANCODE),13) else '' ''|| rpad(to_char(SCANCODE),12) end
|| case when VAT_RATE <0 then rpad(to_char(VAT_RATE),7) else '' ''|| rpad(to_char(VAT_RATE),6) end
|| case when FREE_SALE <0 then rpad(to_char(FREE_SALE),7) else '' ''|| rpad(to_char(FREE_SALE),6) end
|| case when FUNDING <0 then rpad(to_char(FUNDING),7) else '' ''|| rpad(to_char(FUNDING),6) end
|| RPAD(coalesce(PROMO_IND,'' ''),1) ||
RPAD(coalesce(OFFER_TYPE,'' ''),2) ||
case when PROMO_RETRO <0 then rpad(to_char(PROMO_RETRO),16) else '' ''|| rpad(to_char(PROMO_RETRO),15) end
|| RPAD(coalesce(PROMO_LVL_IND,'' ''),4)
|| lpad(RPAD(COST_TYPE,1),2)
|| case when CURRENT_COST <0 then rpad(to_char(CURRENT_COST),16) else '' ''|| rpad(to_char(CURRENT_COST),15) end
|| RPAD(coalesce(SCOLD_IND,'' ''),1) ||
case when SALES_BSD_MRGN <0 then rpad(to_char(SALES_BSD_MRGN),16) else '' ''|| rpad(to_char(SALES_BSD_MRGN),15) end
|| case when SALES_VAL <0 then rpad(to_char(SALES_VAL),16) else '' ''|| rpad(to_char(SALES_VAL),15) end FROM T1BSKTITMSF201002WEK ';
COLUMN ORGU_NUMB format 09999999
COLUMN SALE_DATE format a8
COLUMN SALE_HOUR format 09
COLUMN SALE_MINUTE format 0999
COLUMN BSKT_NUMB format 099999999999
COLUMN BITM_NUMB format 09999999
COLUMN END_YRWK format 099999
COLUMN END_YEAR format 0999
EXECUTE IMMEDIATE vs_SQL1 BULK COLLECT
INTO vt_TableRowList;
FOR i IN 1 .. vt_TableRowList.LAST LOOP
UTL_FILE.put(vt_FileHandle, vt_TableRowList(i) || pis_RecordDelimiter );
UTL_FILE.fflush(vt_FileHandle);
END LOOP;
UTL_FILE.fclose(vt_FileHandle);
END ;
/
Please can you advice me if this is possible to have the output i need. If so, where i am wrong.
Can i achieve the same thing using soem other method.
Thanks a lot in advance for all help.
Regards,
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 02:25:47 CST 2025
|