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