Q

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.

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.
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 last published in August 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close