Skip to content

Commit 08ed9ab

Browse files
jgebaljgebal
committed
Refactoring - to be continued.
1 parent 94e58a0 commit 08ed9ab

23 files changed

Lines changed: 377 additions & 467 deletions

‎source/core/ut_metadata.pkb‎

Lines changed: 75 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -189,5 +189,80 @@ create or replace package body ut_metadata as
189189
return l_cnt > 0;
190190
end;
191191

192+
function is_collection (a_anytype_code in integer) return boolean is
193+
begin
194+
return coalesce(a_anytype_code in (dbms_types.typecode_varray,dbms_types.typecode_table,dbms_types.typecode_namedcollection),false);
195+
end;
196+
197+
function is_collection (a_owner varchar2, a_type_name varchar2) return boolean is
198+
begin
199+
return is_collection(
200+
get_anytype_members_info(
201+
get_user_defined_type(a_owner, a_type_name)
202+
).type_code
203+
);
204+
end;
205+
206+
function get_attr_elem_info( a_anytype anytype, a_pos pls_integer := null )
207+
return t_anytype_elem_info_rec is
208+
l_result t_anytype_elem_info_rec;
209+
begin
210+
if a_anytype is not null then
211+
l_result.type_code := a_anytype.getattreleminfo(
212+
pos => a_pos,
213+
prec => l_result.precision,
214+
scale => l_result.scale,
215+
len => l_result.length,
216+
csid => l_result.char_set_id,
217+
csfrm => l_result.char_set_frm,
218+
attr_elt_type => l_result.attr_elt_type,
219+
aname => l_result.attribute_name
220+
);
221+
end if;
222+
return l_result;
223+
end;
224+
225+
function get_anytype_members_info( a_anytype anytype )
226+
return t_anytype_members_rec is
227+
l_result t_anytype_members_rec;
228+
begin
229+
if a_anytype is not null then
230+
l_result.type_code := a_anytype.getinfo(
231+
prec => l_result.precision,
232+
scale => l_result.scale,
233+
len => l_result.length,
234+
csid => l_result.char_set_id,
235+
csfrm => l_result.char_set_frm,
236+
schema_name => l_result.schema_name,
237+
type_name => l_result.type_name,
238+
version => l_result.version,
239+
numelems => l_result.elements_count
240+
);
241+
end if;
242+
return l_result;
243+
end;
244+
245+
function get_user_defined_type(a_owner varchar2, a_type_name varchar2) return anytype is
246+
l_anytype anytype;
247+
not_found exception;
248+
pragma exception_init(not_found,-22303);
249+
begin
250+
if a_type_name is not null then
251+
begin
252+
if ut_metadata.is_object_visible('GETANYTYPEFROMPERSISTENT') then
253+
execute immediate 'begin :l_anytype := getanytypefrompersistent( :a_owner, :a_type_name ); end;'
254+
using out l_anytype, in nvl(a_owner,sys_context('userenv','current_schema')), in a_type_name;
255+
else
256+
execute immediate 'begin :l_anytype := anytype.getpersistent( :a_owner, :a_type_name ); end;'
257+
using out l_anytype, in nvl(a_owner,sys_context('userenv','current_schema')), in a_type_name;
258+
end if;
259+
exception
260+
when not_found then
261+
null;
262+
end;
263+
end if;
264+
return l_anytype;
265+
end;
266+
192267
end;
193268
/

‎source/core/ut_metadata.pks‎

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,31 @@ create or replace package ut_metadata authid current_user as
2020
* Common package for all code that reads from the system tables.
2121
*/
2222

23+
type t_anytype_members_rec is record (
24+
type_code pls_integer,
25+
schema_name varchar2(128),
26+
type_name varchar2(128),
27+
length pls_integer,
28+
elements_count pls_integer,
29+
version varchar2(32767),
30+
precision pls_integer,
31+
scale pls_integer,
32+
char_set_id pls_integer,
33+
char_set_frm pls_integer
34+
);
35+
36+
type t_anytype_elem_info_rec is record (
37+
type_code pls_integer,
38+
attribute_name varchar2(260),
39+
length pls_integer,
40+
version varchar2(32767),
41+
precision pls_integer,
42+
scale pls_integer,
43+
char_set_id pls_integer,
44+
char_set_frm pls_integer,
45+
attr_elt_type anytype
46+
);
47+
2348
/**
2449
* Forms correct object/subprogram name to call as owner.object[.subprogram]
2550
*
@@ -91,5 +116,30 @@ create or replace package ut_metadata authid current_user as
91116
*/
92117
function package_exists_in_cur_schema(a_object_name varchar2) return boolean;
93118

