Home » SQL & PL/SQL » SQL & PL/SQL » Sending mail from PL/SQL procudure
Sending mail from PL/SQL procudure [message #36668] Tue, 11 December 2001 03:19 Go to next message
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 #36671 is a reply to message #36668] Tue, 11 December 2001 04:33 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
you didnt get debug message 'success' either. did you?.
execute
set serveroutput on

and run procedure again.

----------------------------------------------------------------------
Re: Sending mail from PL/SQL procudure [message #36673 is a reply to message #36668] Tue, 11 December 2001 04:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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

----------------------------------------------------------------------
Previous Topic: About database login system trigger
Next Topic: Advanced sorting
Goto Forum:
  


Current Time: Thu Mar 28 11:01:20 CDT 2024