RE: Spool to Excel File
Or use Data|Import Data|New Database Query to import via ODBC. This is especially useful for Pivot Tables etc since the dataset (but not the display set) can be larger than the number of rows in an Excel sheet. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mark Richard Sent: 20 January 2004 05:14 To: Multiple recipients of list ORACLE-L Subject: Re: Spool to Excel File Hi, You won't be able to write an Excel format directly but you can create a .csv file, which Excel will happily read in - you'll just have no real formatting options. Look into some of the sql*plus commands like set heading off, set verify off, set feedback off, set pages 0, set lines 1000, set trimspool on. Of course you might want different settings to the ones I proposed but you will find these commands useful to get rid of stuff you don't (or perhaps do) want in the file. If you really want to generate a legitimate Excel file then start searching the web for programs to do this. I have seen one or two that do this reasonably well but cannot remember names. Regards, Mark. Mudhalvan, Moovarkku To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: apan.co.jp Subject: Spool to Excel File Sent by: [EMAIL PROTECTED] .com 20/01/2004 15:44 Please respond to ORACLE-L Dear Friends, I am trying to send output from SQLPlus to Excel file. If any one did the same before please let me know. Thank You Mudhalvan M.M -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mudhalvan, Moovarkku 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). Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net
RE: Spool to Excel File
Mudhalvan,
I generate files that excel can open all the time. they are not actual
real excel files, but Excel can deal with them quite easily.
Here is a tablespace report I run every week. Note the use of the CHR(9)'s.
This is a TAB character. This forces each column into a new cell in the
spreadsheet. CHR(10) is a line-feed.
Feel free to borrow all of this!
Hope this helps!
SET serveroutput ON
SET feedback OFF
SET lines 150
SET pages 100
SET trimspool ON
exec dbms_output.enable(10)
spool tbslspace_rpt.xls
DECLARE
CURSOR UpTime IS
SELECT INITCAP(instance_name) Instance_Name ,INITCAP(Host_Name) Host_Name
,Version,
ROUND(SYSDATE+1-startup_time) || DECODE(ROUND(SYSDATE+1-startup_time),1,'
Day ', ' Days ') ||
MOD(ROUND((SYSDATE+1 - startup_time) * 24),24) || ' Hours ' ||
MOD(ROUND((SYSDATE+1 - startup_time) * 24*60),60) || ' Minutes ' UpTime
FROM v$instance;
UpTime_Rec UpTime%ROWTYPE;
CURSOR TblSpace IS
SELECT d.status , d.tablespace_name ,
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'999,990') Tbs_Size,
TRUNC(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024)Used,
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00')
Used_Pct ,
DECODE(SIGN(80 - NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100,
0)),-1,'** Warning 80% **',NULL) Msg
FROM sys.DBA_TABLESPACES d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM DBA_DATA_FILES GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM DBA_FREE_SPACE GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
ORDER BY 2;
TblSpace_Rec TblSpace%ROWTYPE;
c_email_list VARCHAR2(300);
mail_message VARCHAR2(32000);
mail_message1 VARCHAR2(32000);
loc_start_time DATE;
TblSpace_Msg NUMBER := 0;
BEGIN
BEGIN
SELECT email_notify_txt
INTO c_email_list
FROM WTW_JOB_NOTIFY
WHERE job_name = UPPER('Wtw_Report_Tablespaces');
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
loc_start_time := SYSDATE;
OPEN UpTime;
FETCH UpTime INTO UpTime_Rec;
CLOSE UpTime;
dbms_output.put_line(CHR(9) || CHR(9) ||
UpTime_Rec.Instance_Name || INITCAP('
Uptime/TABLESPACE Report FOR ') ||
TO_CHAR(SYSDATE,'fmMonth ddth, '));
dbms_output.put_Line(CHR(9) || CHR(9) ||
'UpTime : ' || UpTime_Rec.UpTime);
dbms_output.put_Line(
'Status' || CHR(9) ||
INITCAP('TABLESPACE Name') || CHR(9) ||
INITCAP('SIZE (M)') || CHR(9) ||
'Used (M)' || CHR(9) ||
'Used (Pct)' || CHR(9) ||
'Message');
OPEN TblSpace;
FETCH TblSpace INTO TblSpace_Rec;
WHILE TblSpace%FOUND LOOP
dbms_output.put_Line(
TblSpace_Rec.Status || CHR(9) ||
TblSpace_Rec.Tablespace_Name || CHR(9) ||
TblSpace_Rec.Tbs_Size|| CHR(9) ||
TblSpace_Rec.Used|| CHR(9) ||
TblSpace_Rec.Used_Pct|| CHR(9) ||
TblSpace_Rec.Msg);
IF TblSpace_Rec.Msg IS NOT NULL THEN
TblSpace_Msg := 1;
END IF;
FETCH TblSpace INTO TblSpace_Rec;
EXIT WHEN TblSpace%NOTFOUND;
END LOOP;
CLOSE TblSPace;
END;
/
spool OFF
exit
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Monday, January 19, 2004 11:44 PM
To: Multiple recipients of list ORACLE-L
Dear Friends,
I am trying to send output from SQLPlus to Excel file. If any
one did the same before please let me know.
Thank You
Mudhalvan M.M
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mudhalvan, Moovarkku
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.net
--
Author: Mercadante, Thomas F
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
Re: Spool to Excel File
If you're keen on Perl, the Spreadsheet-WriteExcel module is very handy: http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-0.42/ With that you could slurp data out via DBI, and then build a customized spreadsheet based on the data. But I'd agree with what others have said. Dumping to csv, or some other delimited format and then importing into Excel would probably be the easiest way to go. -- Dan On Mon, 19 Jan 2004, Mudhalvan, Moovarkku wrote: Dear Friends, I am trying to send output from SQLPlus to Excel file. If any one did the same before please let me know. Thank You Mudhalvan M.M Daniel Hanks - Systems/Database Administrator About Inc., Web Services Division -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Hanks 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).
Re: Spool to Excel File
I've never used it before, but there is a piece to Excel called Microsoft query that allows you to query the database directly. Check help in Excel, search for query - ways to retreive data from external sources. HTH, Mike Daniel Hanks [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] c.com cc: Sent by: Subject: Re: Spool to Excel File [EMAIL PROTECTED] .com 01/20/2004 01:04 PM Please respond to ORACLE-L If you're keen on Perl, the Spreadsheet-WriteExcel module is very handy: http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-0.42/ With that you could slurp data out via DBI, and then build a customized spreadsheet based on the data. But I'd agree with what others have said. Dumping to csv, or some other delimited format and then importing into Excel would probably be the easiest way to go. -- Dan On Mon, 19 Jan 2004, Mudhalvan, Moovarkku wrote: Dear Friends, I am trying to send output from SQLPlus to Excel file. If any one did the same before please let me know. Thank You Mudhalvan M.M Daniel Hanks - Systems/Database Administrator About Inc., Web Services Division -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Hanks 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.net -- Author: Michael Boligan 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).
RE: Spool to Excel File
I have used Microsoft Query in Excel. The syntax is a little tricky but it works pretty good. -Original Message- Sent: Tuesday, January 20, 2004 12:50 PM To: Multiple recipients of list ORACLE-L I've never used it before, but there is a piece to Excel called Microsoft query that allows you to query the database directly. Check help in Excel, search for query - ways to retreive data from external sources. HTH, Mike Daniel Hanks [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] c.com cc: Sent by: Subject: Re: Spool to Excel File [EMAIL PROTECTED] .com 01/20/2004 01:04 PM Please respond to ORACLE-L If you're keen on Perl, the Spreadsheet-WriteExcel module is very handy: http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-0.42/ With that you could slurp data out via DBI, and then build a customized spreadsheet based on the data. But I'd agree with what others have said. Dumping to csv, or some other delimited format and then importing into Excel would probably be the easiest way to go. -- Dan On Mon, 19 Jan 2004, Mudhalvan, Moovarkku wrote: Dear Friends, I am trying to send output from SQLPlus to Excel file. If any one did the same before please let me know. Thank You Mudhalvan M.M Daniel Hanks - Systems/Database Administrator About Inc., Web Services Division -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Hanks 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.net -- Author: Michael Boligan 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.net -- Author: Burton, Laura 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).
Re: Spool to Excel File
Strange, no one has mentioned OWA_SYLK. Do a search on SYLK at asktom.oracle.com There are 2 versions, one for web output and one for excel output. SYLK allows cell references, etc, if needed, which you won't get with CSV. Jared Mudhalvan, Moovarkku [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/19/2004 08:44 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Spool to Excel File Dear Friends, I am trying to send output from SQLPlus to Excel file. If any one did the same before please let me know. Thank You Mudhalvan M.M -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mudhalvan, Moovarkku 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).
RE: Spool to Excel File
"SYLK allows cell references, etc, if needed, which you won't get with CSV. " Ahh. but you can with my method! If you use tab separated columns, you can also generate formula's that look like text, but work just fine in the spreadsheet! Tom Mercadante Oracle Certified Professional -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 20, 2004 3:44 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Spool to Excel FileStrange, no one has mentioned OWA_SYLK. Do a search on SYLK at asktom.oracle.com There are 2 versions, one for web output and one for excel output. SYLK allows cell references, etc, if needed, which you won't get with CSV. Jared "Mudhalvan, Moovarkku" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/19/2004 08:44 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Spool to Excel FileDear Friends, I am trying to send output from SQLPlus to Excel file. If anyone did the same before please let me know. Thank YouMudhalvan M.M-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mudhalvan, MoovarkkuINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Spool to Excel File
If you use tab separated columns, you can also generate formula's that look like text, but work just fine in the spreadsheet! Didn't realize. In any case, Perl is a much superior tool for this. The SpreadSheet::WriteExcel module allow you to write to individual pages in a workbook. Jared Mercadante, Thomas F [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/20/2004 12:59 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Spool to Excel File SYLK allows cell references, etc, if needed, which you won't get with CSV. Ahh. but you can with my method! If you use tab separated columns, you can also generate formula's that look like text, but work just fine in the spreadsheet! Tom Mercadante Oracle Certified Professional -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 3:44 PM To: Multiple recipients of list ORACLE-L Subject: Re: Spool to Excel File Strange, no one has mentioned OWA_SYLK. Do a search on SYLK at asktom.oracle.com There are 2 versions, one for web output and one for excel output. SYLK allows cell references, etc, if needed, which you won't get with CSV. Jared Mudhalvan, Moovarkku [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/19/2004 08:44 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Spool to Excel File Dear Friends, I am trying to send output from SQLPlus to Excel file. If any one did the same before please let me know. Thank You Mudhalvan M.M -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mudhalvan, Moovarkku 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).
RE: Spool to Excel File
Title: Message Thank you so much for all your response... To solve this issue temporarily... I used SYLK But i was planning to go for Perl. Since i am begineer in Perl hope i need to study a bit.. Thank You Mudhalvan M.M -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 8:29 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Spool to Excel File If you use tab separated columns, you can also generate formula's that look like text, but work just fine in the spreadsheet! Didn't realize. In any case, Perl is a much superior tool for this. The SpreadSheet::WriteExcel module allow you to write to individual pages in a workbook. Jared "Mercadante, Thomas F" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/20/2004 12:59 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: RE: Spool to Excel File"SYLK allows cell references, etc, if needed, which you won't get with CSV. " Ahh. but you can with my method! If you use tab separated columns, you can also generate formula's that look like text, but work just fine in the spreadsheet! Tom Mercadante Oracle Certified Professional -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 20, 2004 3:44 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Spool to Excel FileStrange, no one has mentioned OWA_SYLK. Do a search on SYLK at asktom.oracle.com There are 2 versions, one for web output and one for excel output. SYLK allows cell references, etc, if needed, which you won't get with CSV. Jared "Mudhalvan, Moovarkku" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/19/2004 08:44 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Spool to Excel FileDear Friends, I am trying to send output from SQLPlus to Excel file. If anyone did the same before please let me know. Thank YouMudhalvan M.M-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mudhalvan, MoovarkkuINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Spool to Excel File
Hi Thomas-
Pretty interested in your method!! I tried it out but I guess I have to
create the WTW_JOB_NOTIFY table and populate it and I can't figure where the
mail notification is used. Assit with the table structure and an explanation
on how this table is used.
Thanks and Cheers,
CSW Simon.
-Original Message-
Sent: Tuesday, January 20, 2004 4:04 PM
To: Multiple recipients of list ORACLE-L
Mudhalvan,
I generate files that excel can open all the time. they are not actual
real excel files, but Excel can deal with them quite easily.
Here is a tablespace report I run every week. Note the use of the CHR(9)'s.
This is a TAB character. This forces each column into a new cell in the
spreadsheet. CHR(10) is a line-feed.
Feel free to borrow all of this!
Hope this helps!
SET serveroutput ON
SET feedback OFF
SET lines 150
SET pages 100
SET trimspool ON
exec dbms_output.enable(10)
spool tbslspace_rpt.xls
DECLARE
CURSOR UpTime IS
SELECT INITCAP(instance_name) Instance_Name ,INITCAP(Host_Name) Host_Name
,Version,
ROUND(SYSDATE+1-startup_time) || DECODE(ROUND(SYSDATE+1-startup_time),1,'
Day ', ' Days ') ||
MOD(ROUND((SYSDATE+1 - startup_time) * 24),24) || ' Hours ' ||
MOD(ROUND((SYSDATE+1 - startup_time) * 24*60),60) || ' Minutes ' UpTime
FROM v$instance;
UpTime_Rec UpTime%ROWTYPE;
CURSOR TblSpace IS
SELECT d.status , d.tablespace_name ,
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'999,990') Tbs_Size,
TRUNC(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024)Used,
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00')
Used_Pct ,
DECODE(SIGN(80 - NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100,
0)),-1,'** Warning 80% **',NULL) Msg
FROM sys.DBA_TABLESPACES d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM DBA_DATA_FILES GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM DBA_FREE_SPACE GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
ORDER BY 2;
TblSpace_Rec TblSpace%ROWTYPE;
c_email_list VARCHAR2(300);
mail_message VARCHAR2(32000);
mail_message1 VARCHAR2(32000);
loc_start_time DATE;
TblSpace_Msg NUMBER := 0;
BEGIN
BEGIN
SELECT email_notify_txt
INTO c_email_list
FROM WTW_JOB_NOTIFY
WHERE job_name = UPPER('Wtw_Report_Tablespaces');
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
loc_start_time := SYSDATE;
OPEN UpTime;
FETCH UpTime INTO UpTime_Rec;
CLOSE UpTime;
dbms_output.put_line(CHR(9) || CHR(9) ||
UpTime_Rec.Instance_Name || INITCAP('
Uptime/TABLESPACE Report FOR ') ||
TO_CHAR(SYSDATE,'fmMonth ddth, '));
dbms_output.put_Line(CHR(9) || CHR(9) ||
'UpTime : ' || UpTime_Rec.UpTime);
dbms_output.put_Line(
'Status' || CHR(9) ||
INITCAP('TABLESPACE Name') || CHR(9) ||
INITCAP('SIZE (M)') || CHR(9) ||
'Used (M)' || CHR(9) ||
'Used (Pct)' || CHR(9) ||
'Message');
OPEN TblSpace;
FETCH TblSpace INTO TblSpace_Rec;
WHILE TblSpace%FOUND LOOP
dbms_output.put_Line(
TblSpace_Rec.Status || CHR(9) ||
TblSpace_Rec.Tablespace_Name || CHR(9) ||
TblSpace_Rec.Tbs_Size|| CHR(9) ||
TblSpace_Rec.Used|| CHR(9) ||
TblSpace_Rec.Used_Pct|| CHR(9) ||
TblSpace_Rec.Msg);
IF TblSpace_Rec.Msg IS NOT NULL THEN
TblSpace_Msg := 1;
END IF;
FETCH TblSpace INTO TblSpace_Rec;
EXIT WHEN TblSpace%NOTFOUND;
END LOOP;
CLOSE TblSPace;
END;
/
spool OFF
exit
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Monday, January 19, 2004 11:44 PM
To: Multiple recipients of list ORACLE-L
Dear Friends,
I am trying to send output from SQLPlus to Excel file. If any
one did the same before please let me know.
Thank You
Mudhalvan M.M
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mudhalvan, Moovarkku
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
Spool to Excel File
Dear Friends, I am trying to send output from SQLPlus to Excel file. If any one did the same before please let me know. Thank You Mudhalvan M.M -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mudhalvan, Moovarkku 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).
Re: Spool to Excel File
Hi, You won't be able to write an Excel format directly but you can create a .csv file, which Excel will happily read in - you'll just have no real formatting options. Look into some of the sql*plus commands like set heading off, set verify off, set feedback off, set pages 0, set lines 1000, set trimspool on. Of course you might want different settings to the ones I proposed but you will find these commands useful to get rid of stuff you don't (or perhaps do) want in the file. If you really want to generate a legitimate Excel file then start searching the web for programs to do this. I have seen one or two that do this reasonably well but cannot remember names. Regards, Mark. Mudhalvan, Moovarkku To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: apan.co.jp Subject: Spool to Excel File Sent by: [EMAIL PROTECTED] .com 20/01/2004 15:44 Please respond to ORACLE-L Dear Friends, I am trying to send output from SQLPlus to Excel file. If any one did the same before please let me know. Thank You Mudhalvan M.M -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mudhalvan, Moovarkku 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). Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard 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).
Re: Spool to Excel File
PRANK Try with spool c:\Program*Files\Microsoft*Office\Office\excel.exe /PRANK On 2004.01.19 23:44, Mudhalvan, Moovarkku wrote: Dear Friends, I am trying to send output from SQLPlus to Excel file. If any one did the same before please let me know. Thank You Mudhalvan M.M -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mudhalvan, Moovarkku 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
Re: Spool to Excel File
Hi, Years ago, I stole this idea from: http://www.quest-pipelines.com/newsletter-v3/newsletter_1002.htm I've used it a few times, mostly to impress bystanders. It's got size limitations, but kinda cool anyhow. QUOTE Getting SQL Query Results into an Excel Spread Sheet By Pavel Luzanov This months script will help users get SQL query results into an Excel spread sheet. SET LINESIZE 999 VERIFY OFF FEEDBACK OFF SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF DEFINE table_name = 1 SPOOL table_name..xls SELECT * FROM table_name; SPOOL OFF SET MARKUP HTML OFF ENTMAP OFF SPOOL OFF PREFORMAT ON SET LINESIZE 80 VERIFY ON FEEDBACK ON REM Use this command to start Excel from char-mode sqlplus REM HOST start table_name..xls REM Use this command to start Excel from GUI-mode sqlplus HOST start excel.exe table_name..xls \QUOTE Regards, Mike Thomas --- Mudhalvan, Moovarkku [EMAIL PROTECTED] wrote: Dear Friends, I am trying to send output from SQLPlus to Excel file. If any one did the same before please let me know. Thank You Mudhalvan M.M -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mudhalvan, Moovarkku 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). __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Michael Thomas 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).
