utPLSQL · jgebal · Mar 20, 2019 · Mar 20, 2019 · Mar 20, 2019
diff --git a/source/create_synonyms_and_grants_for_public.sql b/source/create_synonyms_and_grants_for_public.sql
@@ -107,6 +107,8 @@ grant select, insert, delete, update on &&ut3_owner..dbmspcc_units  to public;
 grant execute on &&ut3_owner..ut_matcher_options to public;
 grant execute on &&ut3_owner..ut_matcher_options_items to public;
 grant execute on &&ut3_owner..ut_run_info to public;
+grant execute on &&ut3_owner..ut_cursor_column_tab to public;
+grant execute on &&ut3_owner..ut_cursor_details to public;
 
 prompt Creating synonyms for UTPLSQL objects in &&ut3_owner schema to PUBLIC
 
@@ -167,3 +169,5 @@ create public synonym dbmspcc_blocks for &&ut3_owner..dbmspcc_blocks;
 create public synonym dbmspcc_runs for &&ut3_owner..dbmspcc_runs;
 create public synonym dbmspcc_units for &&ut3_owner..dbmspcc_units;
 create public synonym ut_run_info for &&ut3_owner..ut_run_info;
+create public synonym ut_cursor_column_tab for &&ut3_owner..ut_cursor_column_tab;
+create public synonym ut_cursor_details for &&ut3_owner..ut_cursor_details;
diff --git a/source/create_user_grants.sql b/source/create_user_grants.sql
@@ -126,4 +126,5 @@ grant select, insert, delete, update on &&ut3_owner..dbmspcc_units to &ut3_user;
 grant execute on &&ut3_owner..ut_matcher_options to &ut3_user;
 grant execute on &&ut3_owner..ut_matcher_options_items to &ut3_user;
 grant execute on &&ut3_owner..ut_run_info to &ut3_user;
-
+grant execute on &&ut3_owner..ut_cursor_column_tab to &ut3_user;
+grant execute on &&ut3_owner..ut_cursor_details to &ut3_user;
diff --git a/source/create_user_synonyms.sql b/source/create_user_synonyms.sql
@@ -109,3 +109,5 @@ create or replace synonym &ut3_user..dbmspcc_blocks for &&ut3_owner..dbmspcc_blo
 create or replace synonym &ut3_user..dbmspcc_runs for &&ut3_owner..dbmspcc_runs;
 create or replace synonym &ut3_user..dbmspcc_units for &&ut3_owner..dbmspcc_units;
 create or replace synonym &ut3_user..ut_run_info for &&ut3_owner..ut_run_info;
+create or replace synonym &ut3_user..ut_cursor_column_tab for &&ut3_owner..ut_cursor_column_tab;
+create or replace synonym &ut3_user..ut_cursor_details for &&ut3_owner..ut_cursor_details;
diff --git a/test/core/min_grant_user/test_min_grant_user.pkb b/test/core/min_grant_user/test_min_grant_user.pkb
@@ -1,15 +1,60 @@
-create or replace package body test_min_grant_user is
-
-  procedure test_join_by_cursor is
-    l_results clob;
-  begin
-    execute immediate 'begin ut3$user#.test_cursor_grants.run(); end;';
-    l_results := core.get_dbms_output_as_clob();
-    --Assert
-    ut.expect( l_results ).to_be_like( '%execute join by test [% sec]' ||
-    '%1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)%' );
-
-  end;
-
-end;
-/
+create or replace package body test_min_grant_user is
+
+  procedure test_join_by_cursor is
+    l_results clob;
+  begin
+    execute immediate 'begin ut3$user#.test_cursor_grants.run_test_join_by_cursor(); end;';
+    l_results := core.get_dbms_output_as_clob();
+    --Assert
+    ut.expect( l_results ).to_be_like( '%execute join by test [% sec]' ||
+    '%1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)%' );
+
+  end;
+
+  procedure test_equal_cursor is
+    l_results clob;
+  begin
+    execute immediate 'begin ut3$user#.test_cursor_grants.run_test_equal_cursor(); end;';
+    l_results := core.get_dbms_output_as_clob();
+    --Assert
+    ut.expect( l_results ).to_be_like( '%execute equal test [% sec]' ||
+    '%1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)%' );
+
+  end;
+
+  procedure test_not_empty_cursor is
+    l_results clob;
+  begin
+    execute immediate 'begin ut3$user#.test_cursor_grants.run_test_not_empty_cursor(); end;';
+    l_results := core.get_dbms_output_as_clob();
+    --Assert
+    ut.expect( l_results ).to_be_like( '%execute not_empty test [% sec]' ||
+    '%1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)%' );
+
+  end;
+
+
+  procedure test_have_count_cursor is
+    l_results clob;
+  begin
+    execute immediate 'begin ut3$user#.test_cursor_grants.run_test_have_count_cursor(); end;';
+    l_results := core.get_dbms_output_as_clob();
+    --Assert
+    ut.expect( l_results ).to_be_like( '%execute have_count test [% sec]' ||
+    '%1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)%' );
+
+  end;
+
+  procedure test_empty_cursor is
+    l_results clob;
+  begin
+    execute immediate 'begin ut3$user#.test_cursor_grants.run_test_empty_cursor(); end;';
+    l_results := core.get_dbms_output_as_clob();
+    --Assert
+    ut.expect( l_results ).to_be_like( '%execute empty test [% sec]' ||
+    '%1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)%' );
+
+  end;
+
+end;
+/
diff --git a/test/core/min_grant_user/test_min_grant_user.pks b/test/core/min_grant_user/test_min_grant_user.pks
@@ -1,10 +1,22 @@
-create or replace package test_min_grant_user is
-
-  --%suite(minimum grant user tests)
-  --%suitepath(utplsql.core)
-
-  --%test(execute join by test)
-  procedure test_join_by_cursor;  
-
-end;
-/
+create or replace package test_min_grant_user is
+
+  --%suite(minimum grant user tests)
+  --%suitepath(utplsql.core)
+
+  --%test(execute join by test)
+  procedure test_join_by_cursor;  
+
+   --%test(execute equal test)
+  procedure test_equal_cursor;  
+
+   --%test(execute not_empty test)
+  procedure test_not_empty_cursor;  
+
+   --%test(execute have_count test)
+  procedure test_have_count_cursor;  
+
+   --%test(execute empty test)
+  procedure test_empty_cursor;  
+
+end;
+/
diff --git a/test/helpers/test_dummy_object.tps b/test/helpers/test_dummy_object.tps
@@ -13,4 +13,7 @@ create or replace type test_dummy_object as object (
   "name"  varchar2(30),
   "Value" varchar2(30)
 )
