Skip to content

Commit f951cd3

Browse files
jgebaljgebal
committed
Bringing back the to_string for refcursor.
Moved diff functionality into DIFF code.
1 parent 7b4e2df commit f951cd3

14 files changed

+143
-80
lines changed

‎source/core/ut_utils.pkb‎

Lines changed: 10 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -268,6 +268,14 @@ create or replace package body ut_utils is
268268
return l_result;
269269
end;
270270

271+
procedure append_to_clob(a_clob in out nocopy clob, a_clob_table t_clob_tab, a_delimiter varchar2:= chr(10)) is
272+
begin
273+
for i in 1 .. a_clob_table.count loop
274+
append_to_clob(a_clob,a_delimiter);
275+
dbms_lob.append(a_clob,a_clob_table(i));
276+
end loop;
277+
end;
278+
271279
function time_diff(a_start_time timestamp with time zone, a_end_time timestamp with time zone) return number is
272280
begin
273281
return
@@ -380,11 +388,9 @@ create or replace package body ut_utils is
380388
end;
381389

382390
procedure cleanup_temp_tables is
383-
pragma autonomous_transaction;
384391
begin
385-
execute immediate 'delete from ut_cursor_data';
386-
execute immediate 'delete from ut_cursor_data_diff';
387-
commit;
392+
execute immediate 'delete from ut_data_set_tmp';
393+
execute immediate 'delete from ut_data_set_diff_tmp';
388394
end;
389395

390396
function to_version(a_version_no varchar2) return t_version is

‎source/core/ut_utils.pks‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -107,6 +107,7 @@ create or replace package ut_utils authid definer is
107107
build natural
108108
);
109109

110+
type t_clob_tab is table of clob;
110111

111112
/**
112113
* Converts test results into strings
@@ -197,6 +198,8 @@ create or replace package ut_utils authid definer is
197198

198199
function table_to_clob(a_text_table ut_varchar2_list, a_delimiter varchar2:= chr(10)) return clob;
199200

201+
procedure append_to_clob(a_clob in out nocopy clob, a_clob_table t_clob_tab, a_delimiter varchar2 := chr(10));
202+
200203
/**
201204
* Returns time difference in seconds (with miliseconds) between given timestamps
202205
*/

‎source/create_synonyms_and_grants_for_public.sql‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -65,8 +65,8 @@ grant execute on &&ut3_owner..ut_file_mapping to public;
6565
grant execute on &&ut3_owner..ut_file_mapper to public;
6666
grant execute on &&ut3_owner..ut_key_value_pairs to public;
6767
grant execute on &&ut3_owner..ut_key_value_pair to public;
68-
grant select, insert, delete on &&ut3_owner..ut_cursor_data to public;
69-
grant select, insert, delete on &&ut3_owner..ut_cursor_data_diff to public;
68+
grant select, insert, delete on &&ut3_owner..ut_data_set_tmp to public;
69+
grant select, insert, delete on &&ut3_owner..ut_data_set_diff_tmp to public;
7070
grant execute on &&ut3_owner..ut_sonar_test_reporter to public;
7171
grant execute on &&ut3_owner..ut_annotations to public;
7272
grant execute on &&ut3_owner..ut_annotation to public;
@@ -117,6 +117,6 @@ create public synonym ut_file_mapping for &&ut3_owner..ut_file_mapping;
117117
create public synonym ut_file_mapper for &&ut3_owner..ut_file_mapper;
118118
create public synonym ut_key_value_pairs for &&ut3_owner..ut_key_value_pairs;
119119
create public synonym ut_key_value_pair for &&ut3_owner..ut_key_value_pair;
120-
create public synonym ut_cursor_data for &&ut3_owner..ut_cursor_data;
121-
create public synonym ut_cursor_data_diff for &&ut3_owner..ut_cursor_data_diff;
120+
create public synonym ut_data_set_tmp for &&ut3_owner..ut_data_set_tmp;
121+
create public synonym ut_data_set_diff_tmp for &&ut3_owner..ut_data_set_diff_tmp;
122122
create public synonym ut_sonar_test_reporter for &&ut3_owner..ut_sonar_test_reporter;

