Home » SQL & PL/SQL » SQL & PL/SQL » Unload data to flat file, fixed length
icon5.gif  Unload data to flat file, fixed length [message #270126] Tue, 25 September 2007 17:08 Go to next message
kham2k
Messages: 34
Registered: May 2007
Member
Greetings all!!

I want to ask you is if i want to unload a table to a flat we use spool. but with spool, i know how to delimit it with , or | but how can i generate a file which will be fixed length?

Thanks Allot
icon5.gif  Re: Unload data to flat file, fixed length [message #270129 is a reply to message #270126] Tue, 25 September 2007 17:25 Go to previous messageGo to next message
kham2k
Messages: 34
Registered: May 2007
Member
I tried this one;
 select  
rpad(CLAIM_ID, 6)||'|'||
rpad(claim_nbr,6)||'|'||
rpad(CLAIM_DESC,40)||'|'
from claim 
where create_ts > sysdate-1;


But im not getting the wot is need.
1159  |85078 |Alka & Phillips                         |
1160  |85079 |Balance Bar                             |
1161  |85080 |Met-Rx Protein Plus Protein Bars        |
1162  |85081 |Centrum 180s, Centrum Silver 150s       |
1163  |85082 |Aleve Caplets/Tablets 24ct              |
1164  |85083 |Gerber Boost Nutritional Drink          |
1165  |85084 |SERENITY                                |
1166  |85085 ||



note 116 where CLAIM_DESC is NULL. im getting no spaces at all. How can i get the spaces?

Thanks
icon14.gif  Re: Unload data to flat file, fixed length [message #270134 is a reply to message #270126] Tue, 25 September 2007 18:55 Go to previous messageGo to next message
kham2k
Messages: 34
Registered: May 2007
Member
I found out how to take care of it. If any got a better idea please post

select  rpad(CLAIM_ID,6)||'|'||
rpad(claim_nbr,6)||'|'||
rpad(nvl(CLAIM_DESC,' '),40,' ')||'|'
from claim
where create_ts > sysdate-1


I used NVL.

1159  |85078 |Alka & Phillips                         |
1160  |85079 |Balance Bar                             |
1161  |85080 |Met-Rx Protein Plus Protein Bars        |
1162  |85081 |Centrum 180s, Centrum Silver 150s       |
1163  |85082 |Aleve Caplets/Tablets 24ct              |
1164  |85083 |Gerber Boost Nutritional Drink          |
1165  |85084 |SERENITY                                |
1166  |85085 |                                        |
Re: Unload data to flat file, fixed length [message #270135 is a reply to message #270126] Tue, 25 September 2007 18:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Congratulations on a fine solution!
icon5.gif  Re: Unload data to flat file, fixed length [message #270389 is a reply to message #270126] Wed, 26 September 2007 14:03 Go to previous messageGo to next message
kham2k
Messages: 34
Registered: May 2007
Member
I'll like to extend my question here. What I want now is there i want to spool records from two tables, master and detail, and the file layout should be in following way;

1159  |85078 |Alka & Phillips                         |
                            1159  |033200011101  |1| .58|CA|USA|
                            1159  |033200095101	 |1|4.45|CA|USA| 
1160  |85079 |Balance Bar                             |
                            1160  |722252101303	 |1| .83|NV|USA
1161  |85080 |Met-Rx Protein Plus Protein Bars        |
                            1161  |346581851069	 |1|3.6|TX|USA|
1162  |85081 |Centrum 180s, Centrum Silver 150s       |
1163  |85082 |Aleve Caplets/Tablets 24ct              |
                            1163  |070011115840	 |1|3.12|NY|USA|
1164  |85083 |Gerber Boost Nutritional Drink          |
1165  |85084 |SERENITY                                |
                            1165  |346581700015	 |1| .83|AL|USA|
1166  |85085 |                                        |
                            1166  |346581100402	 |1|2.40|MN|USA|


How can I get the first line to be from the master table and then move to 2nd line and leave some spaces and then put all the records from detail table for that particular record.

Thanks allot for ur help in advance
Re: Unload data to flat file, fixed length [message #270397 is a reply to message #270389] Wed, 26 September 2007 14:52 Go to previous messageGo to next message
diku
Messages: 23
Registered: April 2007
Junior Member
Declare two cursors , one for your master and another for your detail recordset. Loop through your detail cursor everytime you find a new master record from your master cursor.Obviously you'd need to have some sort of a relation between these two which I presume you'd have.

Now that the records are being pulled it's just a question of fomatting them properly. Play around with RPAD and LPAD and it should not be too difficult
Re: Unload data to flat file, fixed length [message #270403 is a reply to message #270389] Wed, 26 September 2007 15:17 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a beginning of a way:
SQL> col filler fold_before
SQL> select decode(nvl(rn,1),1,d.deptno) deptno,
  2         decode(nvl(rn,1),1,d.dname) dname, 
  3         '      ' filler,
  4         e.empno, e.ename
  5  from dept d,
  6       (select empno, ename, deptno,
  7               row_number () over (partition by deptno order by empno) rn
  8        from emp) e
  9  where e.deptno (+) = d.deptno
 10  order by d.deptno, e.empno
 11  /
    DEPTNO DNAME
---------- --------------
FILLER      EMPNO ENAME
------ ---------- ----------
        10 ACCOUNTING
             7782 CLARK

             7839 KING

             7934 MILLER
        20 RESEARCH
             7369 SMITH

             7566 JONES

             7788 SCOTT

             7876 ADAMS

             7902 FORD
        30 SALES
             7499 ALLEN

             7521 WARD

             7654 MARTIN

             7698 BLAKE

             7844 TURNER

             7900 JAMES
        40 OPERATIONS


15 rows selected.

Regards
Michel
Previous Topic: Assigning of %rowtype variable from different table
Next Topic: Online books
Goto Forum:
  


Current Time: Sat Dec 10 05:24:46 CST 2016

Total time taken to generate the page: 0.05666 seconds