utPLSQL · jgebal · Jun 18, 2019 · Jun 18, 2019
diff --git a/.travis.yml b/.travis.yml
@@ -25,6 +25,8 @@ env:
     - UT3_USER_PASSWORD=ut3
     - UT3_TESTER=ut3_tester
     - UT3_TESTER_PASSWORD=ut3
+    - UT3_TESTER_HELPER=ut3_tester_helper
+    - UT3_TESTER_HELPER_PASSWORD=ut3
     - UT3_TABLESPACE=users
     # Environment for building a release
     - CURRENT_BRANCH=${TRAVIS_BRANCH}
@@ -45,7 +47,7 @@ env:
     #utPLSQL released version directory
     - UTPLSQL_DIR="utPLSQL_latest_release"
     - SELFTESTING_BRANCH=${TRAVIS_BRANCH}
-    - UTPLSQL_CLI_VERSION="3.1.0"
+    - UTPLSQL_CLI_VERSION="3.1.6"
     # Maven
     - MAVEN_HOME=/usr/local/maven
     - MAVEN_CFG=$HOME/.m2
@@ -54,6 +56,7 @@ env:
     - ORACLE_VERSION="${DOCKER_TAG_12C:-12c-r1-se2-small}"  CONNECTION_STR='127.0.0.1:1521/ORCLCDB'  DOCKER_OPTIONS="-v /dev/pdbs:/opt/oracle/oradata/pdbs"
     - ORACLE_VERSION="${DOCKER_TAG_12C2:-12c-r2-se2-small}" CONNECTION_STR='127.0.0.1:1521/ORCLCDB'  DOCKER_OPTIONS="-v /dev/pdbs:/opt/oracle/oradata/pdbs"
     - ORACLE_VERSION="${DOCKER_TAG_18:-18c-se2-small}"      CONNECTION_STR='127.0.0.1:1521/ORCLCDB'  DOCKER_OPTIONS="-v /dev/pdbs:/opt/oracle/oradata/pdbs"
+    - ORACLE_VERSION="${DOCKER_TAG_19:-19c-se2-small}"      CONNECTION_STR='127.0.0.1:1521/ORCLCDB'  DOCKER_OPTIONS="-v /dev/pdbs:/opt/oracle/oradata/pdbs"
 
 cache:
   pip: true

diff --git a/.travis/install.sh b/.travis/install.sh
@@ -10,7 +10,7 @@ set verify off
 
 --alter session set plsql_warnings = 'ENABLE:ALL', 'DISABLE:(5004,5018,6000,6001,6003,6009,6010,7206)';
 alter session set plsql_optimize_level=0;
-@install_headless.sql $UT3_OWNER $UT3_OWNER_PASSWORD
+@install_headless_with_trigger.sql $UT3_OWNER $UT3_OWNER_PASSWORD
 SQL
 
 #Run this step only on second child job (12.1 - at it's fastest)
@@ -25,14 +25,18 @@ if [[ "${TRAVIS_JOB_NUMBER}" =~ \.2$ ]]; then
     set verify off
 
     @uninstall_all.sql $UT3_OWNER
+    whenever sqlerror exit failure rollback
     declare
       v_leftover_objects_count integer;
     begin
       select sum(cnt)
         into v_leftover_objects_count
-        from (select count(1) cnt from dba_objects where owner = '$UT3_OWNER'
-        union all
-        select count(1) cnt from dba_synonyms where table_owner = '$UT3_OWNER'
+        from (
+          select count(1) cnt from dba_objects where owner = '$UT3_OWNER'
+           where object_name not like 'PLSQL_PROFILER%' and object_name not like 'DBMSPCC_%'
+          union all
+          select count(1) cnt from dba_synonyms where table_owner = '$UT3_OWNER'
+           where table_name not like 'PLSQL_PROFILER%' and table_name not like 'DBMSPCC_%'
         );
       if v_leftover_objects_count > 0 then
         raise_application_error(-20000, 'Not all objects were successfully uninstalled - leftover objects count='||v_leftover_objects_count);
@@ -47,6 +51,8 @@ SQL
 
     alter session set plsql_optimize_level=0;
     @install.sql $UT3_OWNER
+    @install_ddl_trigger.sql $UT3_OWNER
+    @create_synonyms_and_grants_for_public.sql $UT3_OWNER
 SQL
 
 fi
@@ -60,27 +66,37 @@ grant select any dictionary to $UT3_OWNER;
 grant create any procedure, drop any procedure, execute any procedure to $UT3_OWNER;
 SQL
 
-#Create user that will own the tests
+#Create user that will own the tests that are relevant to internal framework
 time "$SQLCLI" sys/$ORACLE_PWD@//$CONNECTION_STR AS SYSDBA <<-SQL
 set feedback off
 @create_utplsql_owner.sql $UT3_TESTER $UT3_TESTER_PASSWORD $UT3_TABLESPACE
-
---needed for testing distributed transactions
-grant create public database link to $UT3_TESTER;
-grant drop public database link to  $UT3_TESTER;
-set feedback on
---Needed for testing coverage outside of main UT3 schema.
-grant create any procedure, drop any procedure, execute any procedure, create any type, drop any type, execute any type, under any type, select any table, update any table, insert any table, delete any table, create any table, drop any table, alter any table, select any dictionary to $UT3_TESTER;
-revoke execute on dbms_crypto from $UT3_TESTER;
-grant create job to $UT3_TESTER;
+--needed for disabling DDL trigger and testint parser without trigger enabled/present
+grant alter any trigger to ut3_tester;
+grant administer database trigger to $UT3_TESTER;
 exit
 SQL
 
-#Create additional UT3$USER# to test for special characters
+#Create additional UT3$USER# to test for special characters and front end API testing
 time "$SQLCLI" sys/$ORACLE_PWD@//$CONNECTION_STR AS SYSDBA <<-SQL
 set feedback off
 @create_utplsql_owner.sql $UT3_USER $UT3_USER_PASSWORD $UT3_TABLESPACE
---Grant UT3 framework to UT3$USER#
+--Grant UT3 framework to min user
 @create_user_grants.sql $UT3_OWNER $UT3_USER
 exit
 SQL
+
+#Create additional UT3_TESTER_HELPER that will provide a functions to allow min grant test user setup test
+time "$SQLCLI" sys/$ORACLE_PWD@//$CONNECTION_STR AS SYSDBA <<-SQL
+set feedback off
+@create_utplsql_owner.sql $UT3_TESTER_HELPER $UT3_TESTER_HELPER_PASSWORD $UT3_TABLESPACE
+--needed for testing distributed transactions
+grant create public database link to $UT3_TESTER_HELPER;
+grant drop public database link to  $UT3_TESTER_HELPER;
+set feedback on
+--Needed for testing coverage outside of main UT3 schema.
+grant create any procedure, drop any procedure, execute any procedure, create any type, drop any type, execute any type, under any type, select any table, update any table, insert any table, delete any table, create any table, drop any table, alter any table, select any dictionary, create any synonym, drop any synonym to $UT3_TESTER_HELPER;
+grant create job to $UT3_TESTER_HELPER;
+--Needed to allow for enable/disable of annotation triggers
+grant administer database trigger to $UT3_TESTER_HELPER;
+exit
+SQL
diff --git a/.travis/install_utplsql_release.sh b/.travis/install_utplsql_release.sh
@@ -36,9 +36,14 @@ end;
 /
 SQL
 
+INSTALL_FILE="install_headless_with_trigger.sql"
+if [[ ! -f "${INSTALL_FILE}" ]]; then
+ INSTALL_FILE="install_headless.sql"
+fi
+
 "$SQLCLI" sys/$ORACLE_PWD@//$CONNECTION_STR AS SYSDBA <<SQL
 alter session set plsql_optimize_level=0;
-@install_headless.sql ${UT3_RELEASE_VERSION_SCHEMA}
+@${INSTALL_FILE} ${UT3_RELEASE_VERSION_SCHEMA}
 exit
 SQL
 

diff --git a/LICENSE b/LICENSE
@@ -187,7 +187,7 @@
       same "printed page" as the copyright notice for easier
       identification within third-party archives.
 
-   Copyright 2016 utPLSQL Project
+   Copyright 2016 - 2019 utPLSQL Project
 
    Licensed under the Apache License, Version 2.0 (the "License"):
    you may not use this file except in compliance with the License.

diff --git a/VERSION b/VERSION
@@ -1 +1 @@
-v3.1.6
+v3.1.7
diff --git a/development/cleanup.sh b/development/cleanup.sh
@@ -21,16 +21,17 @@ end;
 drop user ${UT3_OWNER} cascade;
 drop user ${UT3_RELEASE_VERSION_SCHEMA} cascade;
 drop user ${UT3_TESTER} cascade;
+drop user ${UT3_TESTER_HELPER} cascade;
 drop user ${UT3_USER} cascade;
 
 begin
   for i in (
-    select decode(owner,'PUBLIC','drop public synonym "','drop synonym "'||owner||'"."')|| synonym_name ||'"' drop_orphaned_synonym from dba_synonyms a
+    select decode(owner,'PUBLIC','drop public synonym "','drop synonym "'||owner||'"."')|| synonym_name ||'"' drop_orphaned_synonym, owner||'.'||synonym_name syn from dba_synonyms a
      where not exists (select 1 from dba_objects b where (a.table_name=b.object_name and a.table_owner=b.owner or b.owner='SYS' and a.table_owner=b.object_name) )
        and a.table_owner not in ('SYS','SYSTEM')
   ) loop
-    dbms_output.put_line(i.drop_orphaned_synonym);
     execute immediate i.drop_orphaned_synonym;
+    dbms_output.put_line('synonym '||i.syn||' dropped');
   end loop;
 end;
 /

diff --git a/development/template.env.sh b/development/template.env.sh
@@ -4,7 +4,7 @@ export SQLCLI=sql # For sqlcl client
 #export SQLCLI=sqlplus # For sqlplus client
 export CONNECTION_STR=127.0.0.1:1521/xe # Adjust the connect string
 export ORACLE_PWD=oracle # Adjust your local SYS password
-export UTPLSQL_CLI_VERSION="3.1.0"
+export UTPLSQL_CLI_VERSION="3.1.6"
 export SELFTESTING_BRANCH=develop
 
 export UTPLSQL_DIR="utPLSQL_latest_release"
@@ -13,7 +13,8 @@ export UT3_OWNER_PASSWORD=ut3
 export UT3_RELEASE_VERSION_SCHEMA=ut3_latest_release
 export UT3_TESTER=ut3_tester
 export UT3_TESTER_PASSWORD=ut3
+export UT3_TESTER_HELPER=ut3_tester_helper
+export UT3_TESTER_HELPER_PASSWORD=ut3
 export UT3_TABLESPACE=users
 export UT3_USER="UT3\$USER#"
 export UT3_USER_PASSWORD=ut3
-
diff --git a/development/utplsql_style_check.sql b/development/utplsql_style_check.sql
@@ -40,8 +40,8 @@ select i.name, i.type, i.object_name, i.object_type, i.usage, i.line, i.col, cou
    and i.usage_context_id = p.usage_id
  where i.type like 'VARIABLE' and i.usage = 'DECLARATION'
    and i.object_type not in ('TYPE')
-   and (i.name not like 'L#_%' escape '#' and p.type in ('PROCEDURE','FUNCTION','ITERATOR')
-       or i.name not like 'G#_%' escape '#' and p.type not in ('PROCEDURE','FUNCTION','ITERATOR'))
+   and (i.name not like 'L#_%' escape '#' and p.type in ('PROCEDURE','FUNCTION','ITERATOR','TRIGGER')
+       or i.name not like 'G#_%' escape '#' and p.type not in ('PROCEDURE','FUNCTION','ITERATOR','TRIGGER'))
    and p.type != 'RECORD'
  order by object_name, object_type, line, col
 ;

diff --git a/docs/about/authors.md b/docs/about/authors.md
@@ -1,4 +1,4 @@
-![version](https://img.shields.io/badge/version-v3.1.6.2729-blue.svg)
+![version](https://img.shields.io/badge/version-v3.1.7.3085-blue.svg)
 
 ### utPLSQL v3 Major Contributors 
 

diff --git a/docs/about/license.md b/docs/about/license.md
@@ -1,4 +1,4 @@
-![version](https://img.shields.io/badge/version-v3.1.6.2729-blue.svg)
+![version](https://img.shields.io/badge/version-v3.1.7.3085-blue.svg)
 
 # Version Information
 

diff --git a/docs/about/project-details.md b/docs/about/project-details.md
@@ -1,4 +1,4 @@
-![version](https://img.shields.io/badge/version-v3.1.6.2729-blue.svg)
+![version](https://img.shields.io/badge/version-v3.1.7.3085-blue.svg)
 
 # utPLSQL Project Details
 

diff --git a/docs/about/support.md b/docs/about/support.md
@@ -1,4 +1,4 @@
-![version](https://img.shields.io/badge/version-v3.1.6.2729-blue.svg)
+![version](https://img.shields.io/badge/version-v3.1.7.3085-blue.svg)
 
 # How to get support
 

diff --git a/docs/index.md b/docs/index.md
@@ -1,4 +1,4 @@
-![version](https://img.shields.io/badge/version-v3.1.6.2729-blue.svg)
+![version](https://img.shields.io/badge/version-v3.1.7.3085-blue.svg)
 
 # Introduction to utPLSQL
 

diff --git a/docs/userguide/advanced_data_comparison.md b/docs/userguide/advanced_data_comparison.md
@@ -1,4 +1,4 @@
-![version](https://img.shields.io/badge/version-v3.1.6.2729-blue.svg)
+![version](https://img.shields.io/badge/version-v3.1.7.3085-blue.svg)
 
 # Advanced data comparison
 
@@ -7,10 +7,11 @@ utPLSQL expectations incorporates advanced data comparison options when comparin
 - refcursor
 - object type
 - nested table and varray  
+- json data-types
 
 Advanced data-comparison options are available for the [`equal`](expectations.md#equal) and  [`contain`](expectations.md#include--contain) matcher.
 
-## Syntax
+Syntax
 
 ```
   ut.expect( a_actual {data-type} ).to_( equal( a_expected {data-type})[.extendend_option()[.extendend_option()[...]]]);
@@ -126,6 +127,85 @@ end;
 
 ```
 
+Example of `include / exclude` for anydata.convertCollection
+
+```plsql
+create or replace type person as object(
+  name varchar2(100),
+  age  integer
+)
+/
+create or replace type people as table of person
+/
+
+create or replace package ut_anydata_inc_exc IS
+
+   --%suite(Anydata)
+
+   --%test(Anydata include)
+   procedure ut_anydata_test_inc;
+
+   --%test(Anydata exclude)
+   procedure ut_anydata_test_exc;
+
+   --%test(Fail on age)
+   procedure ut_fail_anydata_test;
+
+end ut_anydata_inc_exc;
+/
+
+create or replace package body ut_anydata_inc_exc IS
+
+   procedure ut_anydata_test_inc IS
+    l_actual           people := people(person('Matt',45));
+    l_expected         people :=people(person('Matt',47));
+  begin
+    ut3.ut.expect(anydata.convertCollection(l_actual)).to_equal(anydata.convertCollection(l_expected)).include('NAME');  
+   end;
+
+   procedure ut_anydata_test_exc IS
+    l_actual           people := people(person('Matt',45));
+    l_expected         people :=people(person('Matt',47));
+  begin
+    --Arrange
+    ut3.ut.expect(anydata.convertCollection(l_actual)).to_equal(anydata.convertCollection(l_expected)).exclude('AGE');   
+   end;
+
+   procedure ut_fail_anydata_test IS
+    l_actual           people := people(person('Matt',45));
+    l_expected         people :=people(person('Matt',47));
+  begin
+    --Arrange
+    ut3.ut.expect(anydata.convertCollection(l_actual)).to_equal(anydata.convertCollection(l_expected)).include('AGE');   
+  end;
+
+end ut_anydata_inc_exc;
+/
+
+```
+
+will result in :
+
+```sql
+Anydata
+  Anydata include [.044 sec]
+  Anydata exclude [.035 sec]
+  Fail on age [.058 sec] (FAILED - 1)
+
+Failures:
+
+  1) ut_fail_anydata_test
+      Actual: ut3.people [ count = 1 ] was expected to equal: ut3.people [ count = 1 ]
+      Diff:
+      Rows: [ 1 differences ]
+        Row No. 1 - Actual:   <AGE>45</AGE>
+        Row No. 1 - Expected: <AGE>47</AGE>
+```
+
+
+
+Example of exclude
+
 Only the columns 'RN', "A_Column" will be compared. Column 'SOME_COL' is excluded.
 
 This option can be useful in scenarios where you need to narrow-down the scope of test so that the test is only focused on very specific data.  
@@ -351,7 +431,7 @@ Unable to join sets:
   Please make sure that your join clause is not refferring to collection element
 ```
 
-***Note***
+**Note**
 >`join_by` option is slower to process as it needs to perform a cursor join.
 
 ## Defining item lists in option
@@ -424,3 +504,5 @@ Finished in .046193 seconds
 1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
 ```
 
+
+