Beware the compound trigger

In 11g, Oracle introduced a new feature in the compound trigger.

Through many years and versions, the solution to the mutating table / ora-04091 problem was often a clumsy combination of packaged global, before statement trigger, after row trigger and after statement trigger.

However, in 11g, the introduction of the compound trigger provided a much more compact solution, where you could declare your global, your statement level logic and any row level code in a single trigger.

Examples from the Oracle documentation:

Trouble is, under certain simple circumstances, they don’t work.

Take this illustration abstracted from a proposed VPD labelling solution on 11.1.0.6.

The illustration is a little artificial because all distractions have been removed but it’s taken from a real world example.

Essentially, three updates via a package cause an ORA-07445 but three direct update statements do not. Take a look:


dominic@DOM11G>prompt ---------- cleanup previous creates
---------- cleanup previous creates
dominic@DOM11G>drop table compound_trigger_test_tab1;

Table dropped.

Elapsed: 00:00:01.02
dominic@DOM11G>drop package compound_trigger_test_pkg;

Package dropped.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>prompt ---------- create table
---------- create table
dominic@DOM11G>
dominic@DOM11G>create table compound_trigger_test_tab1
  2  (col1 number
  3  ,col2 number);

Table created.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>prompt ---------- insert data
---------- insert data
dominic@DOM11G>
dominic@DOM11G>insert into compound_trigger_test_tab1 values (1,1);

1 row created.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>
dominic@DOM11G>prompt ---------- create compound trigger
---------- create compound trigger
dominic@DOM11G>
dominic@DOM11G>CREATE OR REPLACE TRIGGER compound_trigger_test_tri1
  2  FOR INSERT OR UPDATE ON compound_trigger_test_tab1
  3  REFERENCING NEW AS NEW OLD AS OLD
  4  COMPOUND TRIGGER
  5   --
  6   TYPE r_ctt IS RECORD 
  7   (col1    NUMBER
  8   ,col2    NUMBER);
  9   --
 10   TYPE t_ctt IS TABLE OF r_ctt
 11   INDEX BY PLS_INTEGER;
 12   --
 13   vt_ctt t_ctt;
 14   --
 15  BEFORE STATEMENT
 16  IS
 17  BEGIN
 18     --
 19     DBMS_OUTPUT.PUT_LINE('compound_trigger_test_tri1::before statement');
 20     NULL;
 21     --
 22  END BEFORE STATEMENT;
 23  --
 24  BEFORE EACH ROW
 25  IS
 26  BEGIN
 27      --
 28      DBMS_OUTPUT.PUT_LINE('compound_trigger_test_tri1::before row');
 29      NULL;
 30      --
 31  END BEFORE EACH ROW;
 32  --
 33  AFTER EACH ROW
 34  IS
 35  BEGIN
 36     --
 37     DBMS_OUTPUT.PUT_LINE('compound_trigger_test_tri1::after row');
 38     NULL;
 39     --
 40  END AFTER EACH ROW;
 41  --
 42  AFTER STATEMENT
 43  IS
 44    i PLS_INTEGER;
 45  BEGIN
 46     --
 47     DBMS_OUTPUT.PUT_LINE('compound_trigger_test_tri1::after statement');
 48     i := vt_ctt.FIRST;
 49     --
 50     WHILE (i IS NOT NULL)
 51     LOOP
 52         --
 53         NULL;
 54         --
 55         i := vt_ctt.NEXT(i);
 56         --
 57     END LOOP;
 58     --
 59  END AFTER STATEMENT;
 60  --
 61  END compound_trigger_test_tri1;
 62  /

Trigger created.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>show errors
No errors.
dominic@DOM11G>
dominic@DOM11G>
dominic@DOM11G>prompt ---------- create package header
---------- create package header
dominic@DOM11G>
dominic@DOM11G>CREATE OR REPLACE PACKAGE compound_trigger_test_pkg
  2  AS
  3    --
  4    PROCEDURE p_update_tab1 (
  5      i_col1                     in     compound_trigger_test_tab1.col1%TYPE
  6    );
  7    --
  8  END;
  9  /

Package created.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>
dominic@DOM11G>prompt ---------- create package body
---------- create package body
dominic@DOM11G>
dominic@DOM11G>CREATE OR REPLACE PACKAGE BODY compound_trigger_test_pkg
  2  AS
  3    --
  4    PROCEDURE p_update_tab1 (
  5      i_col1                     in     compound_trigger_test_tab1.col1%TYPE
  6    )
  7    AS
  8    BEGIN
  9       --
 10       DBMS_OUTPUT.PUT_LINE('compound_trigger_test_pkg.p_update_tab1');
 11       UPDATE compound_trigger_test_tab1
 12       SET    col2 = col2
 13       WHERE  col1 = i_col1;
 14       --
 15    END p_update_tab1;
 16  END;
 17  /