119+
/**
120+
* Returns true if given typecode is a collection typecode
121+
*/
122+
function is_collection(a_anytype_code in integer) return boolean;
123+
124+
/**
125+
* Returns true if given object is a collection
126+
*/
127+
function is_collection(a_owner varchar2, a_type_name varchar2) return boolean;
128+
129+
/**
130+
* Returns a descriptor of anytype
131+
*/
132+
function get_anytype_members_info( a_anytype anytype ) return t_anytype_members_rec;
133+
134+
/**
135+
* Returns a descriptor of anytype attribute
136+
*/
137+
function get_attr_elem_info( a_anytype anytype, a_pos pls_integer := null ) return t_anytype_elem_info_rec;
138+
139+
/**
140+
* Returns ANYTYPE descriptor of an object type
141+
*/
142+
function get_user_defined_type(a_owner varchar2, a_type_name varchar2) return anytype;
143+
94144
end ut_metadata;
95145
/

‎source/create_synonyms_and_grants_for_public.sql‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -104,8 +104,8 @@ grant execute on &&ut3_owner..ut_realtime_reporter to public;
104104
grant select, insert, delete, update on &&ut3_owner..dbmspcc_blocks to public;
105105
grant select, insert, delete, update on &&ut3_owner..dbmspcc_runs to public;
106106
grant select, insert, delete, update on &&ut3_owner..dbmspcc_units to public;
107-
grant execute on &&ut3_owner..ut_matcher_config to public;
108-
grant execute on &&ut3_owner..ut_matcher_config_items to public;
107+
grant execute on &&ut3_owner..ut_matcher_options to public;
108+
grant execute on &&ut3_owner..ut_matcher_options_items to public;
109109

110110
prompt Creating synonyms for UTPLSQL objects in &&ut3_owner schema to PUBLIC
111111

‎source/create_user_grants.sql‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -123,6 +123,6 @@ grant execute on &&ut3_owner..ut_realtime_reporter to &ut3_user;
123123
grant select, insert, delete, update on &&ut3_owner..dbmspcc_blocks to &ut3_user;
124124
grant select, insert, delete, update on &&ut3_owner..dbmspcc_runs to &ut3_user;
125125
grant select, insert, delete, update on &&ut3_owner..dbmspcc_units to &ut3_user;
126-
grant execute on &&ut3_owner..ut_matcher_config to &ut3_user;
127-
grant execute on &&ut3_owner..ut_matcher_config_items to &ut3_user;
126+
grant execute on &&ut3_owner..ut_matcher_options to &ut3_user;
127+
grant execute on &&ut3_owner..ut_matcher_options_items to &ut3_user;
128128

‎source/expectations/data_values/ut_compound_data_helper.pkb‎

Lines changed: 6 additions & 94 deletions
Original file line numberDiff line numberDiff line change
@@ -346,18 +346,20 @@ create or replace package body ut_compound_data_helper is
346346

347347
begin
348348
dbms_lob.createtemporary(l_compare_sql, true);
349-
gen_sql_pieces_out_of_cursor(a_other.cursor_details.cursor_columns_info, a_join_by_list,
349+
gen_sql_pieces_out_of_cursor(
350+
a_other.cursor_details.cursor_columns_info, a_join_by_list,
350351
l_xmltable_stmt, l_select_stmt, l_partition_stmt, l_equal_stmt,
351-
l_join_on_stmt, l_not_equal_stmt);
352+
l_join_on_stmt, l_not_equal_stmt
353+
);
352354

