RE: sqlplus question

[Naveen Nahata]

Title: RE: sqlplus question

SQLPlus outputs data in columns based on their Maximum length or the COLUMN format set in SQLPlus. Value of SQLPlus COLUMN attribute takes precedence.

 

Even when you do a substr() based on length, SQLPlus doesn't calculate the length of the maximum length value and format columns accordingly. It still looks at the column width until or unless u substring to a constant value like substr(column_name, 1, 10)

 

The best way is to select the columns themselves concatenated with the column separator e.g SELECT col_1 || ',' || col_2.... etc.

 

If any of the columns is a CHAR column then you'll have to use trim() or ltrim(rtrim()) depending upon the oracle version

 

regards

naveen

 

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 20, 2002 4:24 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: sqlplus question

Use rtrim and ltrim are you sure data was loaded with spaces? 

-----Original Message-----
From: Yechiel Adar [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 2:33 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: sqlplus question

Tried this.  The field length is still 56.

Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Thursday, September 19, 2002 6:43 PM

> select substr(ename,1,length(ename)), job from emp;
>
> >From: "Bob Metelsky" <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> >Subject: RE: sqlplus question
> >Date: Thu, 19 Sep 2002 08:18:51 -0800
> >
> >
> > >
> > >     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 "|"
> >
> >Sounds to me like the trimspool should do it...
> >
> >Are you sure its set on
> >
> >ME@DB1 -> trimspool on
> >SP2-0734: unknown command beginning "trimspool ..." - rest of line
> >ignored.
> >
> >Try  set trims on
> >
> >Or
> >
> >Set trimspool on
> >
> >
> >bob
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Bob Metelsky
> >   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).
>
>
>
>
> _________________________________________________________________
> MSN Photos is the easiest way to share and print your photos:
> http://photos.msn.com/support/worldwide.aspx
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Viral Desai
>   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).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Yechiel Adar
  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).