I have a requirement like this:
In table Ticket_detail, I have 2 compare last 2 rows. If there is any change in values between 2 rows, then I have to log the change into another table.
To explain in detail, Say table Ticket_detail has following columns:
Say it has 6 rows, Version 1,2 and 3 with 2 rows each. (Always 2 rows, wont change)
| TICKET_NO | ENTRY_VERSN_NO | TEST_DTL_NO | DESTINATION | ROUTE | TEST_NUMBER |
| 101 | 1 | 1 | ABCD | XYZ | 12345 |
| 101 | 1 | 2 | efgh | qwe | 4567 |
| 101 | 2 | 1 | ABCD | XYZ | 12345 |
| 101 | 2 | 2 | efgh | qwe | 4321 |
| 101 | 3 | 1 | ABCD | XYZ | 67890 |
| 101 | 3 | 2 | ASDF | qwe | 4321 |
My requirement is to compare last 2 versions, i.e, version 2 and 3 and log the changes which i have highlighted above into a log table with below structure:
| TICKET_NO | TICKET_VERSN_NO | TABLE_NAME | COL_NM | COL_ID | OLD_VALUE | NEW_VALUE |
| 101 | 3 | Ticket_detail | TEST_NUMBER | 1 | 12345 | 67890 |
| 101 | 3 | Ticket_detail | DESTINATION | 2 | efgh | ASDF |
Now problem is I have 14 tables with 30 to 40 columns in each for which I have to do this. So I am looking for a common solution to compare columns of the table. I am thinking about using data dictionary ALL_TAB_COLUMNS and fetch the columns for the table and compare them. But problem is the output I get from data dictionary is a string. I am not able to use them as variable. Is there any possibility that we can convert these string into variables so that comparison is possible. Please suggest some idea.
Different solution is also welcome. All i need is to log the changes into the LOG table.
Thanks in advance.