Piece of cake:
run_sql()
{
sqlplus -s system/manager@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:[EMAIL PROTECTED]]
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@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: [EMAIL PROTECTED]
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: [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: William Rogge
INET: [EMAIL PROTECTED]
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: [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).
