Re: Seding mail forms using outlook

From: Joost Bataille <jREMOVETHISbataille>
Date: Mon, 13 Jan 2003 11:50:09 +0100
Message-ID: <avu5l0$jj0f2$1_at_ID-171327.news.dfncis.de>

"rameshk" <member22202_at_dbforums.com> wrote in message news:2380265.1042448260_at_dbforums.com...

>

> Yes Morgan, thanks for your reply.
> I supposed to user UTL_SMTP package for this purpose, but unfortunatly i
> have 8.1.6 version of oracle.
> Now can you tell me, is it possible to use this package for sending mail
> with this version.
> If yes, can you pls hdlp out the procedure to be followed.

UTL_SMTP should be present in a 8.1.6 database. If not you should install the Java option.
I wrote a procedure that sends an email message after the completion of a batch process. It composes a header and then loops through the message table ('met_boodschappen').
It was the first procedure I wrote using UTL_SMTP so I spent some time on implementing error checking. It might give you an idea but check the docs. Hope this helps.

PROCEDURE STUUR_MAIL
IS

c_mailhost              constant varchar2(50) := '<name of your mailserver';
c_local_host            constant varchar2(50) := '<name of your local host';
c_sender                constant varchar2(25) := '<sender's emailadress ';
c_recipient             constant varchar2(50) := '<emailadres of the
recipient';
_reply_to              constant varchar2(50) := '<emailadress of person to
reply to';
c_crlf                  constant varchar2(2)  := utl_tcp.crlf;      --

constant from the utl_tcp package

cursor                  c_doe_bsp
                        is
                        select  tekst
                          from  met_boodschappen
                          where procescode = 'mit_doe_mit';
r_doe_bsp               c_doe_bsp%rowtype;

cursor                  c_bsp
                        is
                        select  procescode
                          ,     tekst
                          ,     status
                          from  met_boodschappen
                          where procescode != 'mit_doe_mit'
                          and   procescode like 'mit%'
                          order by bsp_id;
r_bsp                   c_bsp%rowtype;

l_connection            utl_smtp.connection;
l_reply                 utl_smtp.reply;
l_header                varchar2(1000);
l_global_name           varchar2(20);

l_aantal_bsp            pls_integer := 0;
l_proc                  varchar2(25);

e_error                  exception;

• PL/SQL Block begin
• find database naam select substr(upper(global_name), 1, instr(global_name,'.',1,1) -1) into l_global_name from global_name;

open c_doe_bsp;
fetch c_doe_bsp into r_doe_bsp;
close c_doe_bsp;

• compose the mail header
• note the date format!! l_header := 'Date: ' ||to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss')||c_crlf ||'From: ' ||'Menukaart Import batch process'||c_crlf ||'Subject: ' ||'Automatic email send by batch process '||c_crlf ||'To: ' ||c_recipient||c_crlf ||'Reply-To: '||c_reply_to||c_crlf ;

l_reply := utl_smtp.open_connection ( c_mailhost, 25, l_connection); if not substr(to_char(l_reply.code), 1, 1) = '2' then   l_proc := 'open_connection';
  raise e_error;
end if;

l_reply := utl_smtp.helo(l_connection, c_local_host); if not substr(to_char(l_reply.code), 1,1) = '2' then   l_proc := 'helo';
  raise e_error;
end if;

l_reply := utl_smtp.vrfy(l_connection, c_recipient); if not substr(to_char(l_reply.code), 1,1) = '2' then   l_proc := 'verify';
  raise e_error;
end if;

l_reply := utl_smtp.mail(l_connection, c_sender); if not substr(to_char(l_reply.code), 1,1) = '2' then   l_proc := 'mail';
  raise e_error;
end if;

l_reply := utl_smtp.rcpt(l_connection, c_recipient); if not substr(to_char(l_reply.code), 1,1) = '2' then   l_proc := 'rcpt';
  raise e_error;
end if;

l_reply := utl_smtp.open_data(l_connection); -- returncode can also be a 3 which means 'so far so good, please carry on...'
if not substr(to_char(l_reply.code), 1,1) in ('2', '3') then   l_proc := 'open_data';
  raise e_error;
end if;

utl_smtp.write_data(l_connection, l_header);
utl_smtp.write_data(l_connection, c_crlf);
utl_smtp.write_data(l_connection, 'Database: '
                                  ||l_global_name
                                  ||c_crlf);
utl_smtp.write_data(l_connection,  r_doe_bsp.tekst
                                   ||c_crlf);

open c_bsp;
fetch c_bsp into r_bsp;
while c_bsp%found
loop
  utl_smtp.write_data(l_connection, r_bsp.procescode

                                     ||': '
                                     ||r_bsp.tekst
                                     ||' '
                                     ||r_bsp.status
                                     ||c_crlf
                                     ||c_crlf
                                     );

  fetch c_bsp into r_bsp;
end loop;

l_reply := utl_smtp.close_data(l_connection); if not substr(to_char(l_reply.code), 1, 1) = '2' then   l_proc := 'close_data';
  raise e_error;
end if;

l_reply := utl_smtp.quit(l_connection);
if not substr(to_char(l_reply.code), 1, 1) = '2' then   l_proc := 'quit';
  raise e_error;
end if;

exception
when e_error then
  insert into met_boodschappen ( bsp_id, procescode, datum, ind_bsp_type, tekst, status)

    values ( met_bsp_seq1.nextval

    ,         'mit_mail'
    ,         sysdate
    ,         'F'
    ,         'Error in procedure stuur_mail in '
              ||l_proc
              ||', code: '
              ||to_char(l_reply.code)
    ,         substr(l_reply.text, 1, 240)

    );
  commit;
when others then
  l_sqlerrm := substr(sqlerrm, 1, 240);
  insert into met_boodschappen ( bsp_id, procescode, datum, ind_bsp_type, tekst, status)

    values ( met_bsp_seq1.nextval

    ,         'mit_mail'
    ,         sysdate
    ,         'F'
    ,         'Unkwon error in procedure stuur_mail:'
    ,         l_sqlerrm

    );
  commit;
end stuur_mail;

Regards, Joost

--
Joost Bataille
University of Amsterdam ICT centre

Received on Mon Jan 13 2003 - 11:50:09 CET