RE: Output in 1 line in the spool file from a sql statement

From: William Rogge <William_Rogge_at_voltek.com>
Date: Wed, 14 Feb 2001 07:30:36 -0800
Message-ID: <F001.002B3DF9.20010214063731@fatcity.com>

Piece of cake:
run_sql()
{

sqlplus -s system/manager_at_orcl815 <<!
set echo off pagesize 0 linesize 353
col a format a352
spool 4.lst

select  substr(object,1,254)||' '||
        substr(type,1,24)||' '||
        substr(sid,1,7)||' '||
        substr(owner,1,64) a

from v\$access
order by owner
/
spool off
!
cat 4.lst |sed 's/ *$//' > 5.lst
}

run_sql

Simply concatenate the strings you want onto one line and you are done. Naturally, as the substr get larger/shorter, your linesixe should change.

-----Original Message-----
From: Viraj Luthra [SMTP:viraj999_at_lycos.com] Sent: Wednesday, February 14, 2001 12:45 AM

To:     Multiple recipients of list ORACLE-L
Subject:        Output in 1 line in the spool file  from a sql statement - please

help

Hello,

I need some help. I need all the columns values on 1 line only. that is in the end of this mail you will see the result as,

V$ACCESS

SYNONYM                  8

PUBLIC

I need the above as

V$Access                  Synonym          8         public

all with the correct width as defined in the sql statement. Have you any clue as to what to do?

I have tried different linesizes... also but no use Regards,

Raja

This is the script that I run:-

run_sql()
{

sqlplus -s system/manager_at_orcl815 <<!
set linesize 78 (also tried linesize 1000 but no use) col a format a254
col b format a24
col c format 9999999
col d format a64
set echo off feedback off head off
spool 4.lst

select  substr(object,1,254) a,
        substr(type,1,24) b,
        substr(sid,1,7) c,
        substr(owner,1,64) d

from v\$access
order by owner
/
spool off
!
cat 4.lst |sed 's/ *$//' > 5.lst
}

run_sql

+++++++++++++++++++++++++++++++++++++++++++++++++++

The output I get is like this:-

V$ACCESS

SYNONYM                  8

PUBLIC DBMS_APPLICATION_INFO

PACKAGE                  8

SYS Get your small business started at Lycos Small Business at http://www.lycos.com/business/mail.html

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Viraj Luthra
  INET: viraj999_at_lycos.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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: William Rogge
  INET: William_Rogge_at_voltek.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Wed Feb 14 2001 - 09:30:36 CST

RE: Output in 1 line in the spool file from a sql statement - please help