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;
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…
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;
/
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.
Exchange, E-mail applications, Sendmail, Oracle, Oracle 9i
I'm running oracle9.2 on win2k. I need to do the following:
0
