How to Delete a SQL*Developer Unit Test Suite Using PL/SQL

In my previous post "Integrating Jenkins and SQL*Developer's Unit Test", I included a PL/SQL script in the "GNU_make_example.zip" file that will delete an SQL*Developer Unit Test Suite. The script file is called "delete_utr.sql".  The Makefile in that example uses this PL/SQL script to remove the Unit Test Suite "FIT_blog1" from the Unit Test Repository in the build database.  That delete precedes the loading of an updated "FIT_blog1.xml" Unit Test Suite export file into the build database.

Following is the full text from the "delete_utr.sql" script file developed for SQL*Developer 4.0.3:


-- GNU_make_example delete_utr.sql
--   http://reldesgen.com  2015

set serveroutput on size unlimited

DECLARE
   sname    varchar2(100) := '&1.';
   utsid    ut_suite.ut_sid%TYPE;
   TYPE utids_type is table of ut_test.ut_id%TYPE;
   utids    utids_type;
   num      number;
   -- Not deleting from ut_metadata
   -- Not deleting from ut_lookup_categories
   -- Not deleting from ut_lookup_datatypes
   -- Not deleting from ut_lookup_values
   -- Not deleting from ut_lib_startups
   -- Not deleting from ut_lib_dyn_queries
   -- Not deleting from ut_lib_validations
   -- Not deleting from ut_lib_teardowns
BEGIN
   DBMS_OUTPUT.PUT_LINE('Deleting Suite ' || sname);
   select ut_sid into utsid
    from  ut_suite where name = sname;
   DBMS_OUTPUT.PUT_LINE('Found UT_SID ' || utsid);
   select ut_id bulk collect into utids
    from  ut_suite_items
    where ut_sid = utsid
     and  ut_id is not null
    group by ut_id;
   num := SQL%ROWCOUNT;
   DBMS_OUTPUT.PUT_LINE('Found ' || num || ' Test UT_IDs');
   FOR i in 1 .. utids.count LOOP
      delete from ut_test_coverage_stats
       where uti_id in (select uti_id from ut_test_impl
                         where ut_id = utids(i));
      num := SQL%ROWCOUNT;
      delete from ut_test_arguments where ut_id = utids(i);
         -- delete cascade on ut_test_impl_arguments
         -- delete cascade on ut_test_impl_arg_results
      num := num + SQL%ROWCOUNT;
      delete from ut_test where ut_id = utids(i);
         -- delete cascade on ut_test_impl
         -- delete cascade on ut_test_results
         -- delete cascade on ut_test_impl_results
         -- delete cascade on ut_validations
         -- delete cascade on ut_test_impl_val_results
      num := num + SQL%ROWCOUNT;
      DBMS_OUTPUT.PUT_LINE('Deleted ' || num ||
              ' rows for Test UT_ID ' || utids(i));
   END LOOP;
   delete from ut_teardowns where ut_sid = utsid;
   num := SQL%ROWCOUNT;
   delete from ut_startups  where ut_sid = utsid;
   num := num + SQL%ROWCOUNT;
   delete from ut_suite     where ut_sid = utsid;
      -- delete cascade on ut_suite_items
      -- delete cascage on ut_suite_results
      -- delete cascage on ut_suite_item_results
   num := num + SQL%ROWCOUNT;
   DBMS_OUTPUT.PUT_LINE('Deleted ' || num ||
         ' rows for Suite UT_SID ' || utsid);
END;
/


Cheers!