Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: NOT SOLVEDOutput in 1 line in the spool file from a sql stat

RE: NOT SOLVEDOutput in 1 line in the spool file from a sql stat

From: Dasko, Dan <Dan.Dasko_at_cdicorp.com>
Date: Thu, 15 Feb 2001 08:57:10 -0800
Message-ID: <F001.002B52A8.20010215064125@fatcity.com>

use
select

        lpad(object,254)||lpad(type,24)||lpad(sid,7)||lpad(owner,64) from

        blah blah

etc.

Dan

-----Original Message-----
Sent: Wednesday, February 14, 2001 8:25 PM To: Multiple recipients of list ORACLE-L 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:

>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). _____________________________________________________________________ This message has been checked for all known viruses by UUNET delivered through the MessageLabs Virus Control Centre. For further information visit http://www.uk.uu.net/products/security/virus/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dasko, Dan INET: Dan.Dasko_at_cdicorp.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 Thu Feb 15 2001 - 10:57:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US