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 Go to next message
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),Cool ||
RPAD(SALE_DATE,Cool ||
lpad(RPAD(SALE_HOUR,2),3) ||
lpad(RPAD(SALE_MINUTE,3),4) ||
lpad(RPAD(BSKT_NUMB,11),12) ||
lpad(RPAD(BITM_NUMB,7),Cool ||
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),Cool end
|| case when WEIGHT <0 then rpad(to_char(WEIGHT),9) else '' ''|| rpad(to_char(WEIGHT),Cool 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,


Re: Formatting the flat file output using UTL_File [message #413133 is a reply to message #413130] Tue, 14 July 2009 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Please can you advice me if this is possible to have the output i need.
Yes, it is possible.

> If so, where i am wrong.
PL/SQL is different from SQL*Plus
What is valid in one is not necessarily valid in the other.
For PL/SQL & NUMBER leftmost zeros are suppressed.
You need to convert NUMBER to string datatype.
Re: Formatting the flat file output using UTL_File [message #413164 is a reply to message #413130] Tue, 14 July 2009 11:52 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: How to get the name of the column of the table that caused exception while loading data
Next Topic: Getting the list of columns of a VIEW from data dictionary in pl sql
Goto Forum:
  


Current Time: Thu Dec 08 04:12:38 CST 2016

Total time taken to generate the page: 0.05526 seconds