-
Can some body guide me How to send mail from Oracle.
Which package need to be install..?
what configuration need to be done for that.
Thanks,
Harry
-
Hi,
In order to send email from Oracle package UTL_SMTP must exist in the database.This package is created by running utlsmtp.sql and is available from Oracle version 8.1.6 and above.
The following procedure allows to send the email through Oracle
CREATE OR REPLACE PROCEDURE send_email
( sender IN VARCHAR2,
recipient IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2)
AS
mailhost VARCHAR2(100) := 'mailhost.fictional-domain.com';
mail_conn utl_smtp.connection;
BEGIN
mail_conn :=utl_smtp.open_connection(mailhost);
utl_smtp.helo(mail_conn,mailhost);
utl_smtp.mail(mail_conn,sender); -- sender
utl_smtp.rcpt(mail_conn,recipient); -- recipient
utl_smtp.open_data(mail_conn);
send_header(mail_conn,'From', '"Sender" <'||sender||'>');
send_header(mail_conn,'To', '"Recipient" <'||recipient||'>');
send_header(mail_conn,'Subject', subject);
utl_smtp.write_data(mail_conn, utl_tcp.CRLF||message);
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
utl_smtp.quit(mail_conn);
raise_application_error(-20000,
'Failed tosend mail due to the following error: ' || sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20001,
'The following error has occured: ' || sqlerrm);
END;
/
sql>EXECUTE send_email('me@fictional-domain.com','you@fictional-domain.com','This is a UTL_SMTP-generated email','Hi There !');
Regards,
ROhit Nirkhe,Oracle DBA,OCP 8i
oracle-support@indiatimes.com
-
All that will work if JVM is also installed.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
How can I check that this UTL_SMTP is installed or not..?
Thanks,
Harry
-
Harry,
Complete scripts and PL/SQL procedures could be found
Here
Examples include UTL_SMTP on 8i and 9i.
Hope that helps,
--clio_usa
Senior Oracle DBA
http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi
-----------------------------------------------------------
-
Originally posted by harrygulls
How can I check that this UTL_SMTP is installed or not..?
Thanks,
Harry
Harry, if select count(*) from dba_source where name = 'UTL_SMTP'; returns something like 409, then you have it, if it returns 0, obviously not.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
If you don't currently have the JServer loaded run the following as SYS:
SQL> @$ORACLE_HOME/javavm/install/initjvm.sql
SQL> @$ORACLE_HOME/rdbms/admin/initplsj.sql
http://www.oracle-base.com/Articles/...romPLSQL9i.asp
Cheers
-
Hi All,
Rohit, I tried running the procedure and got some error. Where could I find the Send_header program unit?
Thanks,
Skid
==================================
Errors for PROCEDURE SEND_EMAIL:
LINE/COL ERROR
-------- ----------------------------------------------------------------
15/1 PLS-00201: identifier 'SEND_HEADER' must be declared
15/1 PL/SQL: Statement ignored
16/1 PLS-00201: identifier 'SEND_HEADER' must be declared
16/1 PL/SQL: Statement ignored
17/1 PLS-00201: identifier 'SEND_HEADER' must be declared
17/1 PL/SQL: Statement ignored
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|