Designer TAPI triggers fail on SQL MERGE operations 20188367001

Designer TAPI triggers fail on SQL MERGE operations

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….