‎source/create_synonyms_and_grants_for_user.sql‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -85,8 +85,8 @@ grant execute on &&ut3_owner..ut_file_mapping to &ut3_user;
8585
grant execute on &&ut3_owner..ut_file_mapper to &ut3_user;
8686
grant execute on &&ut3_owner..ut_key_value_pairs to &ut3_user;
8787
grant execute on &&ut3_owner..ut_key_value_pair to &ut3_user;
88-
grant select, insert, delete on &&ut3_owner..ut_cursor_data to &ut3_user;
89-
grant select, insert, delete on &&ut3_owner..ut_cursor_data_diff to &ut3_user;
88+
grant select, insert, delete on &&ut3_owner..ut_data_set_tmp to &ut3_user;
89+
grant select, insert, delete on &&ut3_owner..ut_data_set_diff_tmp to &ut3_user;
9090
grant execute on &&ut3_owner..ut_sonar_test_reporter to &ut3_user;
9191
grant execute on &&ut3_owner..ut_annotations to &ut3_user;
9292
grant execute on &&ut3_owner..ut_annotation to &ut3_user;
@@ -136,6 +136,6 @@ create or replace synonym &ut3_user..ut_file_mapping for &&ut3_owner..ut_file_ma
136136
create or replace synonym &ut3_user..ut_file_mapper for &&ut3_owner..ut_file_mapper;
137137
create or replace synonym &ut3_user..ut_key_value_pairs for &&ut3_owner..ut_key_value_pairs;
138138
create or replace synonym &ut3_user..ut_key_value_pair for &&ut3_owner..ut_key_value_pair;
139-
create or replace synonym &ut3_user..ut_cursor_data for &&ut3_owner..ut_cursor_data;
140-
create or replace synonym &ut3_user..ut_cursor_data_diff for &&ut3_owner..ut_cursor_data_diff;
139+
create or replace synonym &ut3_user..ut_data_set_tmp for &&ut3_owner..ut_cursor_data;
140+
create or replace synonym &ut3_user..ut_data_set_diff_tmp for &&ut3_owner..ut_data_set_diff_tmp;
141141
create or replace synonym &ut3_user..ut_sonar_test_reporter for &&ut3_owner..ut_sonar_test_reporter;