353355
l_temp_string := 'with exp as ( select ucd.* ';
354356
ut_utils.append_to_clob(l_compare_sql, l_temp_string);
355-
get_act_and_exp_set(l_compare_sql, l_partition_stmt,l_select_stmt, l_xmltable_stmt, a_unordered,'exp');
357+
get_act_and_exp_set(l_compare_sql, l_partition_stmt, l_select_stmt, l_xmltable_stmt, a_unordered,'exp');
356358

357359

358360
l_temp_string :=',act as ( select ucd.* ';
359361
ut_utils.append_to_clob(l_compare_sql, l_temp_string);
360-
get_act_and_exp_set(l_compare_sql, l_partition_stmt,l_select_stmt, l_xmltable_stmt, a_unordered,'act');
362+
get_act_and_exp_set(l_compare_sql, l_partition_stmt, l_select_stmt, l_xmltable_stmt, a_unordered,'act');
361363

362364
l_temp_string := ' select a.item_data as act_item_data, a.data_id act_data_id,'
363365
||'e.item_data as exp_item_data, e.data_id exp_data_id, '||
@@ -629,57 +631,6 @@ create or replace package body ut_compound_data_helper is
629631
return g_diff_count;
630632
end;
631633

632-
function get_missing_filter_columns(a_cursor_info ut_cursor_column_tab, a_column_filter_list ut_varchar2_list)
633-
return ut_varchar2_list is
634-
l_result ut_varchar2_list := ut_varchar2_list();
635-
begin
636-
select fl.column_value
637-
bulk collect into l_result
638-
from table(a_column_filter_list) fl
639-
where not exists (select 1 from table(a_cursor_info) c where regexp_like(c.access_path, '^'||fl.column_value||'($|/.*)'));
640-
return l_result;
641-
end;
642-
643-
function get_missing_pk(a_expected ut_cursor_column_tab, a_actual ut_cursor_column_tab, a_current_list ut_varchar2_list)
644-
return tt_missing_pk is
645-
l_actual ut_varchar2_list := coalesce(get_missing_filter_columns(a_actual,a_current_list),ut_varchar2_list());
646-
l_expected ut_varchar2_list := coalesce(get_missing_filter_columns(a_expected,a_current_list),ut_varchar2_list());
647-
l_missing_pk tt_missing_pk;
648-
begin
649-
select name,type
650-
bulk collect into l_missing_pk
651-
from
652-
(select act.column_value name, 'e' type from table(l_expected) act
653-
union all
654-
select exp.column_value name, 'a' type from table(l_actual) exp)
655-
order by type desc,name;
656-
return l_missing_pk;
657-
end;
658-
659-
function contains_collection (a_cursor_info ut_cursor_column_tab)
660-
return number is
661-
l_collection_elements number;
662-
begin
663-
select count(1) into l_collection_elements from
664-
table(a_cursor_info) c where c.is_collection = 1;
665-
return l_collection_elements;
666-
end;
667-
668-
function remove_incomparable_cols( a_cursor_details ut_cursor_column_tab,a_incomparable_cols ut_varchar2_list)
669-
return ut_cursor_column_tab is
670-
l_result ut_cursor_column_tab;
671-
begin
672-
select ut_cursor_column(i.parent_name,i.access_path,i.has_nested_col,i.transformed_name,i.hierarchy_level,i.column_position ,
673-
i.xml_valid_name,i.column_name,i.column_type,i.column_type_name ,i.column_schema,i.column_len,i.is_sql_diffable ,i.is_collection)
674-
bulk collect into l_result
675-
from table(a_cursor_details) i
676-
left outer join table(a_incomparable_cols) c
677-
on (i.access_path = c.column_value)
678-
where c.column_value is null;
679-
680-
return l_result;
681-
end;
682-
683634
function getxmlchildren(a_parent_name varchar2,a_cursor_table ut_cursor_column_tab)
684635
return xmltype is
685636
l_result xmltype;
@@ -722,45 +673,6 @@ create or replace package body ut_compound_data_helper is
722673
end;
723674
end;
724675

