RE: Spool to Excel File

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Tue, 20 Jan 2004 05:04:26 -0800
Message-ID: <F001.005DDA66.20040120050426@fatcity.com>

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: mmudhalvan_at_gcrejapan.co.jp

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: 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.net
-- 
Author: Mercadante, Thomas F
  INET: thomas.mercadante_at_labor.state.ny.us

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: 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 Tue Jan 20 2004 - 07:04:26 CST