Re: How to turn-off SQL*PLUS character formatting for fixed datastream

A copy of this was sent to Scot Balfour <scot.balfour_at_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 _at_ sign will do it.

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

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

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. Received on Tue May 05 1998 - 00:00:00 CEST