![Designer TAPI triggers fail on SQL MERGE operations 20188367001]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Designer TAPI triggers fail on SQL MERGE operations 1")
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!
To reproduce, you have to do nothing very special:
- Open Designer, create (optionally a new workarea and ) a new application system HRM
- Design Capture DEPT from SCOTT schema into this new application system HRM
- Make column DEPT.DEPTNO mandatory and Add Primary Key (if not yet present)
- Generate Table API (TAPI) for DEPT into another database schema
- Perform a Merge operation on DEPT
merge into DEPT using (select 50 deptno , 'ICING' dname , 'ZOETERMEER' loc from dual ) new_dept on ( new_dept.deptno = dept.deptno ) when matched then update set dept.loc = new_dept.loc when not matched then insert ( deptno, dname, loc) values ( new_dept.deptno, new_dept.dname, new_dept.loc) ;
This statement will fail with an error like:
ERROR at line 2: ORA-06502: PL/SQL: numeric or value error: NULL index table key value ORA-06512: at "AGS.CG$AUS_DEPT", line 17 ORA-04088: error during execution of trigger 'AGS.CG$AUS_DEPT'
Note: a Merge that does both Insert and Update also fails with this error:
merge
into DEPT
using (select 50 deptno
, 'ICING' dname
, 'ZOETERMEER' loc
from dual
union
select deptno
, dname
, loc
from dept
) new_dept
on ( new_dept.deptno = dept.deptno
)
when matched
then update
set dept.loc = new_dept.loc
when not matched
then insert
( deptno, dname, loc)
values
( new_dept.deptno, new_dept.dname, new_dept.loc)
/
into DEPT
*
ERROR at line 2:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "AGS.CG$AUS_DEPT", line 17
ORA-04088: error during execution of trigger 'AGS.CG$AUS_DEPT'
A statement that only Updates during the Merge sails through:
merge
into DEPT
using (select deptno
, dname
, loc
from dept
) new_dept
on ( new_dept.deptno = dept.deptno
)
when matched
then update
set dept.loc = new_dept.loc
when not matched
then insert
( deptno, dname, loc)
values
( new_dept.deptno, new_dept.dname, new_dept.loc)
;
4 Rows Merged
So what is wrong with the TAPI code?
The offending line is easily found:
IF NOT (cg$DEPT.called_from_package) THEN
idx := cg$DEPT.cg$table.NEXT(idx);
cg$rec.DEPTNO := cg$DEPT.cg$table(idx).DEPTNO;
...
The Associative Array (PL/SQL Table) cg$DEPT.cg$table is addressed with an index idx that is NULL. Apparently, the programmers of this TAPI code had not foreseen that it could be NULL at this stage. So what is so special about the MERGE operation? Well, the main characteristic relevant to this situation is that MERGE fires both INSERT and UPDATE statement level triggers, or in fact all four of them. You will have BEFORE INSERT statement, BEFORE UPDATE statement as well as AFTER INSERT and AFTER UPDATE statement level triggers fire in case of a MERGE: even if there are only rows inserted OR only rows updated. It seems that the TAPI designers did not anticipate this situation.
The Before Update Row level trigger that would typically fire for every record – in the expectation of the AFTER UPDATE statement TAPI trigger – creates two entries in the CG$TABLE, one with the old values and one with the new values. Note that this second record is created inside the TAPI package, the upd procedure. The After Update statement level trigger therefore expects there to be at least two entries in the CG$TABLE – as it is fired and the CG$TABLE is not completely empty, it assumes that at least one record has been updated and therefore surely there must be at least two entries in the CG$TABLE. With that assumption it then fails if the CG$TABLE contains only a single record – which was the case with our initial MERGE. Would we try this – we do not get an error:
merge
into DEPT
using (select 50 deptno
, 'ICING' dname
, 'ZOETERMEER' loc
from dual
UNION ALL
select 60 deptno
, 'ON THE CAKE' dname
, 'NIEUWEGEIN' loc
from dual
) new_dept
on ( new_dept.deptno = dept.deptno
)
when matched
then update
set dept.loc = new_dept.loc
when not matched
then insert
( deptno, dname, loc)
values
( new_dept.deptno, new_dept.dname, new_dept.loc)
/
2 rows merged
This may look good, but is in fact quite spectacularly wrong: because we inserted two records, the After Update Statement TAPI trigger does not fail: it finds an even number of records, namely two. However, it assumes that this means that one record has been updated and the second entry in CG$TABLE contains the new values with the first record holding the old values. This when no record at all was updated!
Were I a to add another record to the Merge operation, bringing the total number of records inserted again to an odd number, the TAPI will fall over again:
merge
into DEPT
using (select 50 deptno
, 'ICING' dname
, 'ZOETERMEER' loc
from dual
UNION ALL
select 60 deptno
, 'ON THE CAKE' dname
, 'NIEUWEGEIN' loc
from dual
UNION ALL
select 70 deptno
, '!!!' dname
, 'HOEVELAKEN' loc
from dual
) new_dept
on ( new_dept.deptno = dept.deptno
)
when matched
then update
set dept.loc = new_dept.loc
when not matched
then insert
( deptno, dname, loc)
values
( new_dept.deptno, new_dept.dname, new_dept.loc)
/
ERROR at line 2:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "AGS.CG$AUS_DEPT", line 22
Conclusions so far: The Designer TAPI cannot handle MERGE statements. That is: if the MERGE does no insert at all, but only updates, you are fine. No error and no erroneous processing. However, if there is insert activity as part of the Merge, things go wrong. Nothing apparently will go wrong if the number of records inserted is EVEN: you will have no exceptions like the one shown above. However, what the After Update Statement TAPI trigger does is wrong. It will do superfluous validations making wrong assumptions. At the least it does unnecessary processing but more likely it will do wrong things like disapprove of what it thinks is an erroneous update while it was in fact dealing with a combination of two inserted records. If the number of records inserted is ODD, you will run into the ORA-6502 exception.
See for an example and more in depth discussion on Merge and Statement Level triggers this thread on Tom Kyte’s AskTom website: http://asktom.oracle.com/pls/ask/f?p=4950:8:3485730494385186442::::F4950_P8_DISPLAYID:25733900083512.
Bug and iTAR
After writing this post, I started looking at MetaLink – I know it, wrong order. And I quickly found a bug registered already: 3182094 ERROR ON TRIGGER EXECUTION WHEN USING MERGE-STATEMENT WITH TABLE API. For some bizarre reason – mainly because it was felt that resolving this issue would take a lot of time – it was set to “Desirability Very desirable feature , Status To Internal (Oracle) Review” – instead of 11 – BUG – as it should have been. The bug was recorded as early as August 2003 and most recently updated in September of 2003. Ever since, al has been very quiet. I am not sure by the way that the analysis in this bugreport is completely correct. I have created a new TAR: 4764461.993. I am curious to see what level of support is still available for Designer, as this is clearly a serious issue!
The Solution
The most obvious thing YOU can do to not run into these problems, is to avoid the usage of MERGE – or of course the usage of the Table API. Avoiding the Merge in the example above is quite simple using old-fashioned combinations of INSERT and UPDATE:
update dept
set loc = ( select loc
from ( select 50 newdeptno
, 'ICING' dname
, 'ZOETERMEER' loc
from dual
) new_dept
where deptno = new_dept.newdeptno
)
where exists ( select 1
from ( select 50 newdeptno
, 'ICING' dname
, 'ZOETERMEER' loc
from dual
) new_dept
where deptno = new_dept.newdeptno
)
/
insert into dept
(deptno, dname, loc)
select deptno, dname, loc
from ( select 50 deptno
, 'ICING' dname
, 'ZOETERMEER' loc
from dual
) new_dept
where not exists
( select 1
from dept d2
where d2.deptno = new_dept.deptno
)
/
Quite clearly that is not ideal – Oracle did not add the MERGE statement for nothing. So what would have to change in the TAPI to make it work after all? That obviously is the other workaround, apart from not using the TAPI at all.
It is simple to prevent the exception from occurring: have the After Update Statement trigger check whether idx is null instead of just assuming that it has a value as surely there always is an even number of records. However, that does not remove the logical issue, namely the processing of new, inserted records as if they are updated records – old and new values. So we need to know in the After Update Statement trigger what operation took place on a record, before diving into processing it as Updated – and by the way in the After Insert statement trigger as well because that one may too process records as inserted that are in fact updated. And in 10g we can even add a Delete clause to the Merge statement, meaning that the we also have to fix the After Delete statement trigger.
The solution probably is the following:
1)Extend the definition of cg$row_type – the building block for cg$table – in the TAPI Package Specification with a field specifying the DML operation:
TYPE cg$row_type IS RECORD (DEPTNO cg$row.DEPTNO%TYPE ,DNAME cg$row.DNAME%TYPE ,LOC cg$row.LOC%TYPE ,the_rowid ROWID ,dml_operation varchar2(1) -- values I, U, D ) ;
2) Add logic to the Before Row Insert, Update and Delete triggers to explicitly set the value of dml_operation:
cg$DEPT.cg$table(cg$DEPT.idx).DML_OPERATION = 'U'; -- in Before Row Update
cg$DEPT.cg$table(cg$DEPT.idx).DML_OPERATION = 'I'; -- in Before Row Insert
cg$DEPT.cg$table(cg$DEPT.idx).DML_OPERATION = 'D'; -- in Before Row Delete
note: the record that is being added to cg$DEPT.cg$table inside the TAPI package during the update operation will not have a value for this field.
3) Add logic to the After Insert, Update and Delete statement TAPI trigger to check for the value of DML_OPERATION and only process the record when it has the appropriate value:
BEGIN
WHILE idx IS NOT NULL LOOP
if cg$DEPT.cg$table(idx).dml_operation = 'U'
then
...
Naturally, the Designer maintenance team should create this fix for us. Perhaps instead we can write a post-generation batch program to change all generated TAPI package specifications and triggers. I was thinking out load: dbms_meta.get_ddl, execute immediate etc. Perhaps even some Regular Expressions….
