OraFAQ Forum: Client Tools » trimspool and linesize query

Home » Other » Client Tools » trimspool and linesize query

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

trimspool and linesize query [message #335259]

Mon, 21 July 2008 11:30

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

Report message to a moderator

Re: trimspool and linesize query [message #335260 is a reply to message #335259]

Mon, 21 July 2008 11:34

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

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

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: trimspool and linesize query [message #335270 is a reply to message #335259]

Mon, 21 July 2008 12:15

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

Report message to a moderator

Re: trimspool and linesize query [message #335275 is a reply to message #335270]

Mon, 21 July 2008 12:25

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

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

Report message to a moderator

Re: trimspool and linesize query [message #335368 is a reply to message #335275]

Tue, 22 July 2008 00:15

Barbara Boehmer
Messages: 9101
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."

Report message to a moderator

Re: trimspool and linesize query [message #335393 is a reply to message #335368]

Tue, 22 July 2008 01:05

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

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

Report message to a moderator

Re: trimspool and linesize query [message #335549 is a reply to message #335393]

Tue, 22 July 2008 09:44

kknegi
Messages: 5
Registered: July 2008
Location: USA

Junior Member

is there any way we can set a linesize with a variable value?

Report message to a moderator

Re: trimspool and linesize query [message #335553 is a reply to message #335549]

Tue, 22 July 2008 10:10

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

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

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

Report message to a moderator

Re: trimspool and linesize query [message #335562 is a reply to message #335270]

Tue, 22 July 2008 12:36

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: spooling variable length file [message #335569 is a reply to message #335561]

Tue, 22 July 2008 13:27

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

Senior Member
Account Moderator

Why do you repeat your question?

Regards
Michel

Report message to a moderator

Re: spooling variable length file [message #335571 is a reply to message #335569]

Tue, 22 July 2008 13:35

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.

Report message to a moderator

Re: spooling variable length file [message #335572 is a reply to message #335571]

Tue, 22 July 2008 13:42

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

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

Report message to a moderator

Previous Topic:	Sql Developer have problem
Next Topic:	Spooling problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 15:33:37 CST 2024