PL/SQL post compiler to fix TAPI – dealing with the 'TAPI cannot deal with MERGE' problem

Recently I wrote a post about the failure of the TAPI (table API) generated by Oracle Designer to work together with Merge operations: Designer TAPI triggers fail on SQL MERGE operations. In that article, I have analyzed the problem as well as proposed a solution, i.e. a modification to the generated TAPI code. That change should of course be made to the TAPI generator in the core Oracle Designer product by the Oracle Designer maintenance team. However, as this bug has been known by Oracle since 2003 and nothing seems to be done about it, it would be naive to expect an imminent solution thence. Alternatively, we could manually modify every TAPI package specification as well as six of the TAPI triggers – for every table in our application. That is hardly an appealing solution either.

So I set down to write a relatively simple and small piece of code that we can use as post compiler or post generator. After we generated one or more TAPIs, we can run this post compiler to remedy the problem the TAPI has with Merge operations. The post compiler will fix the PL/SQL inside the database. It reads the TAPI objects using dbms_metadata, makes the simple change by manipulating the PL/SQL source and then recreates the objects using execute immediate. Using this post compiler, it takes but a few seconds to fix the merge flaw in all TAPIs in your application.
So what exactly does this post compiler or tapi_merge_enabler change in the generated code?

1. It extends the cg$row_type definition in the TAPI package specifcation by adding an extra field: dml_operation varchar2(1). This field will hold the type of DML operation for each record processed by the API in the current DML statement
2. In all TAPI Before Record triggers (CG$BUR_table, CG$BIR_table and CG$BDR_table), it adds one line of code that sets the dml_operation to either I, U or D: cg$rec.dml_operation:= 'U'; -- added by post compiler TAPI_MERGE_ENABLER
3. In all TAPI After Statement triggers (CG$AUS_table, CG$AIS_table and CG$ADS_table), it adds a little logic to check whether the record looked at in a certain iteration over the cg$table_name.cg$table was manipulated by the type of dml_operation that this statement level trigger represents: if cg$rec.dml_operation = 'U' -- added by post compiler TAPI_MERGE_ENABLER

Together these changes fix the flaw that I previously described with the TAPI with regard to Merge operations.

How to install and run?

1) Download the code for the post_compiler package
2) Install the package in the schema that contains the TAPIs
3) Run the package for each Table that has a TAPI installed:

begin
  post_compiler.tapi_merge_enabler
  ( p_tbl_name=> 'NAME_OF_YOUR_TABLE');
end;
/

4) Recompile invalid objects
5) Test

Example

Suppose I do the following, using the SCOTT sample schema:

• 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 deptno, dname from dept union select 11 deptno , 'HOI' dname from dual) nd
   on (dept.deptno = nd.deptno)
   when matched then update set dname = nd.dname||'X'
   when not matched
   then insert (deptno, dname) values (nd.deptno, nd.dname)
    ;
  

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'

Now run the post compiler’s tapi_merge_enabler:

begin
  post_compiler.tapi_merge_enabler( p_tbl_name=> 'DEPT');
end;
/

and try the Merge statement again:

 merge into dept
 using (select deptno, dname from dept union select 11 deptno , 'HOI' dname from dual) nd
 on (dept.deptno = nd.deptno)
 when matched then update set dname = nd.dname||'X'
 when not matched
 then insert (deptno, dname) values (nd.deptno, nd.dname)
  ;
5 Rows Merged
...

So at least our fix took away the nasty exception. This does not prove it actually corrected the TAPI in full, but I think it probably did.

The Post Compiler

