| utl_smtp [message #40623] |
Thu, 24 October 2002 04:58  |
eelco oudbier
Messages: 3
Registered: October 2002
|
Junior Member |
|
|
Hello,
I have a problem when i try to execute the following procedure
PROCEDURE send_mail (
msg_from VARCHAR2 := 'EOUDBIER@RIJNSTATE.NL',
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 ('MAILhost', 25); -- open the SMTP port 25 on local machine
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 because of: '
|| SQLERRM
);
END;
END;
Results DBMS_OUTPUT :
220 rijnstate.nl Server ESMTP ready at Thu, 24 Oct 2002 11:30:44 +0200
250 rijnstate.nl Hello localhost
501 strangeness between : and <
503 Waiting for MAIL command
503 Waiting for MAIL command
550 Syntax error
550 Syntax error
This tells me that there is a connection with the localhost. But 501 tells me nothing.
Is this a known problem.
Thanks Eelco!!
|
|
|
|
| Re: utl_smtp [message #40631 is a reply to message #40623] |
Thu, 24 October 2002 11:13  |
welchdor
Messages: 1
Registered: October 2002
|
Junior Member |
|
|
I'm not sure I follow all your proc is doing, but here is a sample of what we are using which seems simpler. Maybe it will be of help. I am assuming that the necessary setup for this has already been done on your server.
PROCEDURE SEND_MAIL
(v_sender IN VARCHAR2,
v_recipient IN VARCHAR2,
v_subj IN VARCHAR2,
v_body IN VARCHAR2,
v_success IN OUT VARCHAR2)
/********************************************************************************/
/*
-- NAME send_mail
-- TYPE stored pl/sql procedure
-- PURPOSE This procedure sends an e-mail message to the recipient passed in
--
-- The parameters that must be passed in are:
--
-- v_sender -- full SMTP e-mail address of sender (e.g., 'bob@mycompany.com')
-- v_recipient -- full SMTP e-mail address of recipient
-- v_subj -- subject line of e-mail
-- v_body -- body of e-mail message - max is 4000 chars less the
-- characters used in the heading ('to', 'from', etc.)
--
-- RETURNS: 'Y' for sucessful send of e-mail; 'N' for failure to send
--
*/
/********************************************************************************/
AS
crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2(4000);
mail_conn UTL_SMTP.CONNECTION;
mail_host VARCHAR2(50) := '000.00.000.00'; -- NOTE: IP address of exchange server
-- cc_recipient VARCHAR2(50) := 'bob@mycompany.com';
-- bcc_recipient VARCHAR2(50) := 'jane@mycompany.com';
BEGIN
mail_conn := utl_smtp.open_connection(mail_host, 25);
utl_smtp.helo(mail_conn, mail_host);
utl_smtp.mail(mail_conn, v_sender);
utl_smtp.rcpt(mail_conn, v_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: ' || v_sender || crlf ||
'To: ' || v_recipient || crlf ||
-- 'Cc: ' || cc_recipient || crlf ||
-- 'Bcc: ' || bcc_recipient || crlf ||
'Subject: ' || v_subj || crlf;
mesg := mesg || '' || crlf || v_body;
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
v_success := 'Y';
EXCEPTION
/* NOTE: The following can be uncommented for testing, or if there needs to be
expanded error code returns
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line('Invalid Operation in SMTP transaction.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line('Temporary problems with sending email - try again later.');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line('Errors in code for SMTP transaction.');
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
*/
WHEN OTHERS THEN
v_success := 'N';
END SEND_MAIL;
/
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
