Email alerts and export report to Excel

0 pts.
Tags:
E-mail applications
Microsoft Exchange
Oracle
Oracle 9i
Sendmail
I'm running oracle9.2 on win2k. I need to do the following: 1) Run a script to monitor my storage percentage,number of extents of a tablespace, free space. After which i need to email this report to myself via MS outlook so that i have a record of this. The email shld be on a daily basis. This is the script i hv created: SELECT upper(f.tablespace_name) "tablespace name", d.Tot_grootte_Mb "tablespace size(M)", d.Tot_grootte_Mb - f.total_bytes "used(M)", to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),'990.99') "cap(%)", f.total_bytes "free(M)" FROM (SELECT tablespace_name, round(SUM(bytes)/(1024*1024),2) total_bytes, round(MAX(bytes)/(1024*1024),2) max_bytes FROM sys.dba_free_space GROUP BY tablespace_name) f, (SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb FROM sys.dba_data_files dd GROUP BY dd.tablespace_name) d WHERE d.tablespace_name = f.tablespace_name ORDER BY 4 DESC; Q1: How do i specify that the results get emailed to me? 2) For the above script, it would be neat if i could export the report into Excel or csv files. How do i this? i will be using both email and Excel to manage all my monitoring so that i could have an "alert" + report for my manager. At the same time, i dont want to configure extra storage space for OEM to use as a management server, as i only had 1 server running oracle
ASKED: July 18, 2005  3:12 AM
UPDATED: July 20, 2005  9:17 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,
for your first question you could create a cdo message to mail the output to your recipient
set msg = createObject(“CDO.Message”)
msg.from = “your choosen name”
msg.to = “your recipient”
msg.subject = “your subject”
msg.textbody = ” you can include the output of your scipt here directly”
or
msg.htmlbody = “you can design a html page for your output and include it to the body”
msg.Configuration.Fields.Item _ (“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
msg.Configuration.Fields.Item _ (“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “here your SMTP Server”
msg.Configuration.Fields.Item _ (“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
msg.Configuration.Fields.Update
msg.send

Second Question
set ofso = createObject(“scripting.FileSystemObject”)
se ofs = ofso.createTextfile(“c:testtest.csv”)
ofs.writeline “your output”
ofs.writeline ….
ofs.writeline ..
ofs.writeline …

Follow this link to learn more about scripting with exel

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting05112004.asp

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Mayleong
    Hi Sinclair, Sorry i am totally lost over your explanation. I am running a SQL script in oracle (v9.2 running over win2k). I am aware that there is a DBMS package for dba to use, so i was thinking of how i could use this package to export the results from the SQL script to excel. Q1: I need to schedule a script to run daily on my oracle server at a fixed time. How do i do this? Q2: I need to use some packages (is it DBMS?), PL/SQL programming to enable the output from the script to excel or csv format Q3: I also need the result from the script to email itself to me PS: This is the script that i run on oracle to monitor tablespaces and storage capacity: SELECT upper(f.tablespace_name) "tablespace name", d.Tot_grootte_Mb "tablespace size(M)", d.Tot_grootte_Mb - f.total_bytes "used(M)", to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),'990.99') "cap(%)", f.total_bytes "free(M)" FROM (SELECT tablespace_name, round(SUM(bytes)/(1024*1024),2) total_bytes, round(MAX(bytes)/(1024*1024),2) max_bytes FROM sys.dba_free_space GROUP BY tablespace_name) f, (SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb FROM sys.dba_data_files dd GROUP BY dd.tablespace_name) d WHERE d.tablespace_name = f.tablespace_name ORDER BY 4 DESC;
    0 pointsBadges:
    report
  • Mljsher
    Oracle has a scheduling function called dbms_job that will allow you to schedule a job to run at a given time. You could also use the NT task scheduler to do the same thing. To produce Oracle output in a certain format, the best thing to do is: select column_name||','||column_name||','||column_name|| etc... Just remember, if you're using PL/SQL, to execute the command 'set serveroutput on' from a SQL> prompt before you start your procedure. In order to mail the output to the appropriate e-mail address, there are a lot of freeware e-mail programs available to do the job, such as blat, etc...
    0 pointsBadges:
    report
  • JLeask
    In answer to your Email query: To use Email from PL/SQL (v9.2) you require a Java VM. Run the following SQL to check if Java virtual machine (VM) is already installed select count(*) from dba_objects where object_type like '%JAVA%'; A count of zero shows that VM has not been installed. If the count is non zero then move to point 4 below. 1. Install Oracle Java virtual machine. Ensure that the target database has a shared_pool size greater than 59Mb and a Java_Pool size greater than 19Mb. CD to ORACLE_HOME/javavm/install Enter sqlplus /nolog and connect / as sysdba Run initjvm.sql (this runs for many minutes) You are now ready to test the UTL_SMTP procedure, which sends Emails. Replace 'exchange.xxxx.com' with your Email server name. See the following example:- DECLARE v_connection UTL_SMTP.CONNECTION; BEGIN v_connection := UTL_SMTP.OPEN_CONNECTION('exchange.xxxx.com',25); UTL_SMTP.HELO(v_connection,'name@company.com'); UTL_SMTP.MAIL(v_connection,'name@company.com'); UTL_SMTP.RCPT(v_connection,'name@company.com'); UTL_SMTP.DATA(v_connection,'To:'name@company.com'||CHR(13)||CHR(10)|| 'Subject:Test Message'||CHR(13)||CHR(10)); UTL_SMTP.QUIT(v_connection); END; / Explaining the UTL_SMTP utility UTL_SMTP.OPEN_CONECTION: This requires the mail server host name and the port the mail server runs on (normally 25). This then opens the connection in order to send and Email. UTL_SMTP.HELO: The next step is to identify the sender to the mail server and initialise its state. UTL_SMTP.MAIL: This initiates the Email message and requires the sender's Email address. UTL_SMTP.RCPT: This identifies the recipient of the Email, it must be a fully qualified Email address. UTL_SMPT.DATA: This contains the data you wish to send e.g. subject, from, to definitions and the body of the Email. This is also where the Email is actually sent. UTL_SMPT.QUIT: Exits from the Email stream. Email PL/SQL procedure example: CREATE OR REPLACE PROCEDURE send_mail ( sender IN VARCHAR2, recipient IN VARCHAR2, subject IN VARCHAR2, message IN VARCHAR2 ) IS mail_host VARCHAR2(100) := 'exchange.xx.xxxx.com'; mail_conn utl_smtp.connection; crlf VARCHAR2(2) := CHR (13) || CHR (10); msg VARCHAR2(2000); BEGIN mail_conn := utl_smtp.open_connection (mail_host, 25); msg := 'Date: ' || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf || 'From: ' || sender || '' || crlf || 'Subject: ' || subject || crlf || 'To: ' || recipient || crlf || '' ||crlf || message; utl_smtp.helo (mail_conn, mail_host); utl_smtp.mail (mail_conn, sender); utl_smtp.rcpt (mail_conn, recipient); utl_smtp.data (mail_conn, msg); utl_smtp.quit (mail_conn); END; /
    0 pointsBadges:
    report
  • Randym
    I have done all that you are asking though Oracle Forms; but it should work through a stored procedure as well. To make Win2k execute the procedure at a specified time, use the windows scheduler. Have it execute an sql command file through SQL*Plus. SQL*Plus would then execute the stored procedure. To export the results to Excel, execute your SQL statement through a cursor within the PL/SQL stored procedure. Use the DDE package to POKE each row of the cursor into an Excel spreadsheet. To email, use the OLE2 package within the PL/SQL stored procedure to create a mapi session and attach the Excel file to the email.
    1,740 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following