Hi,
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 |
create or replace Package Pkg_Column_Value_Differencies as /* Written by Mustafa KALAYCI mustafakalayci.me find different values of columns with previous rows in query order */ Function SF_Find_Differences (Source_Table in Table, Excluded_Columns IN DBMS_TF.COLUMNS_T := null) Return Table Pipelined Row Polymorphic Using Pkg_Column_Value_Differencies; Function Describe(Source_Table in out dbms_tf.table_t, Excluded_Columns IN DBMS_TF.COLUMNS_T := null) return dbms_tf.describe_t; Procedure Fetch_Rows; End; / create or replace Package Body Pkg_Column_Value_differencies As /* Written by Mustafa KALAYCI mustafakalayci.me find different values of columns with previous rows in query order */ Function Describe(Source_Table in out dbms_tf.table_t, Excluded_Columns IN DBMS_TF.COLUMNS_T := null) return dbms_tf.describe_t As Type T_Excluded_Columns is table of Pls_Integer index By Varchar2(128); Begin For I in 1..Source_Table.Column.Count Loop Source_Table.Column(I).For_Read := True; Source_Table.Column(I).Pass_Through := True; if Source_Table.column(I).description.name member of excluded_columns then Source_Table.Column(I).For_Read := False; End if; End Loop; -- add a new column to output of query/table -- different values will be listed as string in this column Return Dbms_Tf.Describe_T(New_Columns => Dbms_Tf.Columns_New_T(1 => Dbms_Tf.Column_Metadata_T(Name =>'DIFFERENT_COLUMS'))); End Describe; /*****************************************************************/ Procedure Fetch_Rows as V_Row_Set Dbms_Tf.Row_Set_T; V_Row_Count PLS_Integer; V_New_Column_Values Dbms_Tf.Tab_Varchar2_T; V_Column_Count PLS_Integer; V_Data Varchar2(4000); V_Result PLS_Integer; V_Current_Column_Value Varchar2(4000); Type T_Arr_Previous_Column_values is table of Varchar2(4000); V_Previous_Column_Values T_Arr_Previous_Column_values := T_Arr_Previous_Column_values(); -- Local Procedure to check differences and add to a variable Procedure LP_Add_Different_Value_Data(P_Column varchar2, P_Old_Value varchar2, P_New_Value varchar2, P_Column_Number PLS_Integer) as Begin if P_Old_Value is Null And P_New_Value is not null Or P_Old_Value is Not Null And P_New_Value is null Or P_Old_Value <> P_New_Value Then V_Data := Substr(V_Data || P_Column || ':' || Nvl(P_Old_Value, 'NULL') || '=>' || Nvl(P_New_Value, 'NULL') || ',', 1, 4000); End if; V_Previous_Column_Values(P_Column_Number) := P_New_Value; End LP_Add_Different_Value_Data; Function LF_Init_Previous_Row_Values(P_Row_Set Dbms_Tf.Row_Set_T) Return PLS_Integer As V_Result PLS_Integer; Begin if Dbms_Tf.XStore_Exists('C1') then /* if this is not the first row set chunk then read last row value from previous row set chunk */ For i in 1..P_Row_Set.Count Loop Dbms_Tf.XStore_Get('C' || i, V_Previous_Column_Values(i)); End Loop; V_Result := 1; -- there were a previous row on previous row set chunk Else /*if this is the first row set chunk, then assign current row value as previous row */ V_New_Column_Values(1) := Null; for k in 1..V_Previous_Column_Values.Count Loop Case P_Row_Set(k).Description.Type When Dbms_Tf.Type_Varchar2 Then V_Previous_Column_Values(k) := P_Row_Set(k).Tab_Varchar2(1); When Dbms_Tf.Type_Char Then V_Previous_Column_Values(k) := P_Row_Set(k).Tab_Varchar2(1); When Dbms_Tf.Type_Number Then V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Number(1)); When Dbms_Tf.Type_Date Then V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Date(1)); When Dbms_Tf.Type_Timestamp Then V_Previous_Column_Values(k) := To_Char(P_Row_Set(k).Tab_Timestamp(1)); End Case; End Loop; V_Result := 0; -- this is the first row set chunk End if; Return V_Result; End LF_Init_Previous_Row_Values; Function LF_Get_Current_Column_Value(P_Col Dbms_Tf.Column_Data_T, P_Row_Number PLS_Integer) Return Varchar2 As Begin Return Case P_Col.Description.Type When Dbms_Tf.Type_Varchar2 Then P_Col.Tab_Varchar2(P_Row_Number) When Dbms_Tf.Type_Char Then P_Col.Tab_Varchar2(P_Row_Number) When Dbms_Tf.Type_Number Then To_Char(P_Col.Tab_Number(P_Row_Number)) When Dbms_Tf.Type_Date Then To_Char(P_Col.Tab_Date(P_Row_Number)) When Dbms_Tf.Type_Timestamp Then To_Char(P_Col.Tab_Timestamp(P_Row_Number)) End; End; Begin /* get result of the query and generate new column values */ Dbms_Tf.Get_Row_Set(V_Row_Set, V_Row_Count, V_Column_Count); V_Previous_Column_Values.Extend(V_Column_Count); -- loop for every row for i in 1..V_Row_Count Loop V_Data := Null; if i = 1 Then V_Result := LF_Init_Previous_Row_Values(V_Row_Set); Continue When V_Result = 0; -- this is the first row set chunk so this is the first row, no previous End if; --loop for all columns in base table/query --and check if value is different in previous row for k in 1..V_Column_Count Loop --get current column value as varchar2 V_Current_Column_Value := LF_Get_Current_Column_Value(V_Row_Set(k), i); --check column type and use related collection to read data --and if previous row data is different than current add it to string LP_Add_Different_Value_Data(P_Column => V_Row_Set(k).Description.Name, P_Old_Value => V_Previous_Column_Values(k), P_New_Value => V_Current_Column_Value, P_Column_Number => k); End Loop; -- column loop V_New_Column_Values(i) := V_Data; End Loop; -- row loop /* set last row data into xstore so it can be read in next row set chunk */ For i in 1..V_Column_Count Loop Dbms_Tf.XStore_Set('C' || i, V_Previous_Column_Values(i)); End Loop; --assign generated column values to first new column. Dbms_Tf.Put_Col(1, V_New_Column_Values); exception when others then dbms_output.put_line('ERROR: ' || sqlerrm || dbms_utility.format_error_Backtrace); Raise; End Fetch_Rows; end; / |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
/********** SAMPLE DATA **************/ drop table emp purge; exec dbms_lock.sleep(10); create table emp as select * from hr.employees; commit; exec dbms_lock.sleep(10); update emp set first_name = 'Mustafa' where employee_id = 100; commit; exec dbms_lock.sleep(10); update emp set last_name = 'KALAYCI' where employee_id = 100; commit; exec dbms_lock.sleep(10); update emp set salary = 35000, commission_pct = 0.6 where employee_id = 100; commit; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
with history_data as ( select versions_starttime, versions_endtime, e.employee_id as id, e.first_name, e.last_name, e.salary, e.commission_pct from emp versions between scn minvalue and maxvalue e where employee_id = 100 order by versions_endtime ) select * from Pkg_Column_Value_Differencies.SF_Find_Differences(history_data); VERSIONS_STARTTIME VERSIONS_ENDTIME ID FIRST_NAME LAST_NAME SALARY COMMISSION_PCT DIFFERENT_COLUMS NULL 01-NOV-20 11.58.31 PM 100 Steven King 24000 NULL NULL 01-NOV-20 11.58.31 PM 01-NOV-20 11.58.40 PM 100 Mustafa King 24000 NULL "VERSIONS_STARTTIME":NULL=>01-NOV-20 11.58.31.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.31.000000000 PM=>01-NOV-20 11.58.40.000000000 PM,"FIRST_NAME":Steven=>Mustafa, 01-NOV-20 11.58.40 PM 01-NOV-20 11.58.52 PM 100 Mustafa KALAYCI 24000 NULL "VERSIONS_STARTTIME":01-NOV-20 11.58.31.014177000 PM=>01-NOV-20 11.58.40.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.40.000000000 PM=>01-NOV-20 11.58.52.000000000 PM,"LAST_NAME":King=>KALAYCI, 01-NOV-20 11.58.52 PM NULL 100 Mustafa KALAYCI 35000 .6 "VERSIONS_STARTTIME":01-NOV-20 11.58.40.014177000 PM=>01-NOV-20 11.58.52.014177000 PM,"VERSIONS_ENDTIME":01-NOV-20 11.58.52.000000000 PM=>NULL,"SALARY":24000=>35000,"COMMISSION_PCT":NULL=>.6, |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
with history_data as ( select versions_starttime, versions_endtime, e.employee_id as id, e.first_name, e.last_name, e.salary, e.commission_pct from emp versions between scn minvalue and maxvalue e where employee_id = 100 order by versions_endtime ) select * from Pkg_Column_Value_Differencies.SF_Find_Differences(history_data, columns(versions_starttime, versions_endtime)); VERSIONS_STARTTIME VERSIONS_ENDTIME ID FIRST_NAME LAST_NAME SALARY COMMISSION_PCT DIFFERENT_COLUMS NULL 01-NOV-20 11.58.31 PM 100 Steven King 24000 NULL NULL 01-NOV-20 11.58.31 PM 01-NOV-20 11.58.40 PM 100 Mustafa King 24000 NULL "FIRST_NAME":Steven=>Mustafa, 01-NOV-20 11.58.40 PM 01-NOV-20 11.58.52 PM 100 Mustafa KALAYCI 24000 NULL "LAST_NAME":King=>KALAYCI, 01-NOV-20 11.58.52 PM NULL 100 Mustafa KALAYCI 35000 .6 "SALARY":24000=>35000,"COMMISSION_PCT":NULL=>.6, |
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.