Edit: Thanks to Chris Saxon who is from asktom team, checked my code and warned me about a problem. Not to overload the PGA fetch_rows function only fetches 1024 rows at once so every row set chunk size is 1024. In this case for the 1025th row, previous row information is lost because fetch_rows procedure ended and recalled. He suggested to use XStore to store data from previous run of fetch_rows and I added it. While doing that I removed some unnecessary lines too. Thanks again Chris.
PS: this is not a Polymorphic Table Function (PTF) explanation or introduction, I just wanted to share a code that I wrote before and very useful (at least for me). if you want to learn more about PTFs check https://blog.sqlora.com/en/polymorphic-table-functions-example-transposing-columns-to-rows/
PTF is a great tool that enhances the sql. I think I only need a “pointer” definition in PLSQL after PTF and that’s all I would be very happy 🙂 even in this example, it would be nice if I can use a pointer to point data collections which makes the code shorter and more readable.
About a few months ago, I debugged a code for some unnecessary rows. It comes to an sql statement which has a MINUS set operator to eliminate rows but some rows are not eliminated as needed. I found the rows and try to find out what column has different values so that row is not eliminated in MINUS but this table has so many NUMBER columns which has many decimal points and it was really hard to find which column(s) has different values.
Since I am a so lazy person to check all column by column in two rows, I created a Polymorphic Table Function (PTF) to find the differences for me and it worked as I needed. I added a new varchar2 column to the base query (select … minus select …) called DIFFERENT_COLUMNS and I put different column names, old value and new value into that column and got all of them. After solving my problems, I realized that I can use this code for something even better!!!
While looking to a log data of table (trigger base or flashback or flashback data archive), I have been asked many times “what changed”. which column(s) has changed? it is not easy and mostly required to check row by row, column by column. So, my polymorphic table function is perfect solution for that! Just provide necessary table or select statement and find all columns changed every step.
Here is my basic code:
this package has it’s own polymorphic table functions (SF_Find_Differences) which has two parameter, first is the table or query that you want to find differences with previous rows. By saying “previous” I am using default order what you provided to my PTF I am not ordering data in any way. I will explain second parameter later. let’s make an example:
this will be my sample data (flashback query). one problem is while you are looking for a historical data mostly you will be checking one specified row history not all rows. so you must filter rows using primary key or something. Unfortunately you can not pass a “subquery” to PTF but you can pass a WITH data source.
So, I just created a dummy emp table and then updated employee with id 100. first change first_name then last_name then salary and commission_pct both. here is my query:
I think this is so cool 🙂 if you check the last column it will be listing what is changed (which columns). As you can see I use a WITH clause to define specific historical data and order it using VERSIONS_ENDTIME. One problem is, I wanted to see when these changes happend (versions_starttime and endtime) but since they are also columns in this query my DIFFERENT_COLUMNS has those changed columns too! that is where the second parameter gets involved. second parameter is EXCLUDED column list from change control. so:
much clear. on the second row, Steven becomes Mustafa, on the third row, King becomes KALAYCI and on the last one, salary and commission_pct are changed. it is more easier to track it or listing in your application.
Of course this is not a complete code, I just checked for varchar2, number, date and timestamp columns but not the others. Also didn’t work on “formatting” like date and timestamp data types which can be done but still pretty nice.
I wanted to share this with whole community. I hope this helps.
Edit: while checking whether old value and new value are different or not, I used if conditions but I would like to use SYS_OP_MAP_NONNULL function. unfortunately SYS_OP_MAP_NONNULL is not defined in plsql.
Edit: I made a change about excluding columns. instead of for loop I used “member of” clause.
wish you healthy corona & earthquake free days. Unfortunately, an earthquake happened in my country and neighbor countries. Rescue teams are still trying to save people. I hope this never happens in any place and no one experience this pain.