Skip to content

Commit cc718d9

Browse files
lwasylowlwasylow
committed
Added changes to cater for nested tables
Updated documentation Shift packages around for current user id
1 parent 44cbcc8 commit cc718d9

16 files changed

Lines changed: 466 additions & 95 deletions

‎docs/userguide/advanced_data_comparison.md‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -139,7 +139,9 @@ Above test will result in two differences of one row extra and one row missing.
139139

140140
## Join By option
141141

142-
You can now join two cursors by defining a primary key or composite key that will be used to uniquely identify and compare rows. This option allows us to exactly show which rows are missing, extra and which are different without ordering clause.
142+
You can now join two cursors by defining a primary key or composite key that will be used to uniquely identify and compare rows. This option allows us to exactly show which rows are missing, extra and which are different without ordering clause. In the situation where the join key is not unique, join will partition set over rows with a same key and join on row number as well as given join key. The extra rows or missing will be presented to user as well as not matching rows.
143+
144+
Join by option can be used in conjunction with include or exclude options. However if any of the join keys is part of exclude set, comparison will fail and report to user that sets could not be joined on specific key (excluded).
143145

144146
```sql
145147
procedure join_by_username is

‎source/core/types/ut_key_anyval_pair.tps‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
create or replace type ut_key_anyval_pair force as object(
2+
/*
3+
utPLSQL - Version 3
4+
Copyright 2016 - 2017 utPLSQL Project
5+
6+
Licensed under the Apache License, Version 2.0 (the "License"):
7+
you may not use this file except in compliance with the License.
8+
You may obtain a copy of the License at
9+
10+
http://www.apache.org/licenses/LICENSE-2.0
11+
12+
Unless required by applicable law or agreed to in writing, software
13+
distributed under the License is distributed on an "AS IS" BASIS,
14+
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15+
See the License for the specific language governing permissions and
16+
limitations under the License.
17+
*/
18+
key varchar2(4000)
19+
) not final
20+
/

‎source/core/types/ut_key_anyval_pairs.tps‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
create or replace type ut_key_anyval_pairs as
2+
/*
3+
utPLSQL - Version 3
4+
Copyright 2016 - 2017 utPLSQL Project
5+
6+
Licensed under the Apache License, Version 2.0 (the "License"):
7+
you may not use this file except in compliance with the License.
8+
You may obtain a copy of the License at
9+
10+
http://www.apache.org/licenses/LICENSE-2.0
11+
12+
Unless required by applicable law or agreed to in writing, software
13+
distributed under the License is distributed on an "AS IS" BASIS,
14+
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15+
See the License for the specific language governing permissions and
16+
limitations under the License.
17+
*/
18+
table of ut_key_anyval_pair
19+
/

‎source/core/types/ut_key_value_pair.tps‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
create or replace type ut_key_value_pair as object(
1+
create or replace type ut_key_value_pair force as object(
22
/*
33
utPLSQL - Version 3
44
Copyright 2016 - 2017 utPLSQL Project

‎source/core/types/ut_key_varcharvalue_pair.tps‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
create or replace type ut_key_varcharvalue_pair under ut_key_anyval_pair (
2+
/*
3+
utPLSQL - Version 3
4+
Copyright 2016 - 2017 utPLSQL Project
5+
6+
Licensed under the Apache License, Version 2.0 (the "License"):
7+
you may not use this file except in compliance with the License.
8+
You may obtain a copy of the License at
9+
10+
http://www.apache.org/licenses/LICENSE-2.0
11+
12+
Unless required by applicable law or agreed to in writing, software
13+
distributed under the License is distributed on an "AS IS" BASIS,
14+
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15+
See the License for the specific language governing permissions and
16+
limitations under the License.
17+
*/
18+
value varchar2(4000)
19+
)
20+
/

‎source/core/types/ut_key_xmlvalue_pair.tps‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
create or replace type ut_key_xmlvalue_pair under ut_key_anyval_pair (
2+
/*
3+
utPLSQL - Version 3
4+
Copyright 2016 - 2017 utPLSQL Project
5+
6+
Licensed under the Apache License, Version 2.0 (the "License"):
7+
you may not use this file except in compliance with the License.
8+
You may obtain a copy of the License at
9+
10+
http://www.apache.org/licenses/LICENSE-2.0
11+
12+
Unless required by applicable law or agreed to in writing, software
13+
distributed under the License is distributed on an "AS IS" BASIS,
14+
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15+
See the License for the specific language governing permissions and
16+
limitations under the License.
17+
*/
18+
value xmltype
19+
)
20+
/

‎source/expectations/data_values/ut_compound_data_helper.pkb‎

Lines changed: 19 additions & 72 deletions
Original file line numberDiff line numberDiff line change
@@ -16,55 +16,23 @@ create or replace package body ut_compound_data_helper is
1616
limitations under the License.
1717
*/
1818

19-
type t_type_name_map is table of varchar2(100) index by binary_integer;
20-
g_type_name_map t_type_name_map;
21-
22-
function get_column_type(a_desc_rec dbms_sql.desc_rec3) return varchar2 is
23-
l_result varchar2(500) := 'unknown datatype';
24-
begin
25-
if g_type_name_map.exists(a_desc_rec.col_type) then
26-
l_result := g_type_name_map(a_desc_rec.col_type);
27-
elsif a_desc_rec.col_schema_name is not null and a_desc_rec.col_type_name is not null then
28-
l_result := a_desc_rec.col_schema_name||'.'||a_desc_rec.col_type_name;
29-
end if;
30-
return l_result;
31-
end;
32-
33-
function get_columns_info(a_columns_tab dbms_sql.desc_tab3, a_columns_count integer) return ut_key_value_pairs is
34-
l_result ut_key_value_pairs := ut_key_value_pairs();
35-
begin
36-
for i in 1 .. a_columns_count loop
37-
l_result.extend;
38-
l_result(l_result.last) := ut_key_value_pair(a_columns_tab(i).col_name, get_column_type(a_columns_tab(i)));
39-
end loop;
40-
return l_result;
41-
end;
42-
43-
function get_columns_info(a_cursor in out nocopy sys_refcursor) return xmltype is
44-
l_cursor_number integer;
45-
l_columns_count pls_integer;
46-
l_columns_desc dbms_sql.desc_tab3;
47-
l_result xmltype;
48-
l_columns_tab ut_key_value_pairs;
49-
begin
50-
if a_cursor is null or not a_cursor%isopen then
51-
return null;
52-
end if;
53-
l_cursor_number := dbms_sql.to_cursor_number( a_cursor );
54-
dbms_sql.describe_columns3( l_cursor_number, l_columns_count, l_columns_desc );
55-
a_cursor := dbms_sql.to_refcursor( l_cursor_number );
56-
l_columns_tab := get_columns_info( l_columns_desc, l_columns_count);
57-
58-
select
59-
XMLELEMENT("ROW", xmlagg(xmlelement(evalname ut_utils.xmlgen_escaped_string(key),
60-
XMLATTRIBUTES(key AS "xml_valid_name"),
61-
value)))
62-
into l_result
63-
from table(l_columns_tab );
64-
65-
return l_result;
66-
end;
67-
19+
g_user_defined_type pls_integer := dbms_sql.user_defined_type;
20+
21+
function get_column_info_xml(a_column_details ut_key_anyval_pair) return xmltype is
22+
l_result varchar2(4000);
23+
l_res xmltype;
24+
begin
25+
l_result := '<'||ut_utils.xmlgen_escaped_string(a_column_details.KEY)||' xml_valid_name="'||ut_utils.xmlgen_escaped_string(a_column_details.KEY)||'">';
26+
if a_column_details is of(ut_key_xmlvalue_pair) then
27+
l_result := l_result || (treat(a_column_details as ut_key_xmlvalue_pair).value.getstringval());
28+
else
29+
l_result := l_result || ut_utils.xmlgen_escaped_string((treat(a_column_details as ut_key_varcharvalue_pair).value));
30+
end if;
31+
l_result := l_result ||'</'||ut_utils.xmlgen_escaped_string(a_column_details.KEY)||'>';
32+
33+
return xmltype(l_result);
34+
end;
35+
6836
function get_columns_filter(
6937
a_exclude_xpath varchar2, a_include_xpath varchar2,
7038
a_table_alias varchar2 := 'ucd', a_column_alias varchar2 := 'item_data'
@@ -524,6 +492,7 @@ create or replace package body ut_compound_data_helper is
524492
l_pk_xpath_tabs ut_varchar2_list := ut_varchar2_list();
525493
l_column_filter varchar2(32767);
526494
l_no_missing_keys tt_missing_pk := tt_missing_pk();
495+
527496
begin
528497
if a_join_by_xpath is not null then
529498
l_pk_xpath_tabs := ut_utils.string_to_table(a_join_by_xpath,'|');
@@ -559,28 +528,6 @@ create or replace package body ut_compound_data_helper is
559528

560529
return l_no_missing_keys;
561530
end;
562-
563-
564-
begin
565-
g_type_name_map( dbms_sql.binary_bouble_type ) := 'BINARY_DOUBLE';
566-
g_type_name_map( dbms_sql.bfile_type ) := 'BFILE';
567-
g_type_name_map( dbms_sql.binary_float_type ) := 'BINARY_FLOAT';
568-
g_type_name_map( dbms_sql.blob_type ) := 'BLOB';
569-
g_type_name_map( dbms_sql.long_raw_type ) := 'LONG RAW';
570-
g_type_name_map( dbms_sql.char_type ) := 'CHAR';
571-
g_type_name_map( dbms_sql.clob_type ) := 'CLOB';
572-
g_type_name_map( dbms_sql.long_type ) := 'LONG';
573-
g_type_name_map( dbms_sql.date_type ) := 'DATE';
574-
g_type_name_map( dbms_sql.interval_day_to_second_type ) := 'INTERVAL DAY TO SECOND';
575-
g_type_name_map( dbms_sql.interval_year_to_month_type ) := 'INTERVAL YEAR TO MONTH';
576-
g_type_name_map( dbms_sql.raw_type ) := 'RAW';
577-
g_type_name_map( dbms_sql.timestamp_type ) := 'TIMESTAMP';
578-
g_type_name_map( dbms_sql.timestamp_with_tz_type ) := 'TIMESTAMP WITH TIME ZONE';
579-
g_type_name_map( dbms_sql.timestamp_with_local_tz_type ) := 'TIMESTAMP WITH LOCAL TIME ZONE';
580-
g_type_name_map( dbms_sql.varchar2_type ) := 'VARCHAR2';
581-
g_type_name_map( dbms_sql.number_type ) := 'NUMBER';
582-
g_type_name_map( dbms_sql.rowid_type ) := 'ROWID';
583-
g_type_name_map( dbms_sql.urowid_type ) := 'UROWID';
584-
531+
585532
end;
586533
/

‎source/expectations/data_values/ut_compound_data_helper.pks‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,7 @@ create or replace package ut_compound_data_helper authid definer is
4848

4949
type tt_row_diffs is table of t_row_diffs;
5050

51-
function get_columns_info(a_cursor in out nocopy sys_refcursor) return xmltype;
51+
function get_column_info_xml(a_column_details ut_key_anyval_pair) return xmltype;
5252

5353
function get_columns_filter(
5454
a_exclude_xpath varchar2, a_include_xpath varchar2,

0 commit comments

Comments
 (0)