RE: sqlplus question

From: Jay Hostetter <jhostetter_at_decommunications.com>
Date: Thu, 19 Sep 2002 08:33:38 -0800
Message-ID: <F001.004D3F42.20020919083338@fatcity.com>

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

>>> wcarle_at_att.com 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
wcarle_at_att.com

-----Original Message-----

Sent:	Thursday, September 19, 2002 9:51 AM
To:	'ORACLE-L_at_fatcity.com'; 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
wcarle_at_att.com

**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: jhostetter_at_decommunications.com

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: ListGuru_at_fatcity.com (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).

Received on Thu Sep 19 2002 - 11:33:38 CDT