Instead of using colsep, try this:
select ename||'|'||job
from emp;
I was going to suggest -> col emp form a4
but if it can be more than 4 positions, it will wrap.
Jay
>>> [EMAIL PROTECTED] 09/19/02 11:53AM >>>
I don't want just 4. It's variable length and I want the actual number of valid bytes.
Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]
-----Original Message-----
Sent: Thursday, September 19, 2002 9:51 AM
To: '[EMAIL PROTECTED]'; Carle, William T (Bill), ALCAS
Subject: RE: sqlplus question
If you want only 4 bytes, us the SUBSTR function to take only what you need.
SQL> select ename, job
2 from emp;
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
SQL> set colsep '|'
SQL> /
SMITH |CLERK
ALLEN |SALESMAN
WARD |SALESMAN
JONES |MANAGER
SQL> select substr(ename,1,4), job
2 from emp;
SMIT|CLERK
ALLE|SALESMAN
WARD|SALESMAN
JONE|MANAGER
-----Original Message-----
Sent: Thursday, September 19, 2002 9:19 AM
To: Multiple recipients of list ORACLE-L
Howdy,
I am spooling my sqlplus output to a file with no headings and all the
fields separated by a delimiter. I have a field that is defined as
varchar2(56), but typically only 4 or 5 bytes are filled. Oracle recognizes
that and if you select length(fld1) from the table, you will get 4. But if I
spool this to a file, I always get the full 56 bytes padded with blanks. In
other words, I get 4 bytes of data and 52 blanks for that field. I only want
the four valid bytes so that my delimiter comes immediately after that 4th
byte. My sqlplus options are as follows:
set newpage 0 space 0 linesize 5000 pagesize 0 echo off recsep off feedback
off
heading off trimspool on colsep "|"
Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
D&E except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
