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.
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:
- MOS Note: 1271490.1
Upgrade From 10G To 11G Fails On DBMS_SQLTUNE : ORA-04063: Package Body “SYS.DBMS_SQLTUNE” Has Errors - MOS Note: 1912384.1
DBMS_SQLTUNE_INTERNAL invalid and trying to compile getting ORA-00904: XMLCONCAT: invalid identifier error - Bug# 28433703 – DROP PUBLIC SYNONYM XMLCONCAT DURING UPGRADE
–Mike
