Goal / Problem Description
In order to send E-Mails from an Oracle DB using the Oracle PL/SQL package utl_smtp Access Control Lists (ACLs) must be configured.
This, not only for this package but also for other packages like: utm_mail, utl_http, utl_inaddr and utl_tcp
ACLs needs the XML-DB option. XML-DB is known to raise security issues, so it is often avoided.
It also would be an "overkill" to install XML-DB, only for sending mails.
A simple alternative is:
- create your own PL/SQL Package, Procedure or Funktion in the SYS Schema
- make a public alias
- grant the execution right to the DB-Users needing the functionality
Et voilà !
Implementation Suggestion
Implementation suggestion of a package with a function and a procedure to send e-mails.
I suggest prefixing it with a name that helps recognize, that the it does not belongs to the default SYS Schema (here my_ ).
Also document this in order to avoid forgetting it in case of migration.
CREATE OR REPLACE PACKAGE SYS.my_mail_utl AUTHID DEFINER IS
-- Used by: send_mail() and send_mail_fct()
c_res_success CONSTANT PLS_INTEGER:= 0;
c_res_inv_operation CONSTANT PLS_INTEGER:= 1;
c_res_tmp_problem CONSTANT PLS_INTEGER:= 2;
c_res_perm_error CONSTANT PLS_INTEGER:= 3;
/*-------------------------------------------------------
USAGE EXAMPLES
-- Test: Procedure
BEGIN
my_mail_utl.send_mail(
'my.name@my_domain.ch', --Sender
'my.name@my_domain.ch', --Recipient
'Test Subject 1', --Subject
'Test message text 1' --Message
);
END;
-- Test: Function
BEGIN
DBMS_OUTPUT.PUT_LINE ('ResVal: '||
my_mail_utl.send_mail_fct(
'my.name@my_domain.ch', --Sender
'my.name@my_domain.ch', --Recipient
'Test Subject 2', --Subject
'Test message text 2' --Message
));
END;
-------------------------------------------------------*/
/*-------------------------------------------------------
RETURN VALUES
c_res_success Success
c_res_inv_operation Invalid Operation in Mail attempt using UTL_SMTP.
c_res_tmp_problem Temporary e-mail issue - try again
c_res_perm_error Permanent Error Encountered
-------------------------------------------------------*/
FUNCTION send_mail_fct
(
i_sender IN VARCHAR2,
i_recipient IN VARCHAR2,
i_subject IN VARCHAR2,
i_message IN LONG
) RETURN PLS_INTEGER;
/*-------------------------------------------------------
See send_mail_fct()
-------------------------------------------------------*/
PROCEDURE send_mail
(
i_sender IN VARCHAR2,
i_recipient IN VARCHAR2,
i_subject IN VARCHAR2,
i_message IN LONG
);
END my_mail_utl;
/
--=============================================================================
-- PACKAGE BODY
--=============================================================================
CREATE OR REPLACE PACKAGE BODY SYS.my_mail_utl IS
/*-------------------------------------------------------
See package header
-------------------------------------------------------*/
FUNCTION send_mail_fct
(
i_sender IN VARCHAR2,
i_recipient IN VARCHAR2,
i_subject IN VARCHAR2,
i_message IN LONG
) RETURN PLS_INTEGER IS
c_crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
c_mailhost CONSTANT VARCHAR2(30):= 'my_mailhost.my_domain.ch'; -- host mail address
v_mail_conn utl_smtp.connection;
v_mesg long;
BEGIN
v_mail_conn := utl_smtp.open_connection(c_mailhost, 25);
v_mesg:= 'Date: ' || TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss' ) || c_crlf ||
'From: <' || i_sender ||'>' || c_crlf ||
'Subject: ' || i_subject || c_crlf ||
'To: ' || i_recipient || c_crlf ||
c_crlf || i_message;
utl_smtp.helo(v_mail_conn, c_mailhost);
utl_smtp.mail(v_mail_conn, i_sende);
utl_smtp.rcpt(v_mail_conn, i_recipient);
utl_smtp.data(v_mail_conn, v_mesg);
utl_smtp.quit(v_mail_conn);
RETURN c_res_success;
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line('Invalid Operation in Mail attempt using UTL_SMTP.');
RETURN c_res_inv_operation;
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line( 'Temporary e-mail issue - try again');
RETURN c_res_tmp_problem;
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line( 'Permanent Error Encountered.');
RETURN c_res_perm_error;
END send_mail_fct;
/*-------------------------------------------------------
See package header
-------------------------------------------------------*/
PROCEDURE send_mail_smtp
(
i_sender IN VARCHAR2,
i_recipient IN VARCHAR2,
i_subject IN VARCHAR2,
i_message IN LONG
) IS
v_dummy PLS_INTEGER;
BEGIN
v_dummy:= send_mail_fct(i_sender, i_recipient, i_subject, i_message);
END send_mail;
END my_mail_utl;
/
Create a public synonym in order to make the location of the package "my_mail_utl" transparent to the "users".
CREATE OR REPLACE PUBLIC SYNONYM my_mail_utl FOR sys.my_mail_utl;
Give the execution right on the package, function or procedure to the DB-users needing to send emails.
GRANT EXECUTE ON my_mail_utl TO <DB-User>;
For testing as SYS user you can change the session context in SQL*Plus without needing to logon as <DB-User> (what is often not possible if you don't know its passord.)
ALTER SESSION SET CURRENT_SCHEMA=<DB-User>;
Testing the procedure (see package header):
-- Test: Procedure
BEGIN
my_mail_utl.send_mail(
'my.name@my_domain.ch', -- Sender
'my.name@my_domain.ch', -- Recipient
'Test Subject 1', -- Subject
'Test message text 1' -- Message
);
END;
/
Tags: Oracle, PL/SQL, Mail, Publish
January 10, 2013 at 03:15PM
No comments :
Post a Comment