SEND_MAIL - ORA-01775: looping chain of synonyms

 In one of our environment, post migration we tested the SEND_MAIL procedure to initiate the mail via SMTP server from DB level and faced the below issue.

ORA-01775: looping chain of synonyms

Looping chain of synonyms means, the created synonyms points to another object in a circle.

Here for better understanding,

1. SEND_MAIL is the procedure used to trigger mails from DB Server
2. UTL_SMTP and DBMS_OUTPUT are the dependent objects for SEND_MAIL procedure
3. UTL_SMTP synonym is also dependent object for SEND_MAIL procedure

So it is clearly evident that the dependent objects should be valid before fixing the issues with the SEND_MAIL procedure.

So, I started the drilldown on the required dependent objects and found the procedure 'SEND_MAIL' and package body of the object 'UTL_SMTP' is invalid.

So I made an attempt to compile the UTL_SMTP body to make it a valid object and faced the below issue.

This issue indicates the format of the wrapped unit being compiled is not understood by the compiler. This may be because the unit was edited or modified after it was wrapped. In our case this is what exactly I did. I copied this packages from some other database and compiled it in this database which leads to this issue. So now the ideal solution would be create this package from this database itself. Also since this UTL_SMTP is a standard package it should remain under SYS schema's ownership. In my case it was under the ownership of custom user 'TFGADMIN'.

So I dropped the package and created it separately in this database as follows.

After I dropped, checked and found only the synonym is available and this is the required result.

So to create the package, run the below scripts as SYSDBA.

After I ran the scripts, found the packages created under the SYS ownership and the status is Valid.

Now the next step is to compile the SEND_MAIL procedure as it was invalid since the dependent objects were Invalid. Now the dependencies are valid, so I compiled the procedure and it becomes a valid object.

Post this, run the below given ACL to complete the mail sending process. 

grant execute on utl_http to TFGADMIN;

BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'ACL_TEST3.xml', description => 'ACL FOR TEST PURPOSES', principal => 'TFGADMIN' ,  is_grant => true,

privilege => 'connect', start_date => null,end_date => null );COMMIT; END;

SELECT DECODE( DBMS_NETWORK_ACL_ADMIN.check_privilege('ACL_TEST5.xml', 'TFGADMIN', 'connect'),1, 'GRANTED', 0, 'DENIED', NULL) privilege FROM dual;

BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'ACL_TEST5.xml', principal => 'TFGADMIN', is_grant => true, privilege => 'connect', position => 1);COMMIT;END;

BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (acl => 'ACL_TEST5.xml', host => '172.17.104.72', lower_port=> null, upper_port => null);COMMIT;END;

Once the ACL is created, I ran the SEND_MAIL procedure and the mail is triggered and the same is received in my mailbox.

****************************நன்றி****************************