From: tkyte@us.oracle.com (Thomas Kyte)
Subject: Re: How to turn-off SQL*PLUS character formatting for fixed datastream
Date: 1998/05/05
Message-ID: <354f8506.30431978@192.86.155.100>#1/1
Content-Transfer-Encoding: 7bit
References: <354F6A94.54CB2BA1@lmco.com>
Content-Type: text/plain; charset=us-ascii
Organization: Oracle Government
Mime-Version: 1.0
Reply-To: tkyte@us.oracle.com
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.tools



A copy of this was sent to Scot Balfour <scot.balfour@lmco.com>
(if that email address didn't require changing)
On Tue, 05 May 1998 15:37:57 -0400, you wrote:

>My customer has asked me to generate a datastream in a format that looks
>like this:
>
>123-45-6789,Balfour,Scot,X,Gaithersburg
>
>I have a query that selects the appropriate information and concatenates
>the comma delimiter between fields; however, SQL*PLUS is padding each
>field value with trailing blanks up to the length of the varchar2 field
>(defined at table create time). So my output looks like this:
>
>123-45-6789,Balfour                  ,Scot
>,X,Gaithersburg
>
>The data is stored in Oracle without leading or trailing spaces so the
>"LTRIM" and "RTRIM" functions do not help.  Can SQL*PLUS be made to
>simply print the field values without padding out to the length of the
>field, or do I have to write something more sophisticated using PL/SQL?
>

select column1 || ',' || column2 || ',' || column3 || ',' || column4
from T
/

will do it.  In 7.2 on up, you can "set trimspool on" to remove the trailing
blanks from each line as well, so something like:

set feedback off
set heading off
set termout off
set trimspool on
set linesize 1024
spool test.dat
select empno || ',' || ename || ',' || deptno
from emp
/
spool off


in a sqlplus script that you run with the @ sign will do it.



>Any query/code fragments would be appreciated. This seems so
>simple...Thanks.
>scot.balfour@lmco.com

 
Thomas Kyte
tkyte@us.oracle.com
Oracle Government
Herndon VA
 
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.