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;

/