Skip to content

Commit 2a133a1

Browse files
lwasylowlwasylow
committed
Fix to issue #770 when the merge statement is run via current user which can result in error due to missing privs to internal function
1 parent 3e685e8 commit 2a133a1

File tree

3 files changed

+59
-44
lines changed

3 files changed

+59
-44
lines changed

‎source/expectations/data_values/ut_compound_data_helper.pkb

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -541,6 +541,59 @@ create or replace package body ut_compound_data_helper is
541541
return l_no_missing_keys;
542542
end;
543543

544+
procedure update_row_and_pk_hash(a_self_data_id in raw, a_other_data_id in raw, a_exclude_xpath varchar2,
545+
a_include_xpath varchar2, a_join_by_xpath varchar2) is
546+
l_ut_owner varchar2(250) := ut_utils.ut_owner;
547+
l_column_filter varchar2(32767);
548+
l_pk_hash_sql varchar2(32767);
549+
550+
function get_column_pk_hash(a_join_by_xpath varchar2) return varchar2 is
551+
l_column varchar2(32767);
552+
begin
553+
/* due to possibility of key being to columns we cannot use xmlextractvalue
554+
usage of xmlagg is possible however it greatly complicates code and performance is impacted.
555+
xpath to be looked at or regex
556+
*/
557+
if a_join_by_xpath is not null then
558+
l_column := l_ut_owner ||'.ut_compound_data_helper.get_hash(extract(ucd.item_data,:join_by_xpath).GetClobVal()) pk_hash';
559+
else
560+
l_column := ':join_by_xpath pk_hash';
561+
end if;
562+
return l_column;
563+
end;
564+
565+
begin
566+
l_column_filter := ut_compound_data_helper.get_columns_filter(a_exclude_xpath, a_include_xpath);
567+
l_pk_hash_sql := get_column_pk_hash(a_join_by_xpath);
568+
569+
execute immediate 'merge into ' || l_ut_owner || '.ut_compound_data_tmp tgt
570+
using (
571+
select ucd_out.item_hash,
572+
ucd_out.pk_hash,
573+
ucd_out.item_no,
574+
ucd_out.data_id,
575+
row_number() over (partition by ucd_out.pk_hash,ucd_out.item_hash,ucd_out.data_id order by 1,2) duplicate_no
576+
from
577+
(
578+
select '||l_ut_owner ||'.ut_compound_data_helper.get_hash(ucd.item_data.getclobval()) item_hash,
579+
pk_hash, ucd.item_no, ucd.data_id
580+
from
581+
(
582+
select '||l_column_filter||','||l_pk_hash_sql||', item_no, data_id
583+
from ' || l_ut_owner || q'[.ut_compound_data_tmp ucd
584+
where data_id = :self_guid or data_id = :other_guid
585+
) ucd
586+
)ucd_out
587+
) src
588+
on (tgt.item_no = src.item_no and tgt.data_id = src.data_id)
589+
when matched then update
590+
set tgt.item_hash = src.item_hash,
591+
tgt.pk_hash = src.pk_hash,
592+
tgt.duplicate_no = src.duplicate_no]'
593+
using a_exclude_xpath, a_include_xpath,a_join_by_xpath,a_self_data_id, a_other_data_id;
594+
595+
end;
596+
544597
function get_unordered(a_owner in varchar2) return varchar2 is
545598
l_sql varchar2(32767);
546599
begin

‎source/expectations/data_values/ut_compound_data_helper.pks

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -83,5 +83,8 @@ create or replace package ut_compound_data_helper authid definer is
8383

8484
function get_refcursor_matcher_sql(a_owner in varchar2,a_inclusion_matcher boolean := false, a_negated_match boolean := false) return varchar2;
8585

86+
procedure update_row_and_pk_hash(a_self_data_id in raw, a_other_data_id in raw, a_exclude_xpath varchar2,
87+
a_include_xpath varchar2, a_join_by_xpath varchar2);
88+
8689
end;
8790
/

