I need to run a select statement every monday evening at 6, and I need a generated spool file to be sent as a mail attachment to multiple users using PL/SQL . Can anyone suggest how to do this? I need to do this in a PL/SQL procedure, not using a cron tab or shell script. Thanks in advance.
Requires Free Membership to View
To send an email from a spool file every Monday evening at 6:00, you will need to create: 1) an Oracle job procedure; 2) a PL/SQL to generate a select statement; 3) a PL/SQL package, which receives the output from the select statement and sends it as an email.
If you want to run a select statement every Monday at 6:00 pm, use an Oracle job. I have an example of an Oracle job:
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'begin
proc_mail;
end;'
, next_date => to_date
('13/08/2006 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'NEXT_DAY(TRUNC(SYSDATE+1)+6/24,
''MONDAY'')'
,no_parse => FALSE
);
END;
/
Note: This job calls a procedure called "PROC_MAIL."
The procedure "PROC_MAIL" selects info from a SQL select statement and sends it to a package called "MAIL." Listed below is the procedure "PROC_MAIL."
create or replace procedure proc_mail
as
cursor c1 is
select ename||' '||job||' '||hiredate empdata
from scott.emp;
CRLF VARCHAR2(10) := CHR(13)||CHR(10);
v_empdata varchar2(100);
v_message varchar2(32000);
begin
open c1;
loop
fetch c1 into v_empdata;
exit when c1%notfound;
v_message := v_message||crlf||v_empdata;
end loop;
mail.send('williams.greg@dol.gov','williams.greg@dol.gov',
v_message,'test message');
close c1;
end;
The package below is used to send email to a single recipient.
CREATE OR REPLACE PACKAGE BODY Mail AS
FUNCTION get_sys_parameter (p_name VARCHAR2) RETURN
VARCHAR2 IS
v_return VARCHAR2(2000);
TmpVar NUMBER;
BEGIN
tmpVar := 0;
SELECT VALUE
INTO v_return
FROM SYSTEM_PARAMETERS
WHERE NAME = p_name;
RETURN v_return;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
END get_sys_parameter;
PROCEDURE send (
p_sender VARCHAR2,
p_recipient VARCHAR2,
p_message VARCHAR2,
p_subject VARCHAR2
)
IS
mail_conn utl_smtp.connection;
TmpVar NUMBER;
BEGIN
tmpVar := 0;
mail_conn := utl_smtp.open_connection
(g_smtp_server, g_smtp_server_port);
utl_smtp.helo(mail_conn, g_smtp_server);
utl_smtp.Mail(mail_conn, p_sender);
utl_smtp.rcpt(mail_conn, p_recipient);
utl_smtp.DATA(
mail_conn,
'Subject:'||p_subject||
CHR (13) ||
CHR (10) ||
'To:'||
p_recipient ||
CHR (13) ||
CHR (10) ||
p_message||
CHR (13) ||
CHR (10)
);
utl_smtp.quit (mail_conn);
END send;
BEGIN
g_smtp_server := get_sys_parameter ('smtp_server');
IF g_smtp_server IS NULL THEN
RAISE_APPLICATION_ERROR(-20031, 'Could not find system
paremeter smtp_server in table system_parameters.');
END IF;
g_smtp_server_port := CAST(get_sys_parameter
('smtp_server_port') AS NUMBER);
IF g_smtp_server_port IS NULL THEN
RAISE_APPLICATION_ERROR(-20031, 'Could not find system
parameter smtp_server_port in table system_parameters.');
END IF;
END Mail;
/
where the table SYSTEM_PARAMETERS has one column and two records. The value of the two records is the server ip-address and the port number.This was first published in August 2006
Join the conversationComment
Share
Comments
Results
Contribute to the conversation