If one is found, then the example grants user_name the CONNECT privilege in the ACL only if that user does not already have it. The following example first looks for any ACL currently assigned to host_name. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases. #How To Install Dbms_Network_Acl_Admin Package installIf you have applications that use one of these packages, you must install Oracle XML DB if it is not already installed. Oracle Database 11g Release 1 (11.1) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB. SQL>exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(‘MY.xml’,’*.abc.com’) Ĭonfigure Fine-Grained Access to External Network Services SQL> exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(‘MY.xml’, If above query not show XML DB component then run following sql to install XML DBįollowing example show, how add scott user to ACL SQL> select COMP_NAME, status from dba_registry You need execute addition steps and add user to ACL ( access control list ) #How To Install Dbms_Network_Acl_Admin Package codeNOTE:130785.Oracle 11g and ACL ( access control list ): “If your application code ( package, procedure, function ) uses utl_tcp, utl_smtp etc and you upgraded database to oracle 11g, then that code will not work even you gave execute permission on utl_tcp etc to user #How To Install Dbms_Network_Acl_Admin Package how toNOTE:74269.1 – How to Test an SMTP Mail Gateway From a Command Line Interface TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date, Assign the list to the smtp ( mail server ): # After Feedback that public indeed should be avoided in these matters this is the adepted example: IF you set it up not for public but for dedicated users.Īlter system set smtp_out_server = '.com:25' scope=both Send_mail(p_to => => => 'This is a test message.', The code below shows how the procedure is called. UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf) L_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port) # This is an example to check if it is working: CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2, # Assign the list to the smtp ( mail server ). BEGINĭescription => 'Permissions to access e-mail server.', # Dropping same named access_control list if already existing. # check what is out there in the database. # Execute the following with user SYS as SYSDBA: alter system set smtp_out_server = .com:25' scope=both # Hint: If you do not do this, you will receive “ORA-06502: PL/SQL: numeric or value error” when you try to use the UTL_MAIL package. # Next, add the address & port of the e-mail server (smtp server). sqlplus /nologĬonnect / as execute on utl_mail to public You need to actually install the UTL_MAIL package. # They are not default installed in the database (11g). # In order to send mails in Oracle 11g you will need to take care of several steps. # So that is my first clue Below are the steps you will need to do in the database. Still to me this post offers a nice overview. There is a lot of good notes about that already on the web which i used to get the picture and to get things done. I have been asked to do the steps to send mail from inside an Oracle 11G.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |