utPLSQL · jgebal · Nov 17, 2018 · Oct 27, 2018 · Oct 28, 2018 · Oct 28, 2018
diff --git a/source/expectations/data_values/ut_compound_data_helper.pkb b/source/expectations/data_values/ut_compound_data_helper.pkb
@@ -541,5 +541,58 @@ create or replace package body ut_compound_data_helper is
     return l_no_missing_keys;
   end;
 
+  procedure update_row_and_pk_hash(a_self_data_id in raw, a_other_data_id in raw, a_exclude_xpath varchar2, 
+                                   a_include_xpath varchar2, a_join_by_xpath varchar2) is
+    l_ut_owner        varchar2(250) := ut_utils.ut_owner;
+    l_column_filter   varchar2(32767);
+    l_pk_hash_sql     varchar2(32767);
+
+    function get_column_pk_hash(a_join_by_xpath varchar2) return varchar2 is
+      l_column varchar2(32767);
+    begin
+      /* due to possibility of key being to columns we cannot use xmlextractvalue
+         usage of xmlagg is possible however it greatly complicates code and performance is impacted.
+         xpath to be looked at or regex
+      */
+      if a_join_by_xpath is not null then
+        l_column :=  l_ut_owner ||'.ut_compound_data_helper.get_hash(extract(ucd.item_data,:join_by_xpath).GetClobVal()) pk_hash';
+      else
+        l_column := ':join_by_xpath pk_hash';
+      end if;
+      return l_column;
+    end;  
+
+  begin
+    l_column_filter := ut_compound_data_helper.get_columns_filter(a_exclude_xpath, a_include_xpath);
+    l_pk_hash_sql := get_column_pk_hash(a_join_by_xpath);
+
+    execute immediate 'merge into ' || l_ut_owner || '.ut_compound_data_tmp tgt
+                       using (
+                              select ucd_out.item_hash,
+                                     ucd_out.pk_hash,
+                                     ucd_out.item_no, 
+                                     ucd_out.data_id,
+                                     row_number() over (partition by ucd_out.pk_hash,ucd_out.item_hash,ucd_out.data_id order by 1,2) duplicate_no
+                              from 
+                              (
+                              select '||l_ut_owner ||'.ut_compound_data_helper.get_hash(ucd.item_data.getclobval()) item_hash, 
+                                      pk_hash, ucd.item_no, ucd.data_id
+                              from
+                              (
+                              select '||l_column_filter||','||l_pk_hash_sql||', item_no, data_id
+                              from  ' || l_ut_owner || q'[.ut_compound_data_tmp ucd
+                              where data_id = :self_guid or data_id = :other_guid
+                              ) ucd
+                              )ucd_out
+                       ) src
+                       on (tgt.item_no = src.item_no and tgt.data_id = src.data_id)
+                       when matched then update
+                       set tgt.item_hash = src.item_hash,
+                           tgt.pk_hash = src.pk_hash,
+                           tgt.duplicate_no = src.duplicate_no]'
+                       using a_exclude_xpath, a_include_xpath,a_join_by_xpath,a_self_data_id, a_other_data_id;    
+
+  end;
+
 end;
 /
diff --git a/source/expectations/data_values/ut_compound_data_helper.pks b/source/expectations/data_values/ut_compound_data_helper.pks
@@ -81,5 +81,8 @@ create or replace package ut_compound_data_helper authid definer is
   function is_pk_exists(a_expected_cursor xmltype, a_actual_cursor xmltype, a_exclude_xpath varchar2, a_include_xpath varchar2,a_join_by_xpath varchar2) 
   return tt_missing_pk;
 
+  procedure update_row_and_pk_hash(a_self_data_id in raw, a_other_data_id in raw, a_exclude_xpath varchar2, 
+                                   a_include_xpath varchar2, a_join_by_xpath varchar2);
+
 end;
 /
diff --git a/source/expectations/data_values/ut_compound_data_value.tpb b/source/expectations/data_values/ut_compound_data_value.tpb
@@ -197,27 +197,11 @@ create or replace type body ut_compound_data_value as
   member function compare_implementation(a_other ut_data_value, a_exclude_xpath varchar2, a_include_xpath varchar2, a_join_by_xpath varchar2, a_unordered boolean ) return integer is
     l_other           ut_compound_data_value;
     l_ut_owner        varchar2(250) := ut_utils.ut_owner;
-    l_column_filter   varchar2(32767);
     l_diff_id         ut_compound_data_helper.t_hash;
     l_result          integer;
     l_row_diffs       ut_compound_data_helper.tt_row_diffs;
     c_max_rows        constant integer := 20;
 
-    function get_column_pk_hash(a_join_by_xpath varchar2) return varchar2 is
-      l_column varchar2(32767);
-    begin
-      /* due to possibility of key being to columns we cannot use xmlextractvalue
-         usage of xmlagg is possible however it greatly complicates code and performance is impacted.
-         xpath to be looked at or regex
-      */
-      if a_join_by_xpath is not null then
-        l_column :=  l_ut_owner ||'.ut_compound_data_helper.get_hash(extract(ucd.item_data,:join_by_xpath).GetClobVal()) pk_hash';
-      else
-        l_column := ':join_by_xpath pk_hash';
-      end if;
-      return l_column;
-    end;
-
   begin
     if not a_other is of (ut_compound_data_value) then
       raise value_error;
@@ -226,37 +210,12 @@ create or replace type body ut_compound_data_value as
     l_other   := treat(a_other as ut_compound_data_value);
 
     l_diff_id := ut_compound_data_helper.get_hash(self.data_id||l_other.data_id);
-    l_column_filter := ut_compound_data_helper.get_columns_filter(a_exclude_xpath, a_include_xpath);
-
+
     /**
     * Due to incompatibility issues in XML between 11 and 12.2 and 12.1 versions we will prepopulate pk_hash upfront to
     * avoid optimizer incorrectly rewrite and causing NULL error or ORA-600
-    **/        
-    execute immediate 'merge into ' || l_ut_owner || '.ut_compound_data_tmp tgt
-                       using (
-                              select ucd_out.item_hash,
-                                     ucd_out.pk_hash,
-                                     ucd_out.item_no, 
-                                     ucd_out.data_id,
-                                     row_number() over (partition by ucd_out.pk_hash,ucd_out.item_hash,ucd_out.data_id order by 1,2) duplicate_no
-                              from 
-                              (
-                              select '||l_ut_owner ||'.ut_compound_data_helper.get_hash(ucd.item_data.getclobval()) item_hash, 
-                                      pk_hash, ucd.item_no, ucd.data_id
-                              from
-                              (
-                              select '||l_column_filter||','||get_column_pk_hash(a_join_by_xpath)||', item_no, data_id
-                              from  ' || l_ut_owner || q'[.ut_compound_data_tmp ucd
-                              where data_id = :self_guid or data_id = :other_guid
-                              ) ucd
-                              )ucd_out
-                       ) src
-                       on (tgt.item_no = src.item_no and tgt.data_id = src.data_id)
-                       when matched then update
-                       set tgt.item_hash = src.item_hash,
-                           tgt.pk_hash = src.pk_hash,
-                           tgt.duplicate_no = src.duplicate_no]'
-                       using a_exclude_xpath, a_include_xpath,a_join_by_xpath,self.data_id, l_other.data_id;
+    **/   
+    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);
 
     /* Peform minus on two sets two get diffrences that will be used later on to print results */
     execute immediate 'insert into ' || l_ut_owner || '.ut_compound_data_diff_tmp ( diff_id,item_hash,pk_hash,duplicate_no)

diff --git a/test/install__min_usr_tests.sql b/test/install__min_usr_tests.sql
@@ -0,0 +1,39 @@
+set define off
+whenever sqlerror exit failure rollback
+whenever oserror exit failure rollback
+
+alter session set plsql_optimize_level=0;
+
+--Install tests
+@@min_grant_user/min_grant_user_exp.pks
+@@min_grant_user/min_grant_user_exp.pkb
+
+set linesize 200
+set define on
+set verify off
+column text format a100
+column error_count noprint new_value error_count
+
+prompt Validating installation
+
+set heading on
+select type, name, sequence, line, position, text, count(1) over() error_count
+  from all_errors
+ where owner = USER
+   and name not like 'BIN$%'  --not recycled
+   and name != 'UT_WITH_INVALID_BODY'
+   -- errors only. ignore warnings
+   and attribute = 'ERROR'
+ order by name, type, sequence
+/
+
+begin
+  if to_number('&&error_count') > 0 then
+    raise_application_error(-20000, 'Not all sources were successfully installed.');
+  else
+    dbms_output.put_line('Installation completed successfully');
+  end if;
+end;
+/
+
+exit;
diff --git a/test/install_and_run_tests.sh b/test/install_and_run_tests.sh
@@ -1,10 +1,28 @@
 #!/bin/bash
 set -ev
 
-
 #goto git root directory
 git rev-parse && cd "$(git rev-parse --show-cdup)"
 
+cd test
+
+time "$SQLCLI" ${UT3_USER}/${UT3_USER_PASSWORD}@//${CONNECTION_STR} @install__min_usr_tests.sql
+
+cd ..
+
+time utPLSQL-cli/bin/utplsql run ${UT3_USER}/${UT3_USER_PASSWORD}@${CONNECTION_STR} \
+-source_path=source -owner=ut3 \
+-test_path=test -c \
+-f=ut_documentation_reporter  -o=min_test_results.log -s \
+-scc
+
+status_line_regex="^[0-9]+ tests, ([0-9]+) failed, ([0-9]+) errored.*"
+
+RC=$(cat min_test_results.log | grep -E "${status_line_regex}" | sed -re "s/${status_line_regex}/\1\2/")
+
+if [ "$RC" == "1" ]; then
+   exit 1
+fi
 
 cd test
 
@@ -32,3 +50,4 @@ status_line_regex="^[0-9]+ tests, ([0-9]+) failed, ([0-9]+) errored.*"
 RC=$(cat test_results.log | grep -E "${status_line_regex}" | sed -re "s/${status_line_regex}/\1\2/")
 
 exit $RC
+
diff --git a/test/min_grant_user/min_grant_user_exp.pkb b/test/min_grant_user/min_grant_user_exp.pkb
@@ -0,0 +1,19 @@
+create or replace package body min_grant_user_exp is
+
+  procedure test_join_by_cursor is 
+    l_actual   SYS_REFCURSOR;
+    l_expected SYS_REFCURSOR;
+  begin
+    --Arrange
+    open l_actual for select owner, object_name,object_type from all_objects where owner = user
+    order by 1,2,3 asc;
+    open l_expected for select owner, object_name,object_type from all_objects where owner = user
+    order by 1,2,3 desc;
+
+    --Act
+    ut3.ut.expect(l_actual).to_equal(l_expected).join_by('OWNER');
+
+  end;
+
+end;
+/
diff --git a/test/min_grant_user/min_grant_user_exp.pks b/test/min_grant_user/min_grant_user_exp.pks
@@ -0,0 +1,9 @@
+create or replace package min_grant_user_exp is
+
+  --%suite(minimum grant user tests)
+
+  --%test(execute join by test)
+  procedure test_join_by_cursor;  
+
+end;
+/