Package body created.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>
dominic@DOM11G>prompt ---------- run update via package test 1 - should be ok
---------- run update via package test 1 - should be ok
dominic@DOM11G>exec compound_trigger_test_pkg.p_update_tab1(1);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>prompt ---------- run update via package 2 - should be ok
---------- run update via package 2 - should be ok
dominic@DOM11G>exec compound_trigger_test_pkg.p_update_tab1(2);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>prompt ---------- run update via package 3 - should go bang
---------- run update via package 3 - should go bang
dominic@DOM11G>exec compound_trigger_test_pkg.p_update_tab1(3);
BEGIN compound_trigger_test_pkg.p_update_tab1(3); END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


Elapsed: 00:00:00.05
dominic@DOM11G>conn dominic@dom11g
Enter password: *******
Connected.
dominic@DOM11G>UPDATE compound_trigger_test_tab1
  2  SET    col2 = col2
  3  WHERE  col1 = 1;

1 row updated.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>UPDATE compound_trigger_test_tab1
  2  SET    col2 = col2
  3  WHERE  col1 = 2;

0 rows updated.

Elapsed: 00:00:00.00
dominic@DOM11G>
dominic@DOM11G>UPDATE compound_trigger_test_tab1
  2  SET    col2 = col2
  3  WHERE  col1 = 3;

0 rows updated.

Elapsed: 00:00:00.00
dominic@DOM11G>

Alert log and trace file reveal the cause to be an ORA-07445:


Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x448] [PC:0x1B6A3B8, kkxtexe()+988]
Errors in file /app/oracle/diag/rdbms/dom11g/DOM11G/trace/dom11G_ora_4860.trc  (incident=845912):
ORA-07445: exception encountered: core dump [kkxtexe()+988] [SIGSEGV] [ADDR:0x448] [PC:0x1B6A3B8] [Address not mapped to object] []
ORA-21779: duration not active

So, for the moment, I would not go near them.
I’ve got an SR raised (it’s all I seem to doing these days is find bugs in 11.1.0.6 (more on the incompatibility between VPD and Materialized Views another time) and 11.1.0.7)
It’s back to multiple row and statement level triggers.

11.1.0.7 Follow up

As a follow-up to Friday’s post about some local issues with the 11.1.0.7 patch, I’ve distilled an isolated piece of SQL to demonstrate the ORA-03113.

Demo test case looks like this:


DOM@11g>l
    WITH subq_goes_bang_data AS
         (SELECT 'CST1' database_name,'-6' ro_id,'\\xxxxx\uts_dev\SplitterToolSystemTestRO1' ros_path
          FROM   dual)
    ,    subq_db_name   AS
         (SELECT DECODE (INSTR (global_name, '.WORLD'),
                         0,global_name,
                         SUBSTR (global_name,1,INSTR (global_name,'.WORLD') - 1)
                        ) database_name
          FROM   global_name)
   ,    subq_goes_bang AS
       (
        SELECT rrc.ro_id,
               rrc.ros_path
        FROM   subq_goes_bang_data rrc
        WHERE rrc.database_name = (SELECT database_name FROM subq_db_name)
        UNION ALL
        SELECT rrc.ro_id,
               rrc.ros_path
        FROM   subq_goes_bang_data rrc
        WHERE  rrc.database_name = 'CST1'
        AND    NOT EXISTS(SELECT NULL
                          FROM subq_goes_bang_data rrc1
                          WHERE rrc1.database_name =(SELECT database_name FROM subq_db_name)
                          AND rrc1.ro_id = rrc.ro_id)
        AND    'CSP1' <>(SELECT database_name FROM subq_db_name)
   )
   SELECT  '<?xml version="1.0" encoding="UTF-16"?>'
           || XMLELEMENT ("LocalConfiguration",XMLFOREST (a.ros_path AS "RootFilePath"))
   FROM    subq_goes_bang a
  WHERE   a.ro_id = -6

DOM@11g>/
WITH subq_goes_bang_data AS
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


Elapsed: 00:00:00.05
DOM@11g>

Comment out the XML bits and bobs and just select the column – no problem.
Comment out the UNION ALL in subq_goes_bang – no problem.

Explain plan looks like this:


