RE: sqlplus question

From: Carle, William T (Bill), ALCAS <wcarle_at_att.com>
Date: Thu, 19 Sep 2002 07:53:35 -0800
Message-ID: <F001.004D3D23.20020919075335@fatcity.com>

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Carle, William T (Bill), ALCAS
INET: wcarle_at_att.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).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Carle, William T (Bill), ALCAS
INET: wcarle_at_att.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------