The example presented in this section shows a procedure that can be used to mine and assemble XML redo for tables that contain out of line XML data. This shows how to assemble the XML data using a temporary LOB. Once the XML document is assembled, it can be used in a meaningful way. This example queries the assembled document for the EmployeeName element and then stores the returned name, the XML document and the SQL_REDO for the original DML in the EMPLOYEE_XML_DOCS table.
This procedure is an example only and is simplified. It is only intended to illustrate that DMLs to tables with XMLType data can be mined and assembled using LogMiner.
Before calling this procedure, all of the relevant logs must be added to a LogMiner session and DBMS_LOGMNR.START_LOGMNR() must be called with the COMMITTED_DATA_ONLY option. The MINE_AND_ASSEMBLE() procedure can then be called with the schema and table name of the table that has XML data to be mined.
-- table to store assembled XML documents
create table employee_xml_docs (
employee_name varchar2(100),
sql_stmt varchar2(4000),
xml_doc SYS.XMLType);
-- procedure to assemble the XML documents
create or replace procedure mine_and_assemble(
schemaname in varchar2,
tablename in varchar2)
AS
loc_c CLOB;
row_op VARCHAR2(100);
row_status NUMBER;
stmt VARCHAR2(4000);
row_redo VARCHAR2(4000);
xml_data VARCHAR2(32767 CHAR);
data_len NUMBER;
xml_lob clob;
xml_doc XMLType;
BEGIN
-- Look for the rows in V$LOGMNR_CONTENTS that are for the appropriate schema
-- and table name but limit it to those that are valid sql or that need assembly
-- because they are XML documents.
For item in ( SELECT operation, status, sql_redo FROM v$logmnr_contents
where seg_owner = schemaname and table_name = tablename
and status IN (DBMS_LOGMNR.VALID_SQL, DBMS_LOGMNR.ASSEMBLY_REQUIRED_SQL))
LOOP
row_op := item.operation;
row_status := item.status;
row_redo := item.sql_redo;
CASE row_op
WHEN 'XML DOC BEGIN' THEN
BEGIN
-- save statement and begin assembling XML data
stmt := row_redo;
xml_data := '';
data_len := 0;
DBMS_LOB.CreateTemporary(xml_lob, TRUE);
END;
WHEN 'XML DOC WRITE' THEN
BEGIN
-- Continue to assemble XML data
xml_data := xml_data || row_redo;
data_len := data_len + length(row_redo);
DBMS_LOB.WriteAppend(xml_lob, length(row_redo), row_redo);
END;
WHEN 'XML DOC END' THEN
BEGIN
-- Now that assembly is complete, we can use the XML document
xml_doc := XMLType.createXML(xml_lob);
insert into employee_xml_docs values
(extractvalue(xml_doc, '/EMPLOYEE/NAME'), stmt, xml_doc);
commit;
-- reset
xml_data := '';
data_len := 0;
xml_lob := NULL;
END;
WHEN 'INSERT' THEN
BEGIN
stmt := row_redo;
END;
WHEN 'UPDATE' THEN
BEGIN
stmt := row_redo;
END;
WHEN 'INTERNAL' THEN
DBMS_OUTPUT.PUT_LINE('Skip rows marked INTERNAL');
ELSE
BEGIN
stmt := row_redo;
DBMS_OUTPUT.PUT_LINE('Other - ' || stmt);
IF row_status != DBMS_LOGMNR.VALID_SQL then
DBMS_OUTPUT.PUT_LINE('Skip rows marked non-executable');
ELSE
dbms_output.put_line('Status : ' || row_status);
END IF;
END;
END CASE;
End LOOP;
End;
/
show errors;
This procedure can then be called to mine the changes to the SCOTT.XML_DATA_TAB and apply the DMLs.
EXECUTE MINE_AND_ASSEMBLE ('SCOTT', 'XML_DATA_TAB');
As a result of this procedure, the EMPLOYEE_XML_DOCS table will have a row for each out-of-line XML column that was changed. The EMPLOYEE_NAME column will have the value extracted from the XML document and the SQL_STMT column and the XML_DOC column reflect the original row change.
The following is an example query to the resulting table that displays only the employee name and SQL statement:
SELECT EMPLOYEE_NAME, SQL_STMT FROM EMPLOYEE_XML_DOCS;
EMPLOYEE_NAME SQL_STMT
Scott Davis update "SCOTT"."XML_DATA_TAB" a set a."F3" = XMLType(:1)
where a."F1" = '5000' and a."F2" = 'Chen' and a."F5" = 'JJJ'
Richard Harry update "SCOTT"."XML_DATA_TAB" a set a."F4" = XMLType(:1)
where a."F1" = '5000' and a."F2" = 'Chen' and a."F5" = 'JJJ'
Margaret Sally update "SCOTT"."XML_DATA_TAB" a set a."F4" = XMLType(:1)
where a."F1" = '5006' and a."F2" = 'Janosik' and a."F5" = 'MMM'