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;
/