source/expectations/data_values/ut_cursor_data_diff.sql renamed to source/expectations/data_values/ut_data_set_diff_tmp.sql

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
create global temporary table ut_cursor_data_diff(
1+
create global temporary table ut_data_set_diff_tmp(
22
/*
33
utPLSQL - Version 3
44
Copyright 2016 - 2017 utPLSQL Project
@@ -12,6 +12,7 @@ create global temporary table ut_cursor_data_diff(
1212
See the License for the specific language governing permissions and
1313
limitations under the License.
1414
*/
15-
row_no integer,
16-
constraint ut_cursor_data_diff_pk primary key(row_no)
15+
diff_id raw(16),
16+
item_no integer,
17+
constraint ut_data_set_diff_tmp_pk primary key(diff_id,item_no)
1718
) on commit preserve rows;

source/expectations/data_values/ut_cursor_data.sql renamed to source/expectations/data_values/ut_data_set_tmp.sql

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
create global temporary table ut_cursor_data(
1+
create global temporary table ut_data_set_tmp(
22
/*
33
utPLSQL - Version 3
44
Copyright 2016 - 2017 utPLSQL Project
@@ -12,8 +12,8 @@ create global temporary table ut_cursor_data(
1212
See the License for the specific language governing permissions and
1313
limitations under the License.
1414
*/
15-
cursor_data_guid raw(16),
16-
row_no integer,
17-
row_data xmltype,
18-
constraint ut_cursor_data_pk primary key(cursor_data_guid, row_no)
15+
data_set_guid raw(16),
16+
item_no integer,
17+
item_data xmltype,
18+
constraint ut_data_set_tmp_pk primary key(data_set_guid, item_no)
1919
) on commit preserve rows;

‎source/expectations/data_values/ut_data_value.tpb‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,16 @@ create or replace type body ut_data_value as
2020
return compare_implementation(a_other);
2121
end;
2222

23+
member function is_diffable return boolean is
24+
begin
25+
return false;
26+
end;
27+
28+
member function diff( a_other ut_data_value ) return varchar2 is
29+
begin
30+
return null;
31+
end;
32+
2333
member function is_multi_line return boolean is
2434
begin
2535
return false;

‎source/expectations/data_values/ut_data_value.tps‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,8 @@ create or replace type ut_data_value authid current_user as object (
2323
final member function format_multi_line( a_string varchar2) return varchar2,
2424
final member function to_string_report(a_add_new_line_for_multi_line boolean := false, a_with_type_name boolean := true) return varchar2,
2525
order member function compare( a_other ut_data_value ) return integer,
26+
member function is_diffable return boolean,
27+
member function diff( a_other ut_data_value ) return varchar2,
2628
not instantiable member function compare_implementation( a_other ut_data_value ) return integer
2729
) not final not instantiable
2830
/

‎source/expectations/data_values/ut_data_value_refcursor.tpb‎

Lines changed: 78 additions & 49 deletions
Original file line numberDiff line numberDiff line change
@@ -49,7 +49,7 @@ create or replace type body ut_data_value_refcursor as
4949
begin
5050
self.is_cursor_null := ut_utils.boolean_to_int(a_value is null);
5151
self.self_type := $$plsql_unit;
52-
self.data_value := sys_guid();
52+
self.data_set_guid := sys_guid();
5353
self.data_type := 'refcursor';
5454

5555
if a_value is not null then
@@ -75,9 +75,9 @@ create or replace type body ut_data_value_refcursor as
7575
loop
7676
l_xml := dbms_xmlgen.getxmltype(l_ctx);
7777

78-
execute immediate 'insert into ' || l_ut_owner || '.ut_cursor_data(cursor_data_guid, row_no, row_data)
78+
execute immediate 'insert into ' || l_ut_owner || '.ut_data_set_tmp(data_set_guid, item_no, item_data)
7979
select :self_guid, :self_row_count + rownum, value(a) from table( xmlsequence( extract(:l_xml,''ROWSET/*'') ) ) a'
80-
using in self.data_value, self.row_count, l_xml;
80+
using in self.data_set_guid, self.row_count, l_xml;
8181

8282
exit when sql%rowcount = 0;
8383

@@ -112,40 +112,67 @@ create or replace type body ut_data_value_refcursor as
112112
end;
113113

114114
overriding member function to_string return varchar2 is
115-
type t_clob_tab is table of clob;
116-
l_results t_clob_tab;
117-
c_max_rows constant integer := 50;
118-
c_pad_depth constant integer := 5;
115+
l_results ut_utils.t_clob_tab;
116+
c_max_rows constant integer := 10;
119117
l_result clob;
120-
l_result_xml xmltype;
121118
l_result_string varchar2(32767);
122-
l_ut_owner varchar2(250) := ut_utils.ut_owner;
119+
begin
120+
dbms_lob.createtemporary(l_result,true);
121+
--return first 10 rows
122+
execute immediate '
123+
select xmlserialize( content ucd.item_data no indent)
124+
from '|| ut_utils.ut_owner ||'.ut_data_set_tmp ucd
125+
where ucd.data_set_guid = :data_set_guid
126+
and ucd.item_no <= :max_rows'
127+
bulk collect into l_results using self.data_set_guid, c_max_rows;
128+
129+
ut_utils.append_to_clob(l_result,'row count: '||row_count);
130+
ut_utils.append_to_clob(l_result,l_results);
131+
132+
l_result_string := ut_utils.to_string(l_result,null);
133+
dbms_lob.freetemporary(l_result);
134+
return self.format_multi_line( l_result_string );
135+
end;
136+
137+
overriding member function is_diffable return boolean is
138+
begin
139+
return true;
140+
end;
141+
142+
overriding member function diff( a_other ut_data_value ) return varchar2 is
143+
c_max_rows constant integer := 50;
144+
c_pad_depth constant integer := 5;
145+
l_results ut_utils.t_clob_tab;
146+
l_result clob;
147+
l_result_string varchar2(32767);
148+
l_ut_owner varchar2(250) := ut_utils.ut_owner;
123149
l_diff_row_count integer;
150+
l_other ut_data_value_refcursor;
151+
l_diff_id raw(16);
124152
begin
153+
if not a_other is of (ut_data_value_refcursor) then
154+
raise value_error;
155+
end if;
156+
l_other := treat(a_other as ut_data_value_refcursor);
157+
l_diff_id := dbms_crypto.hash(self.data_set_guid||l_other.data_set_guid,2);
125158
dbms_lob.createtemporary(l_result,true);
126159
-- First tell how many rows are different
127-
execute immediate 'select count(*) from ' || l_ut_owner || '.ut_cursor_data_diff' into l_diff_row_count;
128-
129-
ut_utils.append_to_clob(l_result,'(rows: ' || to_char(self.row_count)|| ', mismatched: ' || to_char(l_diff_row_count) ||')'|| chr(10));
160+
execute immediate 'select count(*) from ' || l_ut_owner || '.ut_data_set_diff_tmp where diff_id = :diff_id' into l_diff_row_count using l_diff_id;
130161

131162
--return rows which were previously marked as different
132-
execute immediate q'[select 'row_no: '||rpad( ucd.row_no, :c_pad_depth )||' '||xmlserialize( content ucd.row_data no indent)
133-
from ]' || l_ut_owner || '.ut_cursor_data ucd
134-
where ucd.cursor_data_guid = :self_guid
135-
and ucd.row_no in (select row_no from ' || l_ut_owner || '.ut_cursor_data_diff ucdc)
163+
execute immediate q'[select 'row_no: '||rpad( ucd.item_no, :c_pad_depth )||' '||xmlserialize( content ucd.item_data no indent)
164+
from ]' || l_ut_owner || '.ut_data_set_tmp ucd
165+
where ucd.data_set_guid = :self_guid
166+
and ucd.item_no in (select item_no from ' || l_ut_owner || '.ut_data_set_diff_tmp ucdc where diff_id = :diff_id)
136167
and rownum <= :max_rows'
137-
bulk collect into l_results using c_pad_depth, self.data_value, c_max_rows;
168+
bulk collect into l_results using c_pad_depth, self.data_set_guid, l_diff_id, c_max_rows;
138169

139-
for i in 1 .. l_results.count loop
140-
dbms_lob.append(l_result,l_results(i));
141-
if i < l_results.count then
142-
ut_utils.append_to_clob(l_result,chr(10));
143-
end if;
144-
end loop;
170+
ut_utils.append_to_clob(l_result,'(count: ' || to_char(l_diff_row_count) ||')');
171+
ut_utils.append_to_clob(l_result,l_results);
145172

146173
l_result_string := ut_utils.to_string(l_result,null);
147174
dbms_lob.freetemporary(l_result);
148-
return self.format_multi_line( l_result_string );
175+
return l_result_string;
149176
end;
150177

151178
member function is_empty return boolean is
@@ -160,44 +187,46 @@ create or replace type body ut_data_value_refcursor as
160187
l_include_xpath varchar2(32767);
161188
l_ut_owner varchar2(250) := ut_utils.ut_owner;
162189
l_column_filter varchar2(32767);
190+
l_diff_id raw(16);
163191
begin
164192
l_exclude_xpath := coalesce(self.exclude_xpath, l_other.exclude_xpath);
165193
l_include_xpath := coalesce(self.include_xpath, l_other.include_xpath);
166194
-- this SQL statement is constructed in a way that we always get the same number and ordering of substitution variables
167195
-- That is, we always get: l_exclude_xpath, l_include_xpath
168196
-- regardless if the variables are NULL (not to be used) or NOT NULL and will be used for filtering
169197
if l_exclude_xpath is null and l_include_xpath is null then
170-
l_column_filter := ':l_exclude_xpath as l_exclude_xpath, :l_include_xpath as l_include_xpath, ucd.row_data as row_data';
198+
l_column_filter := ':l_exclude_xpath as l_exclude_xpath, :l_include_xpath as l_include_xpath, ucd.item_data as item_data';
171199
elsif l_exclude_xpath is not null and l_include_xpath is null then
172-
l_column_filter := 'deletexml( ucd.row_data, :l_exclude_xpath ) as row_data, :l_include_xpath as l_include_xpath';
200+
l_column_filter := 'deletexml( ucd.item_data, :l_exclude_xpath ) as item_data, :l_include_xpath as l_include_xpath';
173201
elsif l_exclude_xpath is null and l_include_xpath is not null then
174-
l_column_filter := ':l_exclude_xpath as l_exclude_xpath, extract( ucd.row_data, :l_include_xpath ) as row_data';
202+
l_column_filter := ':l_exclude_xpath as l_exclude_xpath, extract( ucd.item_data, :l_include_xpath ) as item_data';
175203
elsif l_exclude_xpath is not null and l_include_xpath is not null then
176-
l_column_filter := 'extract( deletexml( ucd.row_data, :l_exclude_xpath ), :l_include_xpath ) as row_data';
204+
l_column_filter := 'extract( deletexml( ucd.item_data, :l_exclude_xpath ), :l_include_xpath ) as item_data';
177205
end if;
178-
if a_other is of (ut_data_value_refcursor) then
179-
l_other := treat(a_other as ut_data_value_refcursor);
180-
-- Find differences
181-
execute immediate 'insert into ' || l_ut_owner || '.ut_cursor_data_diff ( row_no )
182-
select nvl(exp.row_no, act.row_no)
183-
from (select '||l_column_filter||', ucd.row_no
184-
from ' || l_ut_owner || '.ut_cursor_data ucd where ucd.cursor_data_guid = :self_guid) exp
185-
full outer join
186-
(select '||l_column_filter||', ucd.row_no
187-
from ' || l_ut_owner || '.ut_cursor_data ucd where ucd.cursor_data_guid = :l_other_guid) act
188-
on exp.row_no = act.row_no
189-
where nvl(dbms_lob.compare(xmlserialize( content exp.row_data no indent), xmlserialize( content act.row_data no indent)),1) != 0'
190-
using in l_exclude_xpath, l_include_xpath, self.data_value, l_exclude_xpath, l_include_xpath, l_other.data_value;
191-
192-
--result is OK only if both are same
193-
if sql%rowcount = 0 and self.row_count = l_other.row_count then
194-
l_result := 0;
195-
else
196-
l_result := 1;
197-
end if;
198-
else
206+
if not a_other is of (ut_data_value_refcursor) then
199207
raise value_error;
200208
end if;
209+
210+
l_other := treat(a_other as ut_data_value_refcursor);
211+
l_diff_id := dbms_crypto.hash(self.data_set_guid||l_other.data_set_guid,2);
212+
-- Find differences
213+
execute immediate 'insert into ' || l_ut_owner || '.ut_data_set_diff_tmp ( diff_id, item_no )
214+
select :diff_id, nvl(exp.item_no, act.item_no)
215+
from (select '||l_column_filter||', ucd.item_no
216+
from ' || l_ut_owner || '.ut_data_set_tmp ucd where ucd.data_set_guid = :self_guid) exp
217+
full outer join
218+
(select '||l_column_filter||', ucd.item_no
219+
from ' || l_ut_owner || '.ut_data_set_tmp ucd where ucd.data_set_guid = :l_other_guid) act
220+
on exp.item_no = act.item_no
221+
where nvl(dbms_lob.compare(xmlserialize( content exp.item_data no indent), xmlserialize( content act.item_data no indent)),1) != 0'
222+
using in l_diff_id, l_exclude_xpath, l_include_xpath, self.data_set_guid, l_exclude_xpath, l_include_xpath, l_other.data_set_guid;
223+
224+
--result is OK only if both are same
225+
if sql%rowcount = 0 and self.row_count = l_other.row_count then
226+
l_result := 0;
227+
else
228+
l_result := 1;
229+
end if;
201230
return l_result;
202231
end;
203232

‎source/expectations/data_values/ut_data_value_refcursor.tps‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -31,9 +31,9 @@ create or replace type ut_data_value_refcursor under ut_data_value(
3131
row_count integer,
3232

3333
/**
34-
* Holds unique id for retrieving the cursor data from ut_cursor_data temp table
34+
* Holds unique id for retrieving the cursor data from ut_data_set_tmp temp table
3535
*/
36-
data_value raw(16),
36+
data_set_guid raw(16),
3737

3838
/**
3939
* Holds xpath (list of columns) to exclude when comparing cursors
@@ -51,6 +51,8 @@ create or replace type ut_data_value_refcursor under ut_data_value(
5151
member procedure init(self in out nocopy ut_data_value_refcursor, a_value sys_refcursor),
5252
overriding member function is_null return boolean,
5353
overriding member function to_string return varchar2,
54+
overriding member function is_diffable return boolean,
55+
overriding member function diff( a_other ut_data_value ) return varchar2,
5456
member function is_empty return boolean,
5557
overriding member function is_multi_line return boolean,
5658
overriding member function compare_implementation(a_other ut_data_value) return integer

0 commit comments

Comments
 (0)