Designer Table API (TAPI) incomatible with MERGE statements (ORA-06502)
It turns out that the combination of the Table API (TAPI) as generated by Oracle Designer and MERGE operations using the Oracle 9i SQL Merge statement are not compatible. The generated TAPI trigger code is flawed. Execution of Merge on tables with a TAPI may end in an error like
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "AGS.CG$AUS_DEPT", line 17
...
And even if the Merge does not die with this exception, it is not doing what it should do! In short: if you are using Table APIs, make sure that your code does not contain any MERGE statements. Alternatively: make manual changes to the generated TAPI triggers and TAPI package.
See for a full report: Designer TAPI triggers fail on SQL MERGE operations (http://technology.amis.nl/blog/index.php?p=834) on the AMIS Technology Weblog. Surprisingly enough, this bug has been reported in August 2003 and is "under consideration" ever since!
Perhaps you can help to put some pressure on Oracle product management.
best regards,
Lucas Jellema (AMIS)