-/
+/
+
+
+grant execute on test_dummy_object to ut3$user#;
diff --git a/test/helpers/test_dummy_object_list.tps b/test/helpers/test_dummy_object_list.tps
@@ -1,2 +1,4 @@
 create or replace type test_dummy_object_list as table of test_dummy_object
 /
+
+grant execute on test_dummy_object_list to ut3$user#;
diff --git a/test/helpers/ut3user#.test_cursor_grants.pkb b/test/helpers/ut3user#.test_cursor_grants.pkb
@@ -1,23 +1,96 @@
-create or replace package body ut3$user#.test_cursor_grants is
-
-  procedure run is
-  begin
-    ut3.ut.run('test_cursor_grants');
-  end;
-
-  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;
-/
+create or replace package body ut3$user#.test_cursor_grants is
+
+  procedure run_test_join_by_cursor is
+  begin
+    ut3.ut.run('test_cursor_grants.test_join_by_cursor');
+  end;
+
+  procedure run_test_equal_cursor is
+  begin
+    ut3.ut.run('test_cursor_grants.test_equal_cursor');
+  end;
+
+  procedure run_test_not_empty_cursor is
+  begin
+    ut3.ut.run('test_cursor_grants.test_not_empty_cursor');
+  end;
+
+  procedure run_test_have_count_cursor is
+  begin
+    ut3.ut.run('test_cursor_grants.test_have_count_cursor');
+  end;
+
+  procedure run_test_empty_cursor is  
+  begin
+    ut3.ut.run('test_cursor_grants.test_empty_cursor');
+  end;
+
+  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('OBJECT_NAME,OBJECT_TYPE');
+
+  end;
+
+  procedure test_equal_cursor is
+    l_actual   SYS_REFCURSOR;
+    l_expected SYS_REFCURSOR;
+    l_list ut3_tester.test_dummy_object_list;
+  begin
+    --Arrange
+    open l_actual for select value(x) as item from table(l_list) x;
+    open l_expected for select value(x) as item from table(l_list) x;
+
+    --Act
+    ut3.ut.expect(l_actual).to_equal(l_expected);
+
+  end;
+
+  procedure test_not_empty_cursor is
+    l_details_cur SYS_REFCURSOR;
+    l_expected         ut3_tester.test_dummy_object_list;
+  begin
+    select ut3_tester.test_dummy_object( rn, 'Something '||rn, rn1)
+    bulk collect into l_expected
+    from (select rownum * case when mod(rownum,2) = 0 then -1 else 1 end rn,
+                rownum * case when mod(rownum,4) = 0 then -1 else 1 end rn1
+         from dual connect by level <=10);
+
+    --Arrange
+    open l_details_cur for
+      select value(x) as item from table(l_expected) x;
+
+    --Act
+    ut3.ut.expect(l_details_cur).not_to_be_empty();
+  end;
+
+  procedure test_have_count_cursor is
+    l_expected SYS_REFCURSOR;
+  begin
+    --Arrange
+    open l_expected for
+      select value(x) as item from table(ut3_tester.test_dummy_object_list()) x;
+
+    --Act
+    ut3.ut.expect(l_expected).to_have_count(0);
+  end;
+
+  procedure test_empty_cursor is
+    l_expected SYS_REFCURSOR;
+  begin
+    open l_expected for
+      select value(x) as item from table(ut3_tester.test_dummy_object_list()) x;             
+    --Act
+    ut3.ut.expect(l_expected).to_be_empty();
+  end;
+
+end;
+/
diff --git a/test/helpers/ut3user#.test_cursor_grants.pks b/test/helpers/ut3user#.test_cursor_grants.pks
@@ -1,9 +1,26 @@
-create or replace package ut3$user#.test_cursor_grants is
-  --%suite()
-
-  procedure run;
-
-  --%test(execute join by test)
-  procedure test_join_by_cursor;
-end;
-/
+create or replace package ut3$user#.test_cursor_grants is
+  --%suite()
+
+  procedure run_test_join_by_cursor; 
+  procedure run_test_equal_cursor;
+  procedure run_test_not_empty_cursor;
+  procedure run_test_have_count_cursor;
+  procedure run_test_empty_cursor;  
+
+  --%test(execute join by test)
+  procedure test_join_by_cursor;
+
+  --%test(execute equal test)
+  procedure test_equal_cursor;
+
+  --%test(execute not empty test)
+  procedure test_not_empty_cursor;
+
+  --%test(execute have_count test)
+  procedure test_have_count_cursor;
+
+  --%test(execute empty test)
+  procedure test_empty_cursor;  
+
+end;
+/