Auditing a Oracle table as XML using trigger and Oracle Advanced Queue(AQ)

Recently in one of my integration project where we need to audit any DML action taking place in a table, i.e. if a table row is inserted , updated or deleted we need to  convert the affected row  with all its data to XML and enqueue it to an Oracle Advanced Queue(AQ). which will be later dequeued and saved to SQL Server  tables

 The data in the AQ will be then dequeued by an .net service

We decided we will do the below task

  1. For Insert and Update we will convert the data after action (new) to XML and ENQUEUE
  2. For Delete we don’t have new data so we will convert old data to XML and ENQUEUE

 

 I will try to explain the same step by step

Let’s start by creating a table for Audit

 CREATE TABLE "MYORACLE_INSTANCE"."PROJ_TEST_DEMO"

   (            "ID" NUMBER NOT NULL ENABLE,

            "ADDEDDATE" DATE,

            "PRICE" FLOAT(126),

            "NAME" VARCHAR2(200) NOT NULL ENABLE,

            "DEC" VARCHAR2(200)

   )

 

Now follow the below steps to set up the Oracle AQ and trigger for Enqueue

Step 1: Creating Object Types

Oracle implements Objects through the use of TYPEs,  An object type can be stored in the database for later use. The definition of the type contains a comma separated list of attributes/properties,-Create an type in the format required

create or replace TYPE TRIGGER_AUDIT_TYPE  AS OBJECT

(

   CTABLENAME   VARCHAR(200),  --Name of the table being audited

   IPRIORITY  NUMBER(1),            --set Priority which helps in dequeue

   CSTATUS CHAR(1),                    --status

   CACTIONTYPE CHAR(1),             --- I /U/D (insert/Update/Delete) 

   CFLAG CHAR(1),                        ----stores the status of action 

   CMESSAGEBODY  CLOB ,

   LVADLID CHAR(1),

   DENQUEUE TIMESTAMP (6),

   TRANSACTION_DATE  DATE,

   BY_USER  VARCHAR(200),

   TRANSACTION_NAME VARCHAR(200)

)

 

Step 2: Creating Oracle Queue Table

--create a Queue Table  with payload as above type

 

BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(

     Queue_table        => '"MYORACLE_INSTANCE"."DEMO_TRIGGER_AUDIT_QTABLE"',

     Queue_payload_type => '"MYORACLE_INSTANCE"."DEMO_TRIGGER_AUDIT_TYPE"',

     Sort_list          => 'ENQ_TIME',

     Compatible         => '10.0.0');

  END;

 

Step 3: Creating Oracle Queue

--create a Queue to listen  and update above Queue table

 

BEGIN DBMS_AQADM.CREATE_QUEUE(

     Queue_name          => '"MYORACLE_INSTANCE"."DEMO_TRIGGER_AUDIT_QUEUE"',

     Queue_table         => '"MYORACLE_INSTANCE"."DEMO_TRIGGER_AUDIT_QTABLE"',

     Queue_type          =>  0,

     Max_retries         =>  5,

     Retry_delay         =>  0,

     dependency_tracking =>  FALSE);

  END; 

 

Step 4: Starting a Queue to listen for Enqueue and Dequeue

 

--Start the Que to listen (to allow enqueue and dequeue)

BEGIN

  DBMS_AQADM.START_QUEUE(queue_name => “DEMO_TRIGGER_AUDIT_QUEUE”);

END

or you can do it by right clicking the Queue in Oracle sql developer and selecting "start Queue"

 

Step 5 : Create a stored procedure to Enqueue

We will create a stored procedure which will get the values and convert them to type and enqueue it

create or replace PROCEDURE TRIGGER_AUDIT_ENQUEUE_SP (   

   CTABLENAME   VARCHAR2,

   IPRIORITY  NUMBER,

   CSTATUS CHAR,

   CACTIONTYPE CHAR,

   CFLAG CHAR,

   CMESSAGEBODY  CLOB ,

    TRANSACTION_NAME VARCHAR2

) AS

BEGIN

  DECLARE

   enqueue_options     DBMS_AQ.enqueue_options_t;

   message_properties  DBMS_AQ.message_properties_t;

   message_handle      RAW(16);

   message             DEVENV1.TRIGGER_AUDIT_TYPE;

