Home » SQL & PL/SQL » SQL & PL/SQL » generate fixed format file
generate fixed format file [message #319055] Thu, 08 May 2008 18:13 Go to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member
I need to create fixed format file using sqlplus. The file is about 200 fields long and containing some columns filled with blank spaces. I want to generate a flat file with the fixed format and filled those columns with blank spaces if it contains blank spaces in the columns of the table.
I have used this approach to create flat file but not able to filled the empty columns with desired blank spaces in it.

spool C:\test.txt

select col1||col2 from tab;


Really appreciate you r valuable time in advance.
Re: generate fixed format file [message #319056 is a reply to message #319055] Thu, 08 May 2008 18:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>filled those columns with blank spaces if it contains blank spaces in the columns of the table.
HUH?

RPAD,LPAD,NVL?
Re: generate fixed format file [message #319058 is a reply to message #319056] Thu, 08 May 2008 18:59 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member
I appreciate your time for having look on my problem. The problem i am getting here now, getting blank spaces at the end of each rows in the output file. I have used this option trim on, but no luck.

Thanks again

[Updated on: Thu, 08 May 2008 19:00]

Report message to a moderator

Re: generate fixed format file [message #319059 is a reply to message #319055] Thu, 08 May 2008 19:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
TRIMSPOOL ON
Re: generate fixed format file [message #319060 is a reply to message #319058] Thu, 08 May 2008 19:05 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member
THanks i have tried trimspool on.. it works

Much much appreciated

[Updated on: Thu, 08 May 2008 19:06]

Report message to a moderator

Re: generate fixed format file [message #319061 is a reply to message #319059] Thu, 08 May 2008 19:23 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member
I have last column with blank spaces, this option (trimspool on) also trims the last column which contain blank spaces.

Thanks for you time again and again..
Re: generate fixed format file [message #319065 is a reply to message #319061] Thu, 08 May 2008 20:06 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member
HOw can we fill the blank spaces for the last column with the use of trimspool on option. i have used pad function but no luck.
Re: generate fixed format file [message #319072 is a reply to message #319065] Thu, 08 May 2008 23:32 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
set linesize to the correct value and set trimspool off
Previous Topic: Option to keep running a PL/SQL package automatically
Next Topic: Converting Minutes to Quarter Hours
Goto Forum:
  


Current Time: Mon Feb 17 03:53:41 CST 2025