Ask the Expert

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 . 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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

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
Sort by: OldestNewest

Forgot Password?

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

Your password has been sent to: