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!

Creating a Lubuntu Build Server on Amazon

In previous posts, I showed how to use Oracle's SQL*Developer as part of a CI (Continuous Integration) server. Since I use AWS (Amazon Web Services) to host my development servers, I had to find a way to run SQL*Developer on AWS.  One conflict I discovered was that SQL*Developer requires a GUI (Graphical User Interface) and I couldn't find an AWS server that had a GUI.

After reviewing and testing many alternatives, I settled on Real VNC running on Ubuntu with LXDE.  Real VNC has a free Windows viewer and a free Android viewer.  It worked the first time I installed it and has worked flawlessly since. Because I am using an Amazon VPC with a VPN sever, I did not use the encryption capability of the VNC server.

LXDE is a lightweight X-Windows desktop environment.  It has a small foot print and doesn't spawn many processes.  This was important for ease of installation and efficient execution on the Ubuntu server.  LXDE is also conveniently bundled into an Ubuntu version called Lubuntu.


Setup

1) Create an AWS instance using an "Ubuntu Server 14.04" AMI.  The settings for this will vary, depending on your deployment environment in AWS.  I found that a "t2.small" can run my entire build server, including VNC, SQL*Developer, Jenkins, and Oracle database.  After creating it, you should be able to "ssh" to the new instance.

2) Add Ubuntu Java Repository and get the latest updates

sudo add-apt-repository ppa:webupd8team/java

sudo apt-get update && sudo apt-get dist-upgrade
   (Select "install the package maintainer's version")

3) Install packages for LXDE (lubuntu-core), VNC (vnc4server), Firefox (firefox), GUI editor (leafpad), and packages needed to install OracleXE (allien, libaio1, unixodbc)

sudo apt-get install lubuntu-core vnc4server firefox leafpad alien libaio1 unixodbc

4) To setup VNC, I used this tutorial from "HowtoForge"

https://www.howtoforge.com/how-to-install-vnc-server-on-ubuntu-14.04

5) I was able to setup an Oracle XE database using this blog from Mike Heeren

http://blog.whitehorses.nl/2014/03/18/installing-java-oracle-11g-r2-express-edition-and-sql-developer-on-ubuntu-64-bit/

6) Using a similar approach as Mike Heeren, I setup SQL*Developer.  I started by using Firefox to download SQL*Developer into the "Downloads" directory.  This implies you are successfully running a VNC client connection to the server on Amazon.

7) Convert and install SQL*Developer

sudo alien --scripts -d sqldeveloper-4.0.3.16.84-1.noarch.rpm

sudo dpkg --install sqldeveloper_4.0.3.16.84-2_all.deb

8) Run SQL*Developer for the first time

sudo mkdir /home/.sqldeveloper/

xhost +

sudo /opt/sqldeveloper/sqldeveloper.sh
   (Java Path is /usr/lib/jvm/java-7-oracle)

9) Then, to run SQL*Developer without "sudo":

sudo chown ubuntu .sqldeveloper

xhost -

sqldeveloper

10) At this point, SQL*Developer should run from the "Programming" menu in LXDE.  (Click the CRT icon in the lower left to activate the menu.)

11) Because of a bug in the distribution, the "sdcli" script needs to be updated before SQL*Developer will run in command line mode:

vi /opt/sqldeveloper/sqldeveloper/bin/sdcli
  (Change . "sqldeveloper" to . "/opt/sqldeveloper/sqldeveloper/bin/sqldeveloper")

12) To setup Jenkins, I used this guide from Kohsuke Kawaquchi himself:

https://wiki.jenkins-ci.org/display/JENKINS/Installing+Jenkins+on+Ubuntu


Congratulations.  You should have a complete Lubuntu build server running on AWS.


Resources:

An example Ubuntu AMI:
https://aws.amazon.com/marketplace/pp/B00JV9TBA6

More information on LXDE:
http://lxde.org/

An example Amazon VPC scenario:
http://docs.aws.amazon.com/AmazonVPC/latest/UserGuide/VPC_Scenario2.html

An example VPN AMI:
https://aws.amazon.com/marketplace/pp/B00MI40CAE/ref=mkt_wir_openvpn_byol