Oracle logging changes to XML

Question

I want to log data changes in some of Oracle tables. Structure of log table is:

(...)
olddata (xmltype)
newdata (xmltype)

Data changes will be logged by triggers on update, insert, delete Is there any easy way to make code below working?

log.olddata := magic_rowtype_to_xml_function(old);

I have seen DBMS_XMLGEN.getXmltype('select * from table where row_id = x') but I haven't found how to force it to work with rowtypes.

EDIT

I have found little work-arround:

First get a little information about table:

select 'xmlelement("'|| column_name||'",new.' || column_name || '),'  from all_tab_columns where lower(table_name) = 'my_table';

and just copy paste result into

select xmlelement("doc",

--paste here

) from dual;

Ugly, but working.

score 1 · Answer 1 · answered Jul 19 '11 at 12:43

1

It seems that you have 1 log table for several data tables. I think that it is a bad idea . These xmllogs would be IMO badly searchable. If you had one log table for one data table then you could mimic the structure and has much better grasp of the changes first hand.

Something like

create table t (
id int primary key,
dat varchar2(1000));

and

create table t_changes (change_id int primary key, 
change_date date, 
change_user number, 
--and now the original columns
id int, 
dat varchar2(1000));

Also Oracle has a built-in capacity to log changes in a table. I have now forgotten the name of the feature but it creates another log table and a view for your table and remembers everychange and can even present data in the table as they existed e.g. a week ago.

answered Jul 19 '11 at 12:43

Michal Pravda

809
4
9

I've decided to use single table and xml, and I know what I'm doing ;) – piotrpo Jul 19 '11 at 13:35
I agree with Michal Pravda - you should really consider storing data about a relational database in a relational database. Adding XML to this will make it slower and much more difficult to query. – Jon Heller Jul 20 '11 at 02:28
Guys, it's not "mysql" tagged question - please assume, that I considered pros'n'cons and there is no need to discuss here about approach. On the other hand - if you know any better method to generate xml doc from rowtype I'll be very glad to read about it ;) – piotrpo Jul 20 '11 at 07:03

score 0 · Accepted Answer · answered Jul 22 '11 at 12:13

I have found little work-arround:

First get a little information about table:

select 'xmlelement("'|| column_name||'",new.' || column_name || '),'  from all_tab_columns where lower(table_name) = 'my_table';

and just copy paste result into

select xmlelement("doc",

--paste here

) from dual;

Ugly, but working.

Oracle logging changes to XML

2 Answers2