RE: ACL Lists and UTL_MAIL FAIL when executed from a stored procedure.

From: <Christopher.Taylor2_at_parallon.net>
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-l

Received on Tue Mar 12 2013 - 19:44:08 CET