Home » SQL & PL/SQL » SQL & PL/SQL » Comparing AUDIT TABLE values (11g (unsure of version) OS=Linux)
Comparing AUDIT TABLE values [message #421359] Mon, 07 September 2009 19:02 Go to next message
Messages: 22
Registered: September 2008
Location: Toronto
Junior Member
I've searched through Audit related posting and found some ideas all of which are PL/SQL related. Or use different audit table structures. But I'd like to see if there is another option.

I have an audit table:

There are actually 30 columns in the underlying table (making 60+ in the audit table). And perhaps only one column in each record where the old and new values do not match.

I'm looking for the most straightforward method to compare and display old and new values in the row. So that my resultant set is a list of columns that changed in the underlying record and their old and new value(s). I would not want to see columns that are identical.

Originally I was thinking I could use decode:
decode(old_value_x, new_value_x, new_value_x , 'MATCH')
However, this would display only either my new or old value and not show me both.

Thinking it through, PL/SQL seems the way to go either way. So I going to try loading the values, running individual comparisons on each pair and DBMS_OUTPUT on a successful inequality.

Sorry, I'm kind of using the forum as a sounding board... But if any one has encountered this before or has some ideas to share...

And thanks for listening.

Re: Comparing AUDIT TABLE values [message #421360 is a reply to message #421359] Mon, 07 September 2009 19:34 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You might try something like this:

Difference between two rows

There are many variations on this theme. You should try building your own to suite your needs.

If you only have one table you are interested in and do not want an generic routine like this, then you could easily do this in SQL with DECODE like you had intended.

Good luck, Kevin
Previous Topic: How to show records on zero on hand (Job pick list)
Next Topic: Trigger Problem
Goto Forum:

Current Time: Tue Jan 17 19:51:08 CST 2017

Total time taken to generate the page: 0.12143 seconds