Re: formatting output with SQL*Plus

In message <31oqp1F3d2fp2U1_at_individual.net>, Thomas Kellerer <NNGNVRDSJEBN_at_spammotel.com> writes
>Hello,
>
>I'm hitting the old problem that I need to spool data into a text file, but
>SQL*Plus insists on padding the fields to the length of the column definition.
>
>The problem is that I cannot concatenate the columns as I get an "ORA-01489:
>result of string concatenation is too long" when doing so.
>
>What I want is a file that has the following format
>
>value1|value2|value3
>
>but what I get is:
>
>value1 |value2 |value3
>
>The columns are all VARCHAR2(2000)
>
>What I'm basically doing is:
>
>set term off
>set echo off
>set feedback off
>set heading off
>set define off
>set timing off
>set linesize 32767
>set pagesize 0
>set colsep '|'
>set newpage none
>set trimspool on
>
>spool output.txt
>
>SELECT column1,column2, column3
>FROM my_table;
>
>spool off
>
>Is there any way to remove the trailing spaces in the column values?
>
>I'm using Oracle 8.1.7.4.0 on HP/UX
>

If you have to use SQL*Plus the only option is to edit the file afterwards using sed or similar.

You can do it as part of the same shell script. Something like this

FILENAME=foo

sqlplus <u/p> @${filename} ${filename}.tmp

sed - 's/ *|/|/g' <${filename}.tmp >${filename}.lst

foo.sql contains spool &1

-- 
Jim Smith
Because of their persistent net abuse, I ignore mail from
these domains (among others) .yahoo.com .hotmail.com .kr .cn .tw
For an explanation see <http://www.jimsmith.demon.co.uk/spam>