Home » SQL & PL/SQL » Client Tools » trimspool and linesize query
trimspool and linesize query [message #335259] Mon, 21 July 2008 11:30 Go to next message
kknegi
Messages: 5
Registered: July 2008
Location: USA
Junior Member

Hi All,

I am facing difficulty while spooling records into a flat file.
The Records from the columns are of variable length.
For E.g. first line is of 100 bytes. second is of 120, third is of 200 bytes with 50 spaces in it.

I need to spool the file exactly the way its in the table. I don't want to trim the spaces. BUt as the record length is more than 80 bytes the output doesnt come formatted. if i give a fixed linesize the output comes in a fixed format.

output structure

line 1: "aaaaaaaaaaaaaaaaa aaaaaaa"
line 2: "aaaaaaaaaaaaaaaaaaa "
line 3: "aaa aaaaa aaaaaa "

SO i need the output (without commas)the way it is without trimming the spaces in the records.

Can anyone help me in this regard.

Thanks in advance
Re: trimspool and linesize query [message #335260 is a reply to message #335259] Mon, 21 July 2008 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unfortunatly I don't think you can do it with just SQL*Plus.
A trick could be to replace the space by a not used character, trim the output and then in another step, like sed, replace the replacement character by a space.

Regards
Michel

[Updated on: Mon, 21 July 2008 11:35]

Report message to a moderator

Re: trimspool and linesize query [message #335261 is a reply to message #335259] Mon, 21 July 2008 11:34 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: trimspool and linesize query [message #335270 is a reply to message #335259] Mon, 21 July 2008 12:15 Go to previous messageGo to next message
kknegi
Messages: 5
Registered: July 2008
Location: USA
Junior Member

but the problem is that we are spooling on windows sisde and not using UNix environment. So there is no replace string command associated with MS-DOS
Re: trimspool and linesize query [message #335275 is a reply to message #335270] Mon, 21 July 2008 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you search on the Web I'm pretty sure you will find something like that on Windows.
Otherwise you can write it in C or Perl or your favourite language it is not hard to do if your purpose is just to replace a character by another one.

Regards
Michel
Re: trimspool and linesize query [message #335368 is a reply to message #335275] Tue, 22 July 2008 00:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
set trimspool off

The following is an excerpt from the SQL*Plus User's Guide:

"SET TRIMS[POOL] {ON | OFF}

Determines whether SQL*Plus puts trailing blanks at the end of each spooled line. ON removes blanks at the end of each line. OFF enables SQL*Plus to include trailing blanks. TRIMSPOOL ON does not affect terminal output."
Re: trimspool and linesize query [message #335393 is a reply to message #335368] Tue, 22 July 2008 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Barbara, the problem is that it also trim the value from the column and OP does not want this.

Regards
Michel
Re: trimspool and linesize query [message #335549 is a reply to message #335393] Tue, 22 July 2008 09:44 Go to previous messageGo to next message
kknegi
Messages: 5
Registered: July 2008
Location: USA
Junior Member

is there any way we can set a linesize with a variable value?
Re: trimspool and linesize query [message #335553 is a reply to message #335549] Tue, 22 July 2008 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No. More what you ask is meaningless.

Regards
Michel

[Updated on: Tue, 22 July 2008 10:11]

Report message to a moderator

spooling variable length file [message #335561 is a reply to message #335259] Tue, 22 July 2008 12:07 Go to previous messageGo to next message
kknegi
Messages: 5
Registered: July 2008
Location: USA
Junior Member

Hi All,

I am facing difficulty while spooling records into a flat file.
The Records from the columns are of variable length.
For E.g. first line is of 100 bytes. second is of 120, third is of 200 bytes with 50 spaces in it.

I need to spool the file exactly the way its in the table. I don't want to trim the spaces. But as the record length is more than 80 bytes the output doesnt come formatted. if i give a fixed linesize the output comes in a fixed format.

output structure

line 1: "aaaaaaaaaaaaaaaaa aaaaaaa"
line 2: "aaaaaaaaaaaaaaaaaaa "
line 3: "aaa aaaaa aaaaaa "

SO i need the output (without commas)the way it is without trimming the spaces in the records.

Can anyone help me in this regard.

Thanks in advance
Re: trimspool and linesize query [message #335562 is a reply to message #335270] Tue, 22 July 2008 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
kknegi wrote on Mon, 21 July 2008 10:15
but the problem is that we are spooling on windows sisde and not using UNix environment. So there is no replace string command associated with MS-DOS



http://www.cygwin.com
Re: spooling variable length file [message #335569 is a reply to message #335561] Tue, 22 July 2008 13:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you repeat your question?

Regards
Michel
Re: spooling variable length file [message #335571 is a reply to message #335569] Tue, 22 July 2008 13:35 Go to previous messageGo to next message
kknegi
Messages: 5
Registered: July 2008
Location: USA
Junior Member

wasn't knowing that you can post twice. sorry for reposting but i didnt get proper answer till now.
Re: spooling variable length file [message #335572 is a reply to message #335571] Tue, 22 July 2008 13:42 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You had but you don't want to hear them.
Even if you don't like it the answer is NO.
Use another tool than SQL*Plus or do it as we said.

Regards
Michel
Previous Topic: Sql Developer have problem
Next Topic: Spooling problem
Goto Forum:
  


Current Time: Mon Dec 05 12:49:35 CST 2016

Total time taken to generate the page: 0.07577 seconds