sending mail

[mylena]

Hello all.
This is my first message on this forum.
I am using Oracle 9i and Developer 6i.
I would like to send an e-mail from an application by pressing a button. Also i want that the text of the e-mail be standard but with some changing parameters. How can i do this?
Thanks.
Mylena.

 

[lewisp]

Look at using the utl_smtp package.

 

[mylena]

What is this utl_smtp package?

 

[mylena]

Hello.
I have found the following code in developer help but it doesn't work. I have also the code for the other procedure used in the code. Could you please write the code for sending mail?

PROCEDURE Mail_Warning( send_to VARCHAR2) IS
the_username VARCHAR2(40);
the_password VARCHAR2(40);
the_connect VARCHAR2(40);
the_command VARCHAR2(2000);
BEGIN
the_command := 'orasend '||' to='||send_to||' std_warn.txt '||' subject="## LATE PAYMENT ##"'||' user='||the_username||' password='||the_password||' connect='||the_connect;
Message('Sending Message...', NO_ACKNOWLEDGE);
Synchronize;
Host( the_command, NO_SCREEN );
IF NOT Form_Success THEN
Message('Error -- Message not sent.');
ELSE
Message('Message Sent.');
END IF;
END;

 

[vampirus]

USE THIS PROCEDURE TO SEND EMAIL
CREATE OR REPLACE PROCEDURE JOYEMAIL (pivcFromName IN VARCHAR2
,pivcFromEmail IN VARCHAR2
,pivcToName IN VARCHAR2
,pivcToEmail IN VARCHAR2
,pivcSubject IN VARCHAR2
,pivcBody IN VARCHAR2
,pivcCcName IN VARCHAR2 := NULL
,pivcCcEmail IN VARCHAR2 := NULL
,pivcBccName IN VARCHAR2 := NULL
,pivcBccEmail IN VARCHAR2 := NULL
,pivcMailIp IN VARCHAR2
,pinPort IN NUMBER ) IS
objConnection utl_smtp.connection;
vrData RAW(32767);
BEGIN
objConnection := UTL_smtp.open_connection(pivcMailIp,pinPort);
UTL_smtp.ehlo(objConnection, pivcMailIp);
UTL_smtp.mail(objConnection, pivcFromEmail);
UTL_smtp.rcpt(objConnection, pivcToEmail);
IF (pivcCcEmail IS NOT NULL) THEN
UTL_smtp.rcpt(objConnection, pivcCcEmail);
END IF;
IF (pivcBccEmail IS NOT NULL) THEN
UTL_smtp.rcpt(objConnection, pivcBccEmail);
END IF;
UTL_smtp.open_data(objConnection);
/* ** Sending the header information */
UTL_smtp.write_data(objConnection, 'From: ' || '"' || pivcFromName || '" <' || pivcFromEmail ||'>' || UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection, 'To: ' || '"' || pivcToName || '" <' || pivcToEmail ||'>' || UTL_tcp.CRLF);
IF (pivcCcEmail IS NOT NULL) THEN
UTL_smtp.write_data(objConnection, 'Cc: ' || '"' || pivcCcName || '" <' || pivcCcEmail ||'>' || UTL_tcp.CRLF);
END IF;
IF (pivcBccEmail IS NOT NULL) THEN
UTL_smtp.write_data(objConnection, 'Bcc: ' || '"' || pivcBccName || '" <' || pivcBccEmail ||'>' || UTL_tcp.CRLF);
END IF;
UTL_smtp.write_data(objConnection, 'Subject: ' || pivcSubject || UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection, 'MIME-Version: ' || '1.0' || UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection, 'Content-Type: ' || 'text/plain; charset=utf-8' || UTL_tcp.CRLF);
UTL_smtp.write_data(objConnection, 'Content-Transfer-Encoding: ' || '8bit' || UTL_tcp.CRLF);
/* ** End of header information */
UTL_smtp.write_data(objConnection, UTL_tcp.CRLF);
/* ** Actual body is sent here */
vrData := utl_raw.cast_to_raw(pivcbody);
UTL_smtp.write_raw_data(objConnection, vrData);
/* ** Connection is closed here */
UTL_smtp.close_data(objConnection);
UTL_smtp.quit(objConnection);
EXCEPTION
WHEN UTL_smtp.transient_error OR UTL_smtp.permanent_error THEN
UTL_smtp.quit(objConnection);
dbms_output.put_line(sqlerrm);
WHEN OTHERS THEN
UTL_smtp.quit(objConnection);
dbms_output.put_line(sqlerrm);
END;
/

 

[mylena]

Thank you very much for the code. When Lewisp told me about the utl_smtp package i looked in Developer's help but dindn't find anything.
I have tried your code with Developer but i received an error that i didn't have the right to access utl_tcp. I can not connect as sys. What must i do to connect as sys? I tried sys/password @mylena as sysdba but it didn't work.
Finally i have introduced the procedure in sql+. It was OK. But some problems ocurred when calling it. I have given some values to the parameters but nothing happened. The problem i think it is that i am using dial up connection to the internet. I have given to pinvcMailIP my computer's name and for the pinPort i put 25. I have a SMTP virtual server which is running.

Thank you again and regards.
Mylena.