Sending emails from….ORACLE? (11g)

send-angry-email-400x400

 

 

 

 

 

Sometimes u will need some function for send email
from pl/sql.  Good, here we have the way to do it:

begin
sys.utl_mail.send(sender => ‘samuel.guinales@fujitsu.es’ ,
recipients => ‘samuel.guinales@gmail.com’ ,
subject => ‘testing mails ORACLE’ ,message => ‘This is the test message from oracle server’ );
end;
/

But first of all, you must install the utl_smtp package and Jserver by running the following
DBA scripts as SYSDBA (SYS user):

I. PREREQUSITES
1) Server
ALTER SYSTEM SET SMTP_OUT_SERVER=”[IP_SMTP_SERVER]” scope=both;

2) XML y JAVA

check:
SET LINES 120
SET PAGES 1000
COl COMP_NAME FOR A60
SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;

– you have to watch this:

JServer JAVA Virtual Machine 11.2.0.3.0 VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
Oracle XML Database 11.2.0.3.0 VALID

Run this script the UTL_SMTP package requires Jserver which c
an be installed by running the following scripts as SYS or run
all from one script i writed down(full_jvminst.sql)

—- full_jvminst.sql
SPOOL full_jvminst.log;
SHUTDOWN IMMEDIATE
SET ECHO ON
STARTUP MOUNT
ALTER SYSTEM SET “_system_trig_enabled” = false scope=memory;
ALTER DATABASE OPEN;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
@?/dbhome_11203/javavm/install/initjvm.sql
select count(*), object_type from all_objects
where object_type like ‘%JAVA%’ group by object_type;
@?/dbhome_11203/xdk/admin/initxml.sql
select count(*), object_type from all_objects
where object_type like ‘%JAVA%’ group by object_type;
@?/dbhome_11203/xdk/admin/xmlja.sql
select count(*), object_type from all_objects
where object_type like ‘%JAVA%’ group by object_type;
@?/rdbms/admin/catjava.sql
select count(*), object_type from all_objects
where object_type like ‘%JAVA%’ group by object_type;
SHUTDOWN IMMEDIATE
SET ECHO OFF
SPOOL OFF
EXIT
— End of File full_jvminst.sql
— Start of XML DB Install
STARTUP
SPOOL xmldb_install.log
@?/rdbms/admin/catqm.sql temporal01 SYSAUX TEMP NO
SPOOL OFF
— End of XML DB Install

3) Now install UTL_MAIL package

The UTL_MAIL package provides a simple API to allow email to be sent from PL/SQL. In prior versions this was possible using the UTL_SMTP package , but this required knowledge of the SMTP protocol.

The package is loaded by running the following scripts.

@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb
GRANT EXECUTE ON SYS.UTL_MAIL TO [USER_RUN_PACKAGE];
2) ACLs
Now UTL_MAIL after 11g need set Access Control List:

Example: For user SYS and pepito, setting of smtp_ip_adress too.

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => ‘UTL_SMTP.xml’,
description => ‘Granting privs to required users for UTL_SMTP.xml’,
principal => ‘SYS’,
is_grant => TRUE,
privilege => ‘connect’);
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl => ‘UTL_SMTP.xml’,
principal => ‘SYS’,
is_grant => TRUE,
privilege => ‘resolve’);
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl => ‘UTL_SMTP.xml’,
principal => ‘pepito’,
is_grant => TRUE,
privilege => ‘connect’);
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl => ‘UTL_SMTP.xml’,
principal => ‘pepito’,
is_grant => TRUE,
privilege => ‘resolve’);
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => ‘UTL_SMTP.xml’,
host => ‘[IP_SMTP_SERVER]’);
END;
/
commit;
/
grant execute on UTL_SMTP to pepito;
grant execute on UTL_SMTP to SYS;
/

— check:
select * from user_network_acl_privileges;
select * from dba_network_acls;

SELECT acl,
host,
DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(‘0A94B5BD4CB6422C8B56878FB07B9B3D’,’pepito’, 1, ‘GRANTED’, 0, ‘DENIED’, NULL)) privilege
FROM dba_network_acls;

— For Delete
exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL(‘/sys/acls/UTL_SMTP.xml’);
exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL(‘/sys/acls/UTL_HTTP.xml’);

–Additional users to send mail, use the ADD_PRIVILEGE procedure of the DBMS_NETWORK_ACL_ADMIN package.

exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl=>’UTL_SMTP.xml’, principal=>'[ADDITIONAL_USER]’, is_grant=>TRUE, privilege=> ‘connect’);

II. Doing some testing:
1) conection to server smtp service port 25:

example by tomkyte:
web: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2118740358226

$ telnet aria-dev 25
Trying 138.2.5.52…
Connected to aria-dev.us.oracle.com.
Escape character is ‘^]’.
220 aria-dev.us.oracle.com ESMTP Sendmail 8.9.3+Sun/8.9.1; Mon, 3 Dec 2001 09:41:19 -0500 (EST)
helo aria-dev
250 aria-dev.us.oracle.com Hello aria-dev.us.oracle.com [138.2.5.52], pleased to meet you
mail from: tkyte@us.oracle.com
250 tkyte@us.oracle.com… Sender ok
rcpt to: Thomas.Kyte@oracle.com
250 Thomas.Kyte@oracle.com… Recipient ok
data
354 Enter mail, end with “.” on a line by itself
this is a test
.
250 JAA17566 Message accepted for delivery
quit
221 aria-dev.us.oracle.com closing connection
Connection closed by foreign host.

 

2) Testing on oracle, two ways:

— Using Package
begin
sys.utl_mail.send(sender => ‘samuel.guinales@fujitsu.es’ ,
recipients => ‘samuel.guinales@gmail.com’ ,
subject => ‘testing mails ORACLE’ ,message => ‘This is the test message from oracle server’ );
end;
/

— With a function:

create or replace procedure send_mail
( p_sender in varchar2,
p_recipient in varchar2,
p_message in varchar2 )
as
l_mailhost varchar2(255) := ‘wksmtphub.wk.dcx.com’ ;
l_mail_conn utl_smtp.connection ;
begin
l_mail_conn := utl_smtp.open_connection(l_mailhost, 25) ;
utl_smtp.helo(l_mail_conn, l_mailhost) ;
utl_smtp.mail(l_mail_conn, p_sender) ;
utl_smtp.rcpt(l_mail_conn, p_recipient ) ;
utl_smtp.open_data(l_mail_conn) ;
utl_smtp.write_data(l_mail_conn, p_message ) ;
utl_smtp.close_data(l_mail_conn) ;
utl_smtp.quit(l_mail_conn );
dbms_output.put_line(‘Message send to the user successfully’) ;
end ;

The procedure compiled without any error. I call the above procedure in the same user as :

begin
send_mail(‘oracle@fujitsu.es’,
‘samuel.guinales@gmail.com’,
‘This is the test message from oracle server’ ) ;
end ;

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s