Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Sending mail from PL/SQL procedure

Sending mail from PL/SQL procedure

From: David Jeyathilak <david_at_ionideainteractive.com>
Date: 11 Dec 2001 05:12:33 -0800
Message-ID: <3e9566e2.0112110512.48c80c35@posting.google.com>


I compiled and executed the procedure(send2) after changing the
'mailhost' parameter and gave my mailserver IP address.(shown below)

SQL>Procedure created.

SQL> exec send2('david_at_i2pl.net','david_at_i2pl.net','test','test')

PL/SQL procedure successfully completed.

But I didnot receive any mail. What could be the problem??? Please help.

regards,
David


create or replace PROCEDURE send2(sender IN VARCHAR2, recipient IN VARCHAR2, subj IN VARCHAR2, body IN VARCHAR2) IS
crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 ); mesg VARCHAR2(4000);
mail_conn UTL_SMTP.CONNECTION;
cc_recipient VARCHAR2(50) default 'david_at_i2pl.net'; bcc_recipient VARCHAR2(50) default 'david_at_i2pl.net'; success varchar2(50) default 'mail sent';

BEGIN mail_conn := utl_smtp.open_connection('snxz0001_at_i2pl.net', 25);

utl_smtp.helo(mail_conn, 'snxz0001_at_i2pl.net'); 
utl_smtp.mail(mail_conn, sender); 
utl_smtp.rcpt(mail_conn, recipient); 
utl_smtp.rcpt(mail_conn, cc_recipient); 
utl_smtp.rcpt(mail_conn, bcc_recipient); 

mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: ' || sender || crlf ||
'To: ' || recipient || crlf ||
'Cc: ' || cc_recipient || crlf ||
'Bcc: ' || bcc_recipient || crlf ||
'Subject: ' || subj || crlf;

mesg:= mesg || '' || crlf || body;

utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);

dbms_output.put_line(success);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);

END;
/ Received on Tue Dec 11 2001 - 07:12:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US