Re: Sending mail from PL/SQL procedure

On Thu, 13 Dec 2001 20:14:05 +0300, "Vladimir M. Zakharychev" <bo>"David Jeyathilak" <david_at_ionideainteractive.com> wrote in message
>news:3e9566e2.0112110512.48c80c35_at_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;
>> /
>

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

should be

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

> utl_smtp.helo(mail_conn, 'snxz0001_at_i2pl.net');

should be

utl_smtp.helo(mail_conn, 'i2pl.net');

or whatever the localhost is.

>> utl_smtp.data(mail_conn, mesg);

should be

utl_smtp.write_data(mail_conn, mesg);
utl_smtp.close_data(mail_conn, mesg);

Also, you have no EXCEPTION handler trapping

SYS.UTL_SMTP.Transient_Error OR SYS.UTL_SMTP.Permanent_Error

HTH,
Brian Received on Thu Dec 13 2001 - 12:32:37 CST