PL/SQL: Statement Ignored PLS-00201: identifier 'DBMS_OUTPUT' must be declared

Recently I migrated a database(11gR1) from Windows Platform to 12C on Linux Platform. Migration was successful. During the sanity check, found lot of Invalid objects even after all the grants and privileges were provided. Ran utlrp.sql couple times, still the invalids count remains the same.

So I choose an Invalid object, compiled it manually and faced the below issue.

This was the issue for almost all the invalid objects. So I understood something is not right with the object DBMS_OUTPUT. So I checked this object and to my surprise it was valid.

Since the status is Valid, before doing any change to the object 'DBMS_OUTPUT', I decided to check the coding of the invalid object, since the DBMS_OUTPUT is an oracle standard one.

If I comment the line "dbms_output.put_line" from the invalid object, it is getting compiled and then it is a valid one but this is not the solution. This can be accepted as an workaround when a minimal no.of objects becomes invalid due to this issue. In my case, the invalids count is 226 and it's a humungous task to comment this line in all the coding of those objects. So something must be fishy with the DBMS_OUTPUT object.

Since I know DBMS_OUTPUT is oracle's standard object, I decided to read some oracle documents to get a solution for this. Luckily I got what I looked for.

I ran the script 'dbmsotpt.sql' as SYS user and bingo!!!

Compiled an invalid object manually, and it got compile without any issues.

I ran utlrp.sql to compile all the objects, post which the count got reduced form 226 to 8.

Finally all my issues are resolved.

Ref: https://docs.oracle.com/database/121/ARPLS/d_output.htm#ARPLS67301

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

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.

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

ORA-65040: Operation not allowed from within a pluggable database

 I need to drop some package and synonyms from a PDB. When I attempted to do, I faced the below issue.

Quick surf on the metalink leads to the solution.

Warning: Object owned by SYS user should not be dropped without understanding its impact and proper backup.

When you created the synonym with the schema name also appended with the object name, then it can be dropped like below.

drop public synonym "TFGADMIN.DBMS_OUTPUT";