Error occurs when sending email from database 11g triiger

We had 9i database and we migrated to 11g.
one of the problems that occurred was   executing the "demo_mail" package that sends emails.
The problem
Sending mail via UTL_TCP and UTL_SMTP failes
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115 

Cause :

The Database user which execute the demo_mail package is not authorized to open connection and send a mail.

Solution :

suppose that the user executes the package is "Scott" and  smtp_host is "email2.dhaman.org"

SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'send_mail.xml',description => 'send_mail ACL',principal => 'scott',is_grant => true,privilege => 'connect');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl =>'send_mail.xml',principal => 'scott',is_grant  => true,privilege => 'resolve')

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'send_mail.xml',host => 'email2.dhaman.org')

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

now re execute the package again.It will work.


If you got the error again , follow these steps

as SYS:

exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL('mailserver_acl.xml');      
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('mailserver_acl.xml','Comment','SILVIO',TRUE,'connect');      
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('mailserver_acl.xml','*');      
commit;

as SILVIO:

select utl_inaddr.get_host_address('z-dina-db') from dual;      
SQL> select utl_inaddr.get_host_address('z-dina-db') from dual;      
select utl_inaddr.get_host_address('z-dina-db') from dual      
       *      
ERROR at line 1:      
ORA-24247: network access denied by access control list (ACL)      
ORA-06512: at "SYS.UTL_INADDR", line 19      
ORA-06512: at "SYS.UTL_INADDR", line 40      
ORA-06512: at line 1

as SYS:      
SELECT ACL as ACL_PATH FROM DBA_NETWORK_ACLS      
        WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

     
select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/mailserver_acl.xml','SILVIO','connect') from dual;      
-- returns 1      
select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/mailserver_acl.xml','SILVIO','resolve') from dual;      
-- returns NULL

exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('mailserver_acl.xml','SILVIO',TRUE,'resolve');      
commit;

select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/mailserver_acl.xml','SILVIO','connect') from dual;      
-- returns 1      
select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/mailserver_acl.xml','SILVIO','resolve') from dual;      
-- returns 1

as SILVIO:

SQL>  select utl_inaddr.get_host_address('z-dina-db') from dual;

UTL_INADDR.GET_HOST_ADDRESS('Z-DINA-DB')      
------------------------------------------------------------------------------------------------------------------------------------      
10.223.6.223

 

 

So, only after assigning the 'resolve' privilege in addition to the 'connect' privilege I could successfully use the UTL_INADDR call.

Hope this helps.

 Silviois the user name

Comments

Post a Comment

Popular posts from this blog

Detail entity with row key null cannot find or invalidate its owning entity.

Image
in the model project go to the association. double click on the association and expand the behavior node under relationship tab. make sure that the primary key column is set to DBsequance, if you have a sequence on the database or a function that generate the db sequence from a trigger 3- check on composition association and cascade update key attribute 4- When an entity row is created it's row status is STATUS_NEW . This means row is added in the transaction's list of changes to be validated or posted or committed. This is fine and expected in most of the cases. However, during complex business objects like master-detail there comes the situation where you may want to turn off these validations until both master and detail are available. For such situations to remove the row from transactions's list of changes ADF exposes an api setNewRowStatus(ROW.STATUS_INITIALIZED). This is specifically helpful when master-detail association ...
Read more

Another user has changed the row with primary key oracle.jbo.Key[12 ].

Image
Try these solutions:  make sure that you check "refresh after insert" and refresh after update" for the primary key column in you entity object. in the application module tab Click the Properties tab and select jbo.locking.mode. Double click the “pessimistic”value next to the property to open the field editor and type “optimistic” over the value.Press enter. if error still exist then you must set "refresh after insert" and refresh after update" as true for all attributes in the entity object another way is to execute the View Object after the commit operation to bring back updated data to ViewObject. For this we need to override the  afterCommit()  of ViewObject class as follows:                  public void afterCommit(TransactionEvent){                         executeQuery();                  ...
Read more

setDomainEnv file in weblogic

@ECHO OFF @REM WARNING: This file is created by the Configuration Wizard. @REM Any changes to this script may be lost when adding extensions to this configuration. @REM ************************************************************************* @REM This script is used to setup the needed environment to be able to start Weblogic Server in this domain. @REM @REM This script initializes the following variables before calling commEnv to set other variables: @REM @REM WL_HOME         - The BEA home directory of your WebLogic installation. @REM JAVA_VM         - The desired Java VM to use. You can set this environment variable before calling @REM                   this script to switch between Sun or BEA or just have the default be set. @REM JAVA_HOME       - Location of the version of Java used to start WebLogic @REM                   ...
Read more