Sending email using Oracle 8i

[Maverick7]

I need send e-mail from my database to "customers". I have Oracle 8i for NT. I don't have utl_smtp.
Thanks.

Maverick
[sig][/sig]

 

[carp]

If you don't have utl_smtp, you can run the utlsmtp.sql script in your %ORACLE_HOME%\rdbms\admin subdirectory to get this package. Once this is accomplished, you might want to take a look at my entry in the FAQ section. [sig][/sig]

 

[Guest_imported]

CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from varchar2 := 'oracle',
msg_to varchar2,
msg_subject varchar2 := 'E-Mail message from your database',
msg_text varchar2 := '' )
IS
c utl_tcp.connection;
rc integer;
BEGIN
c := utl_tcp.open_connection('127.0.0.1', 25); -- open the SMTP port
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'HELO localhost');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, msg_text);
rc := utl_tcp.write_line(c, '.'); -- End of message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); -- Close the connection
EXCEPTION
when others then
raise_application_error(-20000,
'Unable to send e-mail message from pl/sql');
END;
/
show errors

-- Examples:
set serveroutput on

exec send_mail(msg_to =>'orafaq@oraaq.org');

exec send_mail(msg_to =>'orafaq@orafaq.org',
msg_text=>'Look Ma, I can send mail from plsql'
);

 

[vskalyan]

As far as I know you can send e-mail using UTL_SMTP and UTL_TCP packages which are available in Oracle 8.1.6 version. I think those package scripts are not available in Oracle 8.1.5.
[sig][/sig]

 

[MikeLacey]

Yup -- all covered in carp's FAQ here in tek-tips actually... [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Making mistakes, so you don't have to. &lt;grin&gt;[/sig]

 

[RandyD]

Also check out a cool utility called EZDBMail

http://www.ezdbmail.com

it works on Oracle 7 and 8, and you dont have to learn smtp.

just insert some data to a table ,and then a mail is formatted and sent.

Randy