utPLSQL · lwasylow · Jan 28, 2022 · Jan 27, 2022
diff --git a/source/core/annotations/ut_annotation_cache_manager.pkb b/source/core/annotations/ut_annotation_cache_manager.pkb
@@ -32,7 +32,7 @@ create or replace package body ut_annotation_cache_manager as
     end if;
 
     -- if not in trigger, or object has annotations
-    if ora_sysevent is null or a_object.annotations is not null and a_object.annotations.count > 0 then
+    if a_object.annotations is not null and a_object.annotations.count > 0 then
 
       update /*+ no_parallel */ ut_annotation_cache_info i
          set i.parse_time = l_timestamp
@@ -47,16 +47,19 @@ create or replace package body ut_annotation_cache_manager as
         values (ut_annotation_cache_seq.nextval, a_object.object_owner, a_object.object_name, a_object.object_type, l_timestamp)
           returning cache_id into l_cache_id;
       end if;
+
+      delete /*+ no_parallel */ from ut_annotation_cache c where cache_id = l_cache_id;
 
-    end if;
-
-    delete /*+ no_parallel */ from ut_annotation_cache c where cache_id = l_cache_id;
-
-    if a_object.annotations is not null and a_object.annotations.count > 0 then
       insert /*+ no_parallel */ into ut_annotation_cache
              (cache_id, annotation_position, annotation_name, annotation_text, subobject_name)
       select /*+ no_parallel */ l_cache_id, a.position, a.name, a.text, a.subobject_name
         from table(a_object.annotations) a;
+    elsif  a_object.annotations is null or a_object.annotations.count = 0 then
+      ut_annotation_cache_manager.remove_from_cache(
+          ut_annotation_objs_cache_info(
+            ut_annotation_obj_cache_info(a_object.object_owner, a_object.object_name, a_object.object_type, 'Y', null)
+          )
+        );
     end if;
     commit;
   end;

diff --git a/test/ut3_tester/core/test_suite_manager.pkb b/test/ut3_tester/core/test_suite_manager.pkb
@@ -1466,5 +1466,99 @@ end;]';
       ut.expect(SQLCODE).to_equal(ut3_develop.ut_utils.gc_value_too_large);
   end;
 
+  procedure setup_remove_annot_test is
+    pragma autonomous_transaction;
+  begin
+    execute immediate q'[create or replace package test_removing_annotation as
+  --%suite
+
+  --%test
+  procedure test1;
+
+  --%test
+  procedure test2;
+
+end;]';
+  end;
+
+  procedure remove_annot_from_test is
+    pragma autonomous_transaction;
+  begin
+    execute immediate q'[create or replace package test_removing_annotation as
+
+  procedure test1;
+
+  procedure test2;
+
+end;]';
+  end;
+
+  procedure rem_one_annot_test is
+    pragma autonomous_transaction;
+  begin
+    execute immediate q'[create or replace package test_removing_annotation as
+  --%suite
+
+  procedure test1;
+
+  --%test
+  procedure test2;
+
+end;]';
+    execute immediate q'[create or replace package body test_removing_annotation as
+
+	 procedure test1 is
+	  begin
+		ut.expect(1).to_equal(1);
+	  end;
+
+	 procedure test2 is
+	  begin
+		ut.expect(1).to_equal(1);
+	  end;
+
+end;]';
+  end;
+
+  procedure clean_remove_annot_test is
+    pragma autonomous_transaction;
+  begin
+    execute immediate 'drop package test_removing_annotation';
+  end;
+
+  procedure test_rem_cache_on_create is
+    l_test_report ut3_develop.ut_varchar2_list;
+  begin
+
+    select * bulk collect into l_test_report from table(ut3_develop.ut.run(sys_context('USERENV', 'CURRENT_USER')||'.test_removing_annotation'));
+
+    -- drop all tests
+    remove_annot_from_test;
+
+    begin
+      select * bulk collect into l_test_report from table(ut3_develop.ut.run(sys_context('USERENV', 'CURRENT_USER') || '.test_removing_annotation'));
+    exception
+      when others then
+        ut.expect(sqlerrm).to_be_like('%ORA-20204: Suite package ut3_tester.test_removing_annotation does not exist%');
+    end;
+
+  end;
+
+  procedure test_rem_cache_on_crt_anno is
+    l_test_report ut3_develop.ut_varchar2_list;
+	l_results clob;
+  begin
+
+    select * bulk collect into l_test_report from table(ut3_develop.ut.run(sys_context('USERENV', 'CURRENT_USER')||'.test_removing_annotation'));
+
+    -- drop single test
+    rem_one_annot_test;
+    ut3_develop.ut.run(sys_context('USERENV', 'CURRENT_USER')|| '.test_removing_annotation',a_reporter => ut3_develop.ut_documentation_reporter() );
+    l_results :=  ut3_tester_helper.main_helper.get_dbms_output_as_clob();
+    --Assert
+    ut.expect( l_results ).to_be_like( '%1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)%' );
+
+  end;
+
 end test_suite_manager;
 /
diff --git a/test/ut3_tester/core/test_suite_manager.pks b/test/ut3_tester/core/test_suite_manager.pks
@@ -170,5 +170,21 @@ create or replace package test_suite_manager is
   procedure create_dummy_long_test_package;
   procedure drop_dummy_long_test_package;
 
+
+  procedure setup_remove_annot_test;
+  procedure clean_remove_annot_test;
+
+  --%test(Remove cache on package create or replace when all annotations removed)
+  --%beforetest(setup_remove_annot_test)
+  --%aftertest(clean_remove_annot_test)
+  procedure test_rem_cache_on_create;
+
+
+  --%test(Remove cache on package create or replace when single annotation removed)
+  --%beforetest(setup_remove_annot_test)
+  --%aftertest(clean_remove_annot_test)
+  procedure test_rem_cache_on_crt_anno;
+
+
 end test_suite_manager;
 /