DOM@11g>explain plan for
  2  WITH subq_goes_bang_data AS
  3       (SELECT 'CST1' database_name,'-6' ro_id,'\\xxxxx\uts_dev\SplitterToolSystemTestRO1' ros_path 
  4        FROM   dual)
  5  ,    subq_db_name   AS
  6       (SELECT DECODE (INSTR (global_name, '.WORLD'),
  7                       0,global_name,
  8                       SUBSTR (global_name,1,INSTR (global_name,'.WORLD') - 1)
  9                      ) database_name
 10        FROM   global_name)
 11  ,    subq_goes_bang AS
 12      (
 13       SELECT rrc.ro_id,
 14              rrc.ros_path
 15       FROM   subq_goes_bang_data rrc
 16       WHERE rrc.database_name = (SELECT database_name FROM subq_db_name)
 17       UNION ALL
 18       SELECT rrc.ro_id,
 19              rrc.ros_path
 20       FROM   subq_goes_bang_data rrc
 21       WHERE  rrc.database_name = 'CST1'
 22       AND    NOT EXISTS(SELECT NULL
 23                         FROM subq_goes_bang_data rrc1
 24                         WHERE rrc1.database_name =(SELECT database_name FROM subq_db_name)
 25                         AND rrc1.ro_id = rrc.ro_id)
 26       AND    'CSP1' <> (SELECT database_name FROM subq_db_name)
 27  )
 28  SELECT  '<?xml version="1.0" encoding="UTF-16"?>'
 29          || XMLELEMENT ("LocalConfiguration",XMLFOREST (a.ros_path AS "RootFilePath"))
 30  FROM    subq_goes_bang a
 31  WHERE   a.ro_id = -6;

Explained.

Elapsed: 00:00:00.00
DOM@11g>
DOM@11g>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4143783385

----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     2 |    86 |    16   (7)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           |                             |       |       |            |          |
|   3 |    INDEX FULL SCAN         | SYS_IOT_TOP_57625           |     1 |       |     1   (0)| 00:00:01 |
|   4 |   LOAD AS SELECT           |                             |       |       |            |          |
|*  5 |    TABLE ACCESS FULL       | PROPS$                      |     1 |    27 |     2   (0)| 00:00:01 |
|   6 |   VIEW                     |                             |     2 |    86 |    13   (8)| 00:00:01 |
|   7 |    UNION-ALL               |                             |       |       |            |          |
|*  8 |     VIEW                   |                             |     1 |    53 |     2   (0)| 00:00:01 |
|   9 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6643_B0B39986 |     1 |    53 |     2   (0)| 00:00
|  10 |      VIEW                  |                             |     1 |  2002 |     2   (0)| 00:00:01 |
|  11 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6644_B0B39986 |     1 |    27 |     2   (0)| 00:0
|* 12 |     FILTER                 |                             |       |       |            |          |
|* 13 |      HASH JOIN ANTI        |                             |     1 |    57 |     7  (15)| 00:00:01 |
|* 14 |       VIEW                 |                             |     1 |    53 |     2   (0)| 00:00:01 |
|  15 |        TABLE ACCESS FULL   | SYS_TEMP_0FD9D6643_B0B39986 |     1 |    53 |     2   (0)| 00:0
|  16 |       VIEW                 | VW_SQ_1                     |     1 |     4 |     4   (0)| 00:00:01 |
|* 17 |        VIEW                |                             |     1 |    10 |     2   (0)| 00:00:01 |
|  18 |         TABLE ACCESS FULL  | SYS_TEMP_0FD9D6643_B0B39986 |     1 |    53 |     2   (0)| 00:00:01 |
|  19 |         VIEW               |                             |     1 |  2002 |     2   (0)| 00:00:01 |
|  20 |          TABLE ACCESS FULL | SYS_TEMP_0FD9D6644_B0B39986 |     1 |    27 |     2   (0)| 00:00:01 |
|  21 |      VIEW                  |                             |     1 |  2002 |     2   (0)| 00:00:01 |
|  22 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6644_B0B39986 |     1 |    27 |     2   (0)| 00:0
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("NAME"='GLOBAL_DB_NAME')
   8 - filter(TO_NUMBER("RRC"."RO_ID")=(-6) AND "RRC"."DATABASE_NAME"= (SELECT "DATABASE_NAME"
              FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "DATABASE_NAME" FROM
              "SYS"."SYS_TEMP_0FD9D6644_B0B39986" "T1") "SUBQ_DB_NAME"))
  12 - filter( (SELECT "DATABASE_NAME" FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
              "DATABASE_NAME" FROM "SYS"."SYS_TEMP_0FD9D6644_B0B39986" "T1") "SUBQ_DB_NAME")<>'CSP1')
  13 - access("ITEM_0"="RRC"."RO_ID")
  14 - filter("RRC"."DATABASE_NAME"='CST1' AND TO_NUMBER("RRC"."RO_ID")=(-6))
  17 - filter("RRC1"."DATABASE_NAME"= (SELECT "DATABASE_NAME" FROM  (SELECT /*+ CACHE_TEMP_TABLE
              ("T1") */ "C0" "DATABASE_NAME" FROM "SYS"."SYS_TEMP_0FD9D6644_B0B39986" "T1") "SUBQ_DB_NAME")

43 rows selected.

Elapsed: 00:00:00.01
DOM@11g>

So, to Oracle support with some trace files.

But I’d be interested if anyone else with an 11.1.0.7 environment can run the above statement.

Follow

Get every new post delivered to your Inbox.

Join 75 other followers