EBS 12.2 -- ORA-00907: missing right parenthesis during Adop Cleanup phase

Recently an issue which was reported to me on my forum, perfectly illustrated how a seemingly complex internal error can sometimes be traced back to a very simple syntax conflict, and from there how a syntax conflict may be caused by not following the EBS development and deployment standards. The problem was ORA-00907 during ADOP Cleanup.

Here is the link to that forum thread -> http://erman-arslan-s-oracle-forum.124.s1.nabble.com/Error-duing-CLEANUP-td13293.html

Well, one of my community members hit a wall while running the cleanup phase: adop phase=cleanup cleanup_mode=full. Everything seemed to be going fine until the process halted with error: ORA-00907: missing right parenthesis.

Here is the the error stack:

Statement Handler Error Code: 907

Statement Handler Error Message: ORA-00907: missing right parenthesis
ORA-06512: at "APPS.AD_ZD", line 64
ORA-06512: at "APPS.AD_ZD", line 48
ORA-06512: at "APPS.AD_ZD_TABLE", line 28
ORA-06512: at "APPS.AD_ZD_TABLE", line 2701
ORA-06512: at "APPS.AD_ZD_TABLE", line 2701
ORA-06512: at "APPS.AD_ZD", line 1152
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
Reference of statement-handler is valid, not attempting database reconnect.
Failed to execute SQL statement:
begin
execute immediate 'alter session set DDL_LOCK_TIMEOUT = 600';
ad_zd.cleanup('FULL');
end;

Error Message:
ORA-00907: missing right parenthesis
ORA-06512: at "APPS.AD_ZD", line 64
ORA-06512: at "APPS.AD_ZD", line 48
ORA-06512: at "APPS.AD_ZD_TABLE", line 28
ORA-06512: at "APPS.AD_ZD_TABLE", line 2701
ORA-06512: at "APPS.AD_ZD_TABLE", line 2701
ORA-06512: at "APPS.AD_ZD", line 1152
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute) [UNEXPECTED]Error occurred calling cleanup PL/SQL API.
[UNEXPECTED]Cleanup phase has failed.

At first glance, this is frustrating. You begin with wondering: Did Oracle’s own AD_ZD package have a coding error? Is there a bug in the PL/SQL generated by the system? The stack trace pointed directly to the Online Patching framework...

But after diving the ADZDSHOWLOG, the missing parenthesis error started to make sense. It wasn't actually a missing parenthesis at all. The system was trying to execute an internal DDL to mark columns as unused in a custom schema.

When looked closely at that column name, it was -> SYS_C00019_26042317:24:34$

That is Oracle identifiers cannot contain colons (:)

It appears that during the automated naming process, the system included a timestamp (17:24:34) directly in the column name. Because the SQL parser encountered colons where it expected a closing parenthesis or a valid identifier character, it threw the ORA-00907. It’s a classic case of the error message being a symptom rather than the cause.

Oracle Support usually suggests complex steps for dropping columns from custom tables in 12.2, often involving the AD_ZD utilities. Just -> Review below document for instructions for dropping columns from custom table - Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 [KA1068 ]

Oracle suggested:

You need to review which columns were added on that time to the custom table. Also if any more columns were added to any other custom tables ---> also get those details. Then remove/drop those columns to bring the custom table to its earlier structure. Review below document for instructions for dropping columns from custom table -Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 [KA1068 ]

However, before going further with the whole recommendation, the issue reached a self-resolved status. Since the table in question was a staging table in the custom schema used for data interfaces, it didn't contain permanent, mission-critical data. My follower took the most logical and efficient route: he dropped the table entirely :) By removing the problematic object, the adop cleanup process no longer had to deal with the invalidly named column, and the next run finished successfully.

My follower found the solution for this by himself, but I wanted to share this, because It is as a good reminder for anyone managing custom schemas in EBS 12.2.

Once again, we see that the Online Patching infrastructure is very sensitive to how custom tables are handled. If a custom table isn't properly registered or handled via AD_ZD during structural changes, you might end up with these strange internal naming conflicts. 

And, once again we felt the importance of checking the logs, not just the error: The ORA-00907 was generic. The real story was in the ADZDSHOWLOG output...