RE: ACL Lists and UTL_MAIL FAIL when executed from a stored procedure.
Date: Tue, 12 Mar 2013 13:44:08 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E885830467C6_at_NADCWPMSGCMS10.hca.corpad.net>
Why would it be rare in 11g? I know we do it here and my last position made use of it as well (both in 10g) and you've piqued my curiosity.
Typically used by a SERVICE account in the db that does monitoring and "stuff" - always handy to get an email notification and output.
Chris
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Patterson, Joel
Sent: Tuesday, March 12, 2013 1:17 PM
To: ORACLE-L
Subject: RE: ACL Lists and UTL_MAIL FAIL when executed from a stored procedure.
Update:
Looks like the problem of sending mail using UTL_MAIL from a stored procedure in 11g can be accomplished by using the 'authid current_user' clause. I suppose the problem is solved -- in that there may not be any bug... but that has not been confirmed.
Using UTL_MAIL from within a stored procedure in 11g appears to be rare.
I would be interested in any comments.
create or replace procedure purge_test_with_exec_immed authid current_user as
sender VARCHAR2(30) := 'DBAGROUP_at_crowley.com';
ccrecipient VARCHAR2(240) := NULL;
bccrecipient VARCHAR2(240) := NULL;
subject VARCHAR2(80) := 'Purge Audit Data from stored procedure: ';
message VARCHAR2(1200) := 'UTL MAIL MESSSAGE FROM STORED PROCEDURE';
header VARCHAR2(120) := 'UTL MAIL HEADER FROM STORED PROCEDURE';
mime_type VARCHAR2(30) := 'text; charset=us-ascii';
priority NUMBER := 3;
recipient VARCHAR2(30) := 'jpatterson_at_entint.com';
smtp_out_server VARCHAR2(30) := 'jaxsmtp01.crowley.com'; BEGIN
-- EXECUTE IMMEDIATE 'ALTER SYSTEM SET smtp_out_server = "'||smtp_out_server||'"';
UTL_MAIL.send(
sender => sender, recipients => recipient, cc => ccrecipient, bcc => bccrecipient, subject => subject, message => message, mime_type => mime_type, priority => priority);
END;
/
Joel Patterson
Database Administrator
904 928-2790
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 12 2013 - 19:44:08 CET