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

For Insert and Update we will convert the data after action (new) to XML and ENQUEUE
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';

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

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

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

Create a function to create a null handled Select Query with NVL
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 Coding

Sreenath Ganga

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

END;

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

Step 5 : Create a stored procedure to Enqueue

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

Other Career Related Blogs and Articles you may be interested

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 DHA Training

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

Free Online PMP Training based on PMBOK

Best Free Online PMP Training based on PMBOK

Free Microsoft Certification Trainings Material

Free Microsoft Training

Comments

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