BEGIN

   message := DEVENV1.TRIGGER_AUDIT_TYPE(CTABLENAME,IPRIORITY,CSTATUS ,CACTIONTYPE ,CFLAG ,CMESSAGEBODY ,'1',null ,TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM'),USER,TRANSACTION_NAME);

 

  DBMS_AQ.ENQUEUE(

      queue_name              => 'DEVENV1.TRIGGER_AUDIT_QUEUE',

      enqueue_options         => enqueue_options,

      message_properties      => message_properties,

      payload                 => message,

      msgid                   => message_handle);

END;

END TRIGGER_AUDIT_ENQUEUE_SP;

 

Now Since we have the Queue and Stored procedure to Enqueue, lets jump to the trigger part , this can be done with different methods asper your requirement

 

Step 6 : Create trigger on each table to call the stored procedure

 We had many tables to audit so instead of writing trigger for each table individually we created a windows form which will allow developers to autogenerate trigger for the tables they want . But since I can add the whole code here .I will explain the manual trigger

 

 

There are different types of triggers implementation possible 

Method 1: Simple ROW Level trigger with static XML generation

Method 2: COMPOUND trigger with static XML generation

Method 3: COMPOUND trigger with dynamic XML generation (without mentioning column)

 

 

 

Method : 1  Simple ROW Level trigger with static XML generation

           

                 Here we will use simple Row level trigger to generate the XML , here  xml conversion is done using the Oracle XMLELEMENT and manually mentioning each columns in the XML generator, :NEW  is used for the Insert and Update  and : OLD is used for delete.

create or replace TRIGGER  C_INTG_TRG_PROJ_TEST_DEMO          

After INSERT OR UPDATE ON PROJ_TEST_DEMO                                                                          

 REFERENCING OLD AS OLD NEW AS NEW    

 FOR EACH ROW                                                                                                                          

 DECLARE                                                                                                                                      

     p_xmldata_del   XMLTYPE;                                                                                         l_rowdata    CLOB;                                                                                                                       

     ctablename             VARCHAR(200) := 'PROJ_TEST_DEMO';                                   

     transaction_name       VARCHAR(200) := ' C_INTG_TRG_PROJ_TEST_DEMO';                           

      actiontype       VARCHAR(1) := '';                                                                                            

 BEGIN                                                                                                                                             

     IF inserting THEN                                                                                                         

             actiontype:= 'I';                                                                                                       

             SELECT XMLELEMENT("ROW" ,XMLElement("ID", : NEW.ID) ,XMLElement("ADDEDDATE", : NEW.ADDEDDATE) ,XMLElement("PRICE", : NEW.PRICE) ,XMLElement("NAME", : NEW.NAME) ,XMLElement("DEC", : NEW.DEC) ) into p_xmldata_del  from dual;

                                                                            

          ELSIF updating THEN                                                                                                            

             actiontype:= 'U';                                                                                                           

             SELECT XMLELEMENT("ROW" ,XMLElement("ID", : NEW.ID) ,XMLElement("ADDEDDATE", : NEW.ADDEDDATE) ,XMLElement("PRICE", : NEW.PRICE) ,XMLElement("NAME", : NEW.NAME) ,XMLElement("DEC", : NEW.DEC) ) into p_xmldata_del  from dual;

                                                                            

             ELSIF DELETING THEN                                                                                                   

                actiontype:= 'D';    

             SELECT XMLELEMENT("ROW" ,XMLElement("ID", : OLD.ID) ,XMLElement("ADDEDDATE", : OLD.ADDEDDATE) ,XMLElement("PRICE", : OLD.PRICE) ,XMLElement("NAME", : OLD.NAME) ,XMLElement("DEC", : OLD.DEC) ) into p_xmldata_del  from dual;

                                                                            

          END IF;                         

    l_rowdata:= p_xmldata_del.getClobVal();                                                                                          

    MYORACLE_INSTANCE.trigger_audit_enqueue_sp(ctablename, 1, 'R', actiontype, 'N',l_rowdata, transaction_name);     --- Calling the stored procedure for ENQUEUE

   NULL;                                                                                                                                       

 END;

 

USING ROW LEVEL TRIGGER

PROS

  • Simple After/Before row trigger, Easy readability and understanding code
  • Oracle simple xml element convertor is used
  • User specifies the columns to be present in XML, No new column is added to xml generated unless user manually change it
  • Default null handler 

CONS

  • New columns will not be present in xml generated unless trigger is edited and new columns added manually
  • Default null handlers treat empty string and null strings as same
  • ENQUEUE is executed for each row                                      

        e.g. if “Delete * from Student where  dept ='science' " deletes 10 rows trigger is executed 10 times . Making the state of row to mutation (almost like lock in sql) delaying further action on same table during each row is enqueued

 

Method 2: COMPOUND trigger with static XML generation

 

Here we use a compound statement level trigger to perform the enqueue

 

CREATE OR REPLACE TRIGGER compound_trigger_name

FOR [INSERT|DELETE]UPDATE [OF column] ON table

COMPOUND TRIGGER

   -- Declarative Section (optional)

   -- Variables declared here have firing-statement duration.    

     --Executed before DML statement

     BEFORE STATEMENT IS

     BEGIN

       NULL;

     END BEFORE STATEMENT;  

     --Executed before each row change- :NEW, :OLD are available

     BEFORE EACH ROW IS

     BEGIN

       NULL;

     END BEFORE EACH ROW;  

     --Executed aftereach row change- :NEW, :OLD are available

     AFTER EACH ROW IS

     BEGIN

       NULL;

     END AFTER EACH ROW;  

     --Executed after DML statement

     AFTER STATEMENT IS

     BEGIN

       NULL;

     END AFTER STATEMENT; 

END compound_trigger_name;

 

We create a compound trigger to make sure that enqueue will happen only after every row is updated by saving all the updated rows in a temporary table and then enqueuing after statement

CREATE OR REPLACE TRIGGER  C_INTG_TRG_PROJ_TEST_DEMO                                                
 FOR UPDATE OR INSERT OR DELETE ON PROJ_TEST_DEMO                                                                                                                                                                                    
 COMPOUND TRIGGER                                                                                                          

     l_query                CLOB;                                                                                                                                                                                                                                                                 
     ctablename             VARCHAR(200) := 'PROJ_TEST_DEMO';                                                                                                         
     transaction_name       VARCHAR(200) := ' C_INTG_TRG_PROJ_TEST_DEMO';                                                                                        

     TYPE id_rt IS RECORD (                                                                             
          log_action   VARCHAR(10)                                                                    
         ,l_rowsql_del   CLOB                                                                             
     );                              

     TYPE row_level_info_t IS                                                                          
         TABLE OF id_rt INDEX BY PLS_INTEGER;                                                                                                                                                                                                                       
     g_row_level_info       row_level_info_t;                                                                                                                                                                                                                                                                                        
        --Region handling Insert and update  and delete starts  here                                                                                                                                                     

     AFTER EACH ROW IS                                                                                                 
     p_xmldata_del   XMLTYPE;                                                                                                                                                                                                                                                       
         BEGIN                                                                                                                                                                                                                                                                                           
           g_row_level_info(g_row_level_info.count+1).log_action :='';                                                                                                                                               
         IF inserting THEN                                                                                                                                                                                                                                                                                   
             g_row_level_info(g_row_level_info.count).log_action := 'I';                                                                                                                                   
             SELECT XMLELEMENT("ROW" ,XMLElement("ID", : NEW.ID) ,XMLElement("ADDEDDATE", : NEW.ADDEDDATE) ,XMLElement("PRICE", : NEW.PRICE) ,XMLElement("NAME", : NEW.NAME) ,XMLElement("DEC", : NEW.DEC) ) into p_xmldata_del  from dual;
            ELSIF updating THEN                                                                                                                                                                                                                                                                             
             g_row_level_info(g_row_level_info.count).log_action := 'U';                                                                                                                                              
             SELECT XMLELEMENT("ROW" ,XMLElement("ID", : NEW.ID) ,XMLElement("ADDEDDATE", : NEW.ADDEDDATE) ,XMLElement("PRICE", : NEW.PRICE) ,XMLElement("NAME", : NEW.NAME) ,XMLElement("DEC", : NEW.DEC) ) into p_xmldata_del  from dual;
         ELSIF deleting THEN                                                                                                                                                                                                                                                                              
             SELECT XMLELEMENT("ROW" ,XMLElement("ID", : OLD.ID) ,XMLElement("ADDEDDATE", : OLD.ADDEDDATE) ,XMLElement("PRICE", : OLD.PRICE) ,XMLElement("NAME", : OLD.NAME) ,XMLElement("DEC", : OLD.DEC) ) into p_xmldata_del  from dual;
g_row_level_info(g_row_level_info.count).log_action := 'D';                                                                                                                                              

         END IF;                                                                                                                                                                                                                                                                                                               
             g_row_level_info(g_row_level_info.count).l_rowsql_del := p_xmldata_del.getClobVal();                                   
     END AFTER EACH ROW;                                                                                                                                                                                                                                                                                        
     AFTER STATEMENT IS                                                                                                                                                                                                                                                                                                        
         l_rowdata   CLOB;                                                                                                                                                                                                                                                                                              
     BEGIN                                                                                                                                                                                                                                                                                                                                            
         --Loop and Enqueue Inserted and Updated entries                                                                                                                                                                                          
         FOR indx IN 1..g_row_level_info.count LOOP                                                                                                                                                                                                              
             l_rowdata := g_row_level_info(indx).l_rowsql_del;                                                                                                                 
             MYORACLE_INSTANCE.trigger_audit_enqueue_sp(ctablename, 1, 'R', g_row_level_info(indx).log_action, 'N',                  
                                                     l_rowdata, transaction_name);                                                                                                                     
                                                                                                                                                                                                                                                                                                                                                                     
         END LOOP;                                                                                                                                                                                                                                                                                                                                                         
     END AFTER STATEMENT;                                                                                                                                                                                                                                                                                       
 END  C_INTG_TRG_PROJ_TEST_DEMO;

 

USING COMPOUND TRIGGER

CONS

  • Complex trigger structure since its combination of After/Before row and After/Before statement in one place(use only the required ones)

PROS

ENQUEUE is executed for each Statement after completion              

e.g. if  "Delete * from Student  where  dept ='science' " deletes 10 rows trigger is executed 1  times . And since enqueue is done after the statement ends rows will be not in mutation state

 

Method 3: COMPOUND trigger with dynamic XML generation (without mentioning column at time of trigger generation)

             

                                         This method is tricky and is a bit memory and execution time expensive because of its dynamic xml generation

We choose this one because

  1. Oracle Database is of a third party system and we don’t know when columns are added or removed to the table , it may cause trigger failure on delete column and also new columns will not be in xml data
  2. Default oracle XMLelement consider null value and empty string as same and generate empty xml tag we need to handle it manually because we need to push the audited data to an sql server

Solving these problems

  1. If we create our own SQL select statement with the NVL handle for required type , we can overcome the issue of null handling  eg : Adding NVL(‘COlumnname’,’{{null}}’) for handling null value in Varchar or char
  2. If we can convert the SQL Query result set of the last modified row  directly to XML , we  will get all the columns present in table at that time

 

  1.       Create a function to create a null handled Select Query with NVL
  2.       Create a function to generate XML from current row

               

--------------------------------------------------------
-- DDL for Function NULL_HANDLED_SQL
--------------------------------------------------------

CREATE OR REPLACE EDITIONABLE FUNCTION "DEVENV1"."NULL_HANDLED_SQL" (TABLENAME_R IN VARCHAR2)
RETURN CLOB
IS queryval clob;
BEGIN
Declare
ccolumn_item CLOB;
Begin
ccolumn_item:='';

queryval:='Select ' ;
FOR somecol IN (SELECT cols.column_name, cols.data_type
FROM
sys.all_tab_cols cols
WHERE
upper(cols.owner) = upper('DEVENV1')
AND upper(cols.table_name) = upper(TABLENAME_R)
ORDER BY
column_id
) LOOP


IF upper(somecol.data_type) = upper('VARCHAR2') THEN
ccolumn_item:='NVL('|| somecol.column_name|| ',''null'')as '|| somecol.column_name||' ,';

ELSE
ccolumn_item:=''|| somecol.column_name||',';
END IF;


queryval:=concat(queryval,ccolumn_item);

END LOOP;

queryval:= RTRIM(queryval, ',');
End;

queryval := concat(queryval,' from '||TABLENAME_R );

return (queryval);

END NULL_HANDLED_SQL; 

 

Function to generate xml from current row

--------------------------------------------------------
-- DDL for Function GET_ROW_XML
--------------------------------------------------------

CREATE OR REPLACE EDITIONABLE FUNCTION "DEVENV1"."GET_ROW_XML" (l_query IN CLOB)
RETURN CLOB
IS l_rowdata clob;
BEGIN
Declare
l_ctx dbms_xmlgen.ctxHandle;
Begin
l_ctx := dbms_xmlgen.newcontext(l_query);
dbms_xmlgen.setnullhandling(l_ctx, dbms_xmlgen.EMPTY_TAG);
l_rowdata := dbms_xmlgen.getXML(l_ctx);
End;
return (l_rowdata);
END GET_ROW_XML;

 

 Now Create the Compond trigger to get value from row convert it to XML with the null handled and ENQUEUE it ot Oracle AQ

CREATE OR REPLACE TRIGGER C_TRG_INT_PROJ_TEST_DEMO
FOR UPDATE OR INSERT OR DELETE ON PROJ_TEST_DEMO
COMPOUND TRIGGER
l_query CLOB;
ctablename VARCHAR(200) := 'PROJ_TEST_DEMO';
transaction_name VARCHAR(200) := ' C_TRG_INT_PROJ_TEST_DEMO';
TYPE id_rt IS RECORD (

ID NUMBER
,log_action VARCHAR(10)
, l_rowsql_del CLOB
);
TYPE row_level_info_t IS
TABLE OF id_rt INDEX BY PLS_INTEGER;
g_row_level_info row_level_info_t;

--Region handling Insert and update starts here
AFTER EACH ROW IS
p_xmldata_del XMLTYPE;
BEGIN
g_row_level_info(g_row_level_info.count+1).log_action :='';
IF inserting THEN

g_row_level_info (g_row_level_info.COUNT).ID := :NEW.ID;
g_row_level_info(g_row_level_info.count).log_action := 'I';
ELSIF updating THEN

g_row_level_info (g_row_level_info.COUNT).ID := :NEW.ID;
g_row_level_info(g_row_level_info.count).log_action := 'U';
ELSIF deleting THEN
g_row_level_info(g_row_level_info.count).log_action := 'D';

SELECT XMLELEMENT("ROW" ,XMLElement("ID", : OLD.ID) ,XMLElement("ADDEDDATE", : OLD.ADDEDDATE) ,XMLElement("PRICE", : OLD.PRICE)
,XMLElement("NAME", : OLD.NAME) ,XMLElement("DEC", : OLD.DEC) ) into p_xmldata_del  from dual
g_row_level_info(g_row_level_info.count).l_rowsql_del := p_xmldata_del.getClobVal();
END IF;
END AFTER EACH ROW;
AFTER STATEMENT IS
l_rowdata CLOB;
l_rowsql CLOB;
p_xmldata XMLTYPE;
BEGIN
l_query := DEVENV1.C_FN_INT_NULL_HANDLED_SQL('PROJ_TEST_DEMO');
--Loop and Enqueue Inserted and Updated entries
FOR indx IN 1..g_row_level_info.count LOOP
if g_row_level_info(indx).log_action='I' then
l_rowSQL:=CONCAT(l_query, ' WHERE ID='||g_row_level_info (indx).ID);
g_row_level_info(indx).l_rowsql_del := DEVENV1.C_FN_INT_GET_ROW_XML(l_rowsql);
ELSIF g_row_level_info(indx).log_action='U' then
l_rowSQL:=CONCAT(l_query, ' WHERE ID='||g_row_level_info (indx).ID);
g_row_level_info(indx).l_rowsql_del := DEVENV1.C_FN_INT_GET_ROW_XML(l_rowsql);
END IF;

DEVENV1.C_SP_INT_TRIGGER_ENQUEUE(ctablename, 1, 'R', g_row_level_info(indx).log_action, 'N',
g_row_level_info(indx).l_rowsql_del, transaction_name);

END LOOP;
END AFTER STATEMENT;
END C_TRG_INT_PROJ_TEST_DEMO;

 

 you can see that we had used static xml in delete , its because  we cannot query a row after it is deleted

 If  any doubts on this oracle AQ implementation  procedure you can contact me   

Happy Codingcool

Sreenath Ganga

        

Other Free Training Materials Available

Free Kerala PSC Exam Training Online
Way4job - Free Kerala PSC training online with previous question papers and free mock exams

Free Kerala PSC training provide notes, Free Online daily Mock test, Previous Questions and answers in english and malayalam. Practice previous years question answers in exam mode will help you success.

Study Now
Free DHA Training
Free DHA Training for Nurses ,Doctors and other medical persons. Sample Questions and Mock test

Study and Practise DHA

Study Now
Free Online PMP Training based on PMBOK
Best Free Online PMP Training based on PMBOK

Best Free Online PMP Training based on PMBOK and PMI guidelines,Sample questions,Exam Simulators and PMP Notes and Flashcards makes you understand the concepts clearly and pass PMP exam at first attempt

Study Now
Free Microsoft Certification Trainings Material
Free Microsoft Training

Study Now

Comments


Commented by : {{x.userName}} {{new Date( x.addedDate).toString().split(' ').splice(0, 5).join(' ') }}

{{x.commentDesc}}

Currently viewing : 11