Friday, September 11, 2009


sender IN VARCHAR2,
recipient IN VARCHAR2,
ccrecipient IN VARCHAR2,
subject IN VARCHAR2
) IS
/* One further thing is that as is used to terminate lines, it is a good idea to define this early in the code. In the UTL_TCP package there is a constant called UTL_TCP.CRLF already defined. */
crlf VARCHAR2 (2) := UTL_TCP.crlf;
err_num NUMBER;
err_msg VARCHAR2 (500);
/*TYPE CONNECTION: A CONNECTION is a record containing port and host information. You will use the connection object in all the other procedures but will never have to look at the contents of the connection object returned by the server.*/
connection UTL_SMTP.connection;
mailhost VARCHAR2 (30) := '';
---Your IP of mail server.
header VARCHAR2 (1000);
-- Start the connection.

--This is only a function only and returns an record of type CONNECTION. This is always the first thing you will do in an SMTP
connection := UTL_SMTP.open_connection (mailhost, 25);
header :=
'Date: '
|| TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
|| crlf
|| 'From: '
|| sender
|| ''
|| crlf
|| 'Subject: '
|| subject
|| crlf
|| 'To: '
|| recipient
|| crlf
|| 'CC: '
|| ccrecipient
|| crlf;
'Hello This is a test mail plz ignore it.. '
|| ''
|| crlf
|| 'Regards,'
|| ''
|| crlf
|| 'Devesh...';
-- Handshake with the SMTP server

--This is the Handshake procedure. Pass in the connection object from earlier and a string that identifies the sending host. This is overloaded as a function which returns a REPLY. HELO is always the next thing called after an OPEN_CONNECTION()
UTL_SMTP.helo (connection, mailhost);
--MAIL initiates the SMTP transaction. Sender is the email address of the sender, parameters is used for extended SMTP as per RFC1869.
UTL_SMTP.mail (connection, sender);
--RCPT specifies the recipients of the email. Recipient is the email address of the recipient. You can call this procedure for every recipient of the message. You must call MAIL before RCPT so that the SMTP transaction has started. Again, RCPT is overloaded as a function.
UTL_SMTP.rcpt (connection, recipient);
UTL_SMTP.rcpt (connection, ccrecipient);
--UTL_SMTP.OPEN_DATA(connection IN): Sends the DATA command. All subsequent calls to UTL_SMTP
UTL_SMTP.open_data (connection);
-- Write the header

--adds data to the string to be sent and finally UTL_SMTP
UTL_SMTP.write_data (connection, header);
-- The crlf is required to distinguish that what comes next is not simply part of the header..
UTL_SMTP.write_data (connection, '' || crlf);
UTL_SMTP.write_data (connection, crlf || MESSAGE);
--Close and Quit the connection
UTL_SMTP.close_data (connection);
UTL_SMTP.quit (connection);
WHEN UTL_SMTP.invalid_operation THEN
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 500);
DBMS_OUTPUT.put_line (' Invalid Operation in SMTP transaction.');
WHEN UTL_SMTP.transient_error THEN
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 500);
(' Temporary problems with sending email - try again later.');
WHEN UTL_SMTP.permanent_error THEN
DBMS_OUTPUT.put_line (' Errors in code for SMTP transaction.');

No comments: