Drop public synonym XMLCONCAT prior to upgrade

Thanks to my team mates, Cindy and Hector, who alerted me on this newly detected issue. When your database has been upgraded from release to release, it could happen that a public synonym XMLCONCAT exists. And before you upgrade to Oracle 12.2.01 or Oracle 18c you must drop this public synonym to avoid upgrade errors.

Drop public synonym XMLCONCAT prior to upgrade

The public synonym XMLCONCAT in pre-9.2.0.2 Oracle databases pointed to a PL/SQL function of the same name. In 9.2.0.2, this PL/SQL function was changed to a C function.The public synonym was no longer needed nor pointing to a valid object. Unfortunately we didn’t drop it correctly. Hence you can still find it in some post-9.2 databases. If the orphan synonym still exists in current database, any Oracle references to the C function XMLCONCAT can be referencing the orphan synonym instead of the valid C function. You need to drop this public synonym prior to upgrading the database.

Drop public synonym XMLCONCAT prior to upgrade

And of course we are embedding a check into the preupgrade.jar scripts as well as into ORAchk.

How to …

select count(*) from dba_objects where object_name = 'XMLCONCAT' and object_type='SYNONYM';

If you get a result of <> 0, then drop the public synonym:

sqlplus / as sysdba

drop public synonym xmlconcat;

You may need to recompile invalid objects afterwards.

This affects all upgrades until including Oracle 18c. It will be fixed for upgrades to Oracle 19c.

Further Information

Please see also:

–Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.