Sending spool file as mail attachment

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('[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.