Sending mail from PL/SQL procudure [message #36668] |
Tue, 11 December 2001 03:19 |
David Jeyathilak
Messages: 9 Registered: October 2001
|
Junior Member |
|
|
[[Referring to a message]]
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@i2pl.net','david@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@i2pl.net';
bcc_recipient VARCHAR2(50) default 'david@i2pl.net';
success varchar2(50) default 'mail sent';
BEGIN
mail_conn := utl_smtp.open_connection('snxz0001@i2pl.net', 25);
utl_smtp.helo(mail_conn, 'snxz0001@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;
/
----------------------------------------------------------------------
|
|
|
|
Re: Sending mail from PL/SQL procudure [message #36673 is a reply to message #36668] |
Tue, 11 December 2001 04:58 |
David Jeyathilak
Messages: 9 Registered: October 2001
|
Junior Member |
|
|
Hi,
After setting, serveroutput on...I got a message
"ORA-29540: class oracle/plsql/net/TCPConnection does not exist"
what should I do to overcome this?
Thanxs and Regards,
David.
----------------------------------------------------------------------
|
|
|
Re: Sending mail from PL/SQL procudure [message #36675 is a reply to message #36668] |
Tue, 11 December 2001 17:36 |
David Jeyathilak
Messages: 9 Registered: October 2001
|
Junior Member |
|
|
Hi,
I tried executing the procedure. It didnt execute.
SQL> exec dbms_java.loadjava('-resolve plsql/jlib/plsql.jar');
BEGIN dbms_java.loadjava('-resolve plsql/jlib/plsql.jar'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_JAVA.LOADJAVA' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Please help.
thanxs and regards,
David.
----------------------------------------------------------------------
|
|
|
Re: Sending mail from PL/SQL procudure [message #36681 is a reply to message #36668] |
Wed, 12 December 2001 00:14 |
tinel
Messages: 42 Registered: November 2001
|
Member |
|
|
Hi
There is no java problem, the first argument in function utl_smtp.open_connection must by your mail domain, like mailhost.mydomain.com and the second argument in the function utl_smtp.helo must be too your mail domain, so try this:
mail_conn := utl_smtp.open_connection('mailhost.mydomain.com', 25);
utl_smtp.helo(mail_conn, 'mailhost.mydomain.com');
Bye
----------------------------------------------------------------------
|
|
|