CREATE OR REPLACE package body post_compiler
as
procedure tapi_merge_enabler
( p_tbl_name in varchar2
) ;
end post_compiler;
/
CREATE OR REPLACE package body post_compiler
as
procedure tapi_merge_enabler
( p_tbl_name in varchar2
)
is
  l_code clob;
  l_pos number;
  l_pos2 number;
  procedure enable_tapi_package
  is
  begin
    -- make sure that dbms_metadata does not return the package body as well
    DBMS_METADATA.SET_TRANSFORM_PARAM
    ( transform_handle  => dbms_metadata.SESSION_TRANSFORM
    , name             => 'BODY'
    , value           => false
    , object_type     =>  'PACKAGE'
    );
    -- get the definition of the TAPI package specification
    l_code:= dbms_metadata.get_ddl('PACKAGE','CG$'||p_tbl_name,USER);
    -- find the start of the cg$row_type record definition
    l_pos:= instr(l_code, 'TYPE cg$row_type IS RECORD');
    -- now find TYPE cg$ind_type - the type definition following the cg$row_type
    l_pos2:= instr(l_code, 'TYPE cg$ind_type', l_pos);
    -- now find ROWID, the last field in that record definition
    l_pos:= instr(l_code, 'ROWID)', l_pos);
  -- note: if l_pos =0 that means ROWID) is not found; that probably indicates that dml_operation was already inserted in between
  if l_pos > 0 and l_pos < l_pos2 -- we do not want to end up modifying the TYPE cg$pk_type
  then
      -- now insert , dml_operation varchar2(1) at this position, as an extra and the last field in cg$row_type
      l_code:= substr(l_code, 1, l_pos+4)
             ||chr(10)||', dml_operation varchar2(1) -- added by post compiler TAPI_MERGE_ENABLER'||chr(10)
             ||substr(l_code, l_pos+5);
      -- at this point, recreate the package specification with the slightly modified code
      execute immediate cast(l_code as varchar2);
  end if; -- l_pos>0
  end enable_tapi_package;
  --
  procedure enable_tapi_trigger
  ( p_dml_type in varchar2 -- I, U or D
  , p_level in varchar2 -- BR or AS
  ) is
    l_trg_name varchar2(32):= 'CG$'||substr(p_level,1,1)||p_dml_type||substr(p_level,2,1)||'_'||p_tbl_name;
  begin
    -- get the definition of the TAPI package specification
    l_code:= dbms_metadata.get_ddl('TRIGGER',l_trg_name,USER);
    if p_level ='BR'
    then
      -- find the position where values are being copied to cg$rec
      l_pos:= instr(l_code, 'cg$rec.');
      -- now check of our line is already in there
      l_pos2:= instr(l_code, 'cg$rec.dml_operation', l_pos);
      -- if l_pos2 > 0 then our code is already in place. Let's just abort this procedure
      if l_pos2 > 0
      then
        null;
      else
        if l_pos > 0
        then
          -- now insert just prior to l_pos: cg$rec.dml_operation:= 'U'; chr(10)
          l_code:= substr(l_code, 1, l_pos-1)
                 ||chr(10)||'    cg$rec.dml_operation:= '''||p_dml_type||''';  -- added by post compiler TAPI_MERGE_ENABLER '||chr(10)||'    '
                 ||substr(l_code, l_pos);
          l_pos2:= instr(l_code, 'ALTER TRIGGER');
          -- at this point, recreate the trigger with the slightly modified code
          execute immediate cast(substr(l_code,1,l_pos2-1)        as varchar2);
        end if; -- l_pos>0
      end if; -- l_pos2>0
    end if; -- p_level ='BR'
    --
    if p_level ='AS'
    then
      -- find the position where the cg$...cg$table is beging traversed
      l_pos:= instr(l_code, 'WHILE idx IS NOT NULL LOOP');
      -- now check of our line is already in there
      l_pos2:= instr(l_code, 'cg$rec.dml_operation', l_pos);
      -- if l_pos2 > 0 then our code is already in place. Let's just abort this procedure
      if l_pos2 > 0
      then
        null;
      else
        if l_pos > 0
        then
          -- now insert just following the search string: chr(10)          if cg$rec.dml_operation = 'U'chr(10)          then
          l_code:= substr(l_code, 1, l_pos+26)
                 ||chr(10)||'      if cg$rec.dml_operation = '''||p_dml_type||'''  -- added by post compiler TAPI_MERGE_ENABLER'
                 ||chr(10)||'      then '
                 ||chr(10)||'             '
                 ||substr(l_code, l_pos+27);
        end if;
      end if;
      -- find the position where the cg$...cg$table is beging traversed
      if p_dml_type='U'
      then
        -- now check of our second line is already in there
        l_pos2:= instr(l_code, 'END IF;', l_pos,2);
        l_pos:= instr(l_code, 'idx := cg$'||p_tbl_name||'.cg$table.NEXT(idx)', 1,2);
      else
        l_pos2:= instr(l_code, 'END IF;', l_pos,2);
        l_pos:= instr(l_code, 'idx := cg$'||p_tbl_name||'.cg$table.NEXT(idx)');
      end if;
      if l_pos2 > 0 and l_pos2 < l_pos
      then
        -- apparently we have a end if; statement after if cg$rec.dml_operation and before idx:= ...; that must surely mean
        -- our code is already in place. Let's just abort this procedure
        null;
      else
        l_code:= substr(l_code, 1, l_pos-1)
               ||'END IF; -- added by post compiler TAPI_MERGE_ENABLER'
               ||chr(10)||'        '
               ||substr(l_code, l_pos);
      end if;
      l_pos2:= instr(l_code, 'ALTER TRIGGER');
      -- at this point, recreate the trigger with the slightly modified code
      execute immediate cast(substr(l_code,1,l_pos2-1)        as varchar2);
    end if; -- p_level ='AS'
  end enable_tapi_trigger;
  --
begin
  enable_tapi_package;
  -- do the triggers:
  enable_tapi_trigger( p_dml_type => 'I', p_level   => 'BR');
  enable_tapi_trigger( p_dml_type => 'U', p_level   => 'BR');
  enable_tapi_trigger( p_dml_type => 'D', p_level   => 'BR');
  enable_tapi_trigger( p_dml_type => 'I', p_level   => 'AS');
  enable_tapi_trigger( p_dml_type => 'D', p_level   => 'AS');
  enable_tapi_trigger( p_dml_type => 'U', p_level   => 'AS');
end tapi_merge_enabler;
end post_compiler;
/