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

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