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(100000)
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, yyyy'));
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 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).
