'Carriage Return' in spool file

[Tse]

Hi,

Whenever I do a select and spool the result to a file in sqlplus on the Unix
server, the length of each row of data is fixed as the 'linesize'. (All rows
of records have a fix length = linesize.)

How do I put a Carriage Return at the end of each line so that there is no
whitespaces after the last field? Which means that now the size of each
record will be different from the others depending on the size of each field
that makes up the row.

The field delimiter is the double quote(") and the field seperator is a
comma(,).

Thanks in advance.

Rgds,
Tse Chong
(Please send reply to mailto:tc...@yas.com.sg )

[Mark G]

If you put

set trimspool on

either in sql*plus or at the start of your script, this will eliminate the
whitespace at the end of the last field for you.

Mark

Tse wrote in message <7lcors$ce3$1...@nobel2.pacific.net.sg>...

[rtpro...@my-deja.com]

Youu can also control the carriage from the SELECT
statement, by using CHR(10). For example, if you were writing
dynamic sql you would write like this:

(This is a poor example because the select is so short,
but just assume you wanted output on two lines:)

Desired result for all tables:
select count(*)
from TABLE;

The Select:
Select 'Select count(*)' || chr(10) || ' from ' ||
TNAME || ';'
from TAB
Where TABTYPE='TABLE';

You might still need trimspool to get rid of whitespace,
but you could control your text with this method.

Robert Proffitt
Beckman Coulter
RTProffi...@Beckman.com

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

[fumi]

Tse <tc...@yas.com.sg> wrote in message news:7lcors$ce3$1...@nobel2.pacific.net.sg...

> Hi,
>
> Whenever I do a select and spool the result to a file in sqlplus on the Unix
> server, the length of each row of data is fixed as the 'linesize'. (All rows
> of records have a fix length = linesize.)
>
> How do I put a Carriage Return at the end of each line so that there is no
> whitespaces after the last field? Which means that now the size of each
> record will be different from the others depending on the size of each field
> that makes up the row.
>
> The field delimiter is the double quote(") and the field seperator is a
> comma(,).

You can use:

set trimspool on