| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> NOT SOLVEDOutput in 1 line in the spool file from a sql statement - please help
The output looks like this if I did what I you ask me to do:-
V$ACCESS SYNONYM 9 PUBLIC DBMS_APPLICATION_INFO PACKAGE 9 SYS DBMS_OUTPUT PACKAGE 9 SYS DBMS_STANDARD PACKAGE 9 SYS GV$ACCESS VIEW 9 SYS STANDARD PACKAGE 9 SYS V$ACCESS VIEW 9 SYS V_$ACCESS VIEW 9 SYS X$KGLDP TABLE 9 SYS X$KGLLK TABLE 9 SYS X$KGLOB TABLE 9 SYS X$KSUSE TABLE 9 SYS V$ACCESS CURSOR 9 SYSTEM But I dont want that, I want say for the first line:- V$ACCESS (white spaces till 254) SYNONYM (till 24 spaces)9 PUBLIC
etc all in 1 line; it should not combine say V$ACCESS AND SYNONYM if V$ACCESS is less than 254, as it happened here.
It is not that simple, then is it??
Rgds,
raja
-- On Wed, 14 Feb 2001 06:37:31 William Rogge wrote:Received on Wed Feb 14 2001 - 20:40:28 CST
>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).
>
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).
 |
 |