Sending spool file as mail attachment
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, not using a cron tab or shell script.
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('[email protected]','[email protected]', 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.