OraFAQ Forum: SQL & PL/SQL » Unload data to flat file, fixed length

Home » SQL & PL/SQL » SQL & PL/SQL » Unload data to flat file, fixed length

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Unload data to flat file, fixed length [message #270126]

Tue, 25 September 2007 17:08

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

Report message to a moderator

Re: Unload data to flat file, fixed length [message #270129 is a reply to message #270126]

Tue, 25 September 2007 17:25

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

Report message to a moderator

Re: Unload data to flat file, fixed length [message #270134 is a reply to message #270126]

Tue, 25 September 2007 18:55

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 |                                        |

Report message to a moderator

Re: Unload data to flat file, fixed length [message #270135 is a reply to message #270126]

Tue, 25 September 2007 18:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Congratulations on a fine solution!

Report message to a moderator

Re: Unload data to flat file, fixed length [message #270389 is a reply to message #270126]

Wed, 26 September 2007 14:03

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

Report message to a moderator

Re: Unload data to flat file, fixed length [message #270397 is a reply to message #270389]

Wed, 26 September 2007 14:52

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

Report message to a moderator

Re: Unload data to flat file, fixed length [message #270403 is a reply to message #270389]

Wed, 26 September 2007 15:17

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Assigning of %rowtype variable from different table
Next Topic:	Online books

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 08 03:08:18 CDT 2025