725-
function get_anytype_members_info( a_anytype anytype )
726-
return t_anytype_members_rec is
727-
l_result t_anytype_members_rec;
728-
begin
729-
if a_anytype is not null then
730-
l_result.type_code := a_anytype.getinfo(
731-
prec => l_result.precision,
732-
scale => l_result.scale,
733-
len => l_result.length,
734-
csid => l_result.char_set_id,
735-
csfrm => l_result.char_set_frm,
736-
schema_name => l_result.schema_name,
737-
type_name => l_result.type_name,
738-
version => l_result.version,
739-
numelems => l_result.elements_count
740-
);
741-
end if;
742-
return l_result;
743-
end;
744-
745-
function get_attr_elem_info( a_anytype anytype, a_pos pls_integer := null )
746-
return t_anytype_elem_info_rec is
747-
l_result t_anytype_elem_info_rec;
748-
begin
749-
if a_anytype is not null then
750-
l_result.type_code := a_anytype.getattreleminfo(
751-
pos => a_pos,
752-
prec => l_result.precision,
753-
scale => l_result.scale,
754-
len => l_result.length,
755-
csid => l_result.char_set_id,
756-
csfrm => l_result.char_set_frm,
757-
attr_elt_type => l_result.attr_elt_type,
758-
aname => l_result.attribute_name
759-
);
760-
end if;
761-
return l_result;
762-
end;
763-
764676
begin
765677
g_anytype_name_map(dbms_types.typecode_date) := 'DATE';
766678
g_anytype_name_map(dbms_types.typecode_number) := 'NUMBER';

‎source/expectations/data_values/ut_compound_data_helper.pks‎

Lines changed: 0 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -31,13 +31,6 @@ create or replace package ut_compound_data_helper authid definer is
3131

3232
type tt_column_diffs is table of t_column_diffs;
3333

34-
type t_missing_pk is record(
35-
missingxpath varchar2(250),
36-
diff_type varchar2(1)
37-
);
38-
39-
type tt_missing_pk is table of t_missing_pk;
40-
4134
type t_row_diffs is record(
4235
rn integer,
4336
diff_type varchar2(250),
@@ -56,31 +49,6 @@ create or replace package ut_compound_data_helper authid definer is
5649
dup_no number
5750
);
5851

59-
type t_anytype_members_rec is record (
60-
type_code pls_integer,
61-
schema_name varchar2(128),
62-
type_name varchar2(128),
63-
length pls_integer,
64-
elements_count pls_integer,
65-
version varchar2(32767),
66-
precision pls_integer,
67-
scale pls_integer,
68-
char_set_id pls_integer,
69-
char_set_frm pls_integer
70-
);
71-
72-
type t_anytype_elem_info_rec is record (
73-
type_code pls_integer,
74-
attribute_name varchar2(260),
75-
length pls_integer,
76-
version varchar2(32767),
77-
precision pls_integer,
78-
scale pls_integer,
79-
char_set_id pls_integer,
80-
char_set_frm pls_integer,
81-
attr_elt_type anytype
82-
);
83-
8452
type t_diff_tab is table of t_diff_rec;
8553

8654
function get_columns_filter(
@@ -125,26 +93,11 @@ create or replace package ut_compound_data_helper authid definer is
12593

12694
function get_rows_diff_count return integer;
12795

128-
function get_missing_pk(
129-
a_expected ut_cursor_column_tab, a_actual ut_cursor_column_tab, a_current_list ut_varchar2_list
130-
) return tt_missing_pk;
131-
132-
function contains_collection (a_cursor_info ut_cursor_column_tab) return number;
133-
134-
function remove_incomparable_cols(
135-
a_cursor_details ut_cursor_column_tab,a_incomparable_cols ut_varchar2_list
136-
) return ut_cursor_column_tab;
137-
13896
function getxmlchildren(a_parent_name varchar2,a_cursor_table ut_cursor_column_tab) return xmltype;
13997

14098
function is_sql_compare_allowed(a_type_name varchar2) return boolean;
14199

142100
function get_column_type_desc(a_type_code in integer, a_dbms_sql_desc in boolean) return varchar2;
143101

144-
145-
function get_anytype_members_info( a_anytype anytype ) return t_anytype_members_rec;
146-
147-
function get_attr_elem_info( a_anytype anytype, a_pos pls_integer := null ) return t_anytype_elem_info_rec;
148-
149102
end;
150103
/

0 commit comments

Comments
 (0)