‎source/expectations/data_values/ut_compound_data_value.tpb

Lines changed: 3 additions & 44 deletions
Original file line numberDiff line numberDiff line change
@@ -198,28 +198,12 @@ create or replace type body ut_compound_data_value as
198198
a_unordered boolean , a_inclusion_compare boolean := false, a_is_negated boolean := false ) return integer is
199199
l_other ut_compound_data_value;
200200
l_ut_owner varchar2(250) := ut_utils.ut_owner;
201-
l_column_filter varchar2(32767);
202201
l_diff_id ut_compound_data_helper.t_hash;
203202
l_result integer;
204203
l_row_diffs ut_compound_data_helper.tt_row_diffs;
205204
c_max_rows constant integer := 20;
206205
l_sql varchar2(32767);
207206

208-
function get_column_pk_hash(a_join_by_xpath varchar2) return varchar2 is
209-
l_column varchar2(32767);
210-
begin
211-
/* due to possibility of key being to columns we cannot use xmlextractvalue
212-
usage of xmlagg is possible however it greatly complicates code and performance is impacted.
213-
xpath to be looked at or regex
214-
*/
215-
if a_join_by_xpath is not null then
216-
l_column := l_ut_owner ||'.ut_compound_data_helper.get_hash(extract(ucd.item_data,:join_by_xpath).GetClobVal()) pk_hash';
217-
else
218-
l_column := ':join_by_xpath pk_hash';
219-
end if;
220-
return l_column;
221-
end;
222-
223207
begin
224208
if not a_other is of (ut_compound_data_value) then
225209
raise value_error;
@@ -228,37 +212,12 @@ create or replace type body ut_compound_data_value as
228212
l_other := treat(a_other as ut_compound_data_value);
229213

230214
l_diff_id := ut_compound_data_helper.get_hash(self.data_id||l_other.data_id);
231-
l_column_filter := ut_compound_data_helper.get_columns_filter(a_exclude_xpath, a_include_xpath);
232-
215+
233216
/**
234217
* Due to incompatibility issues in XML between 11 and 12.2 and 12.1 versions we will prepopulate pk_hash upfront to
235218
* avoid optimizer incorrectly rewrite and causing NULL error or ORA-600
236-
**/
237-
execute immediate 'merge into ' || l_ut_owner || '.ut_compound_data_tmp tgt
238-
using (
239-
select ucd_out.item_hash,
240-
ucd_out.pk_hash,
241-
ucd_out.item_no,
242-
ucd_out.data_id,
243-
row_number() over (partition by ucd_out.pk_hash,ucd_out.item_hash,ucd_out.data_id order by 1,2) duplicate_no
244-
from
245-
(
246-
select '||l_ut_owner ||'.ut_compound_data_helper.get_hash(ucd.item_data.getclobval()) item_hash,
247-
pk_hash, ucd.item_no, ucd.data_id
248-
from
249-
(
250-
select '||l_column_filter||','||get_column_pk_hash(a_join_by_xpath)||', item_no, data_id
251-
from ' || l_ut_owner || q'[.ut_compound_data_tmp ucd
252-
where data_id = :self_guid or data_id = :other_guid
253-
) ucd
254-
)ucd_out
255-
) src
256-
on (tgt.item_no = src.item_no and tgt.data_id = src.data_id)
257-
when matched then update
258-
set tgt.item_hash = src.item_hash,
259-
tgt.pk_hash = src.pk_hash,
260-
tgt.duplicate_no = src.duplicate_no]'
261-
using a_exclude_xpath, a_include_xpath,a_join_by_xpath,self.data_id, l_other.data_id;
219+
**/
220+
ut_compound_data_helper.update_row_and_pk_hash(self.data_id, l_other.data_id, a_exclude_xpath,a_include_xpath,a_join_by_xpath);
262221

263222
/*!*
264223
* Comparision is based on type of search, for inclusion based search we will look for left join only.

0 commit comments

Comments
 (0)