September 2022 - Oracle Findings

Important!
I have been made aware by a user that Oracle is not supporting this framework anymore and recommends using utPLSQL.
Anyway, I will leave this article as someone might find it useful.

[This setup has been tested on SQL Developer 18.3 and Oracle RDBMS 11G. I strongly suggest you not to use any version of SQL Developer older than 18.3 because there are bugs that prevent testing exceptions in functions]

Introduction
It happened a few days ago. I woke up and decided “Enough! Now I really want to set up a proper database unit testing environment for SQL Developer, fixing all the obstacles by myself!”. The point is that by following Oracle instructions you can quickly get your unit test repository up and running for your small local project but for sure not in a corporate environment where more requirements and restrictions apply.
What I mean is: how likely is to have SYS database access, which is required (and lightly overlooked by Oracle as a requirement) in order to create the UT repository with SQL Developer? Even if you do have SYS access, I strongly encourage you to go through the article because there are some tweaks that might be useful to make your environment work better.
This post wants to be a full set up guide to help who wants to get serious about unit testing with SQL Developer and set up a proper repository to use in a corporate production environment. I’ve spent quite some time to solve different issues with SQL Developer, find a way to make it work in a real multi-project environment, understand what’s under the hood and overcome some product limitations. This post is the result of all my findings in this matter. Obviously, I’ve also taken some decisions you can agree with or not, feel free to change anything according to your needs.
Obviously, if you have SYS access and/or want to set up the unit testing framework for just one project, you can skip the initial steps and fly directly to the Unix configuration section.
I really hope this can help you save time and be up and running quickly with a big smile on your face.
Go grab a coffee and let’s start.

Note: this code has been tested on version 19c (19.3.0.0.0)

In this post, I’ll show how incredibly easy is to read data from a table by having only the INDEX privilege granted on it. It’s something I found out when preparing a demo about Oracle Virtual Private Database, you’ll see why later on in this post.
Let’s imagine the following scenario: user SCOTT owns a table CUSTOMERS_CC holding sensitive information about customers

SCOTT> select * from CUSTOMER_CC;
   CUST_ID CUST_NAME            CC_NUMBER
---------- -------------------- --------------------
         1 John Smith           1234-5899-7458-1111
         2 Brenda Lipson        5093-1214-0875-1496
         3 Mark Walfish         9905-8465-3208-5478
         4 Artur Tork           5521-4785-0033-7408
         5 Robert Koczinski     1457-9650-0897-0000

SCOTT> select * from CUSTOMER_CC;

CUST_ID CUST_NAME CC_NUMBER

---------- -------------------- --------------------

1 John Smith 1234-5899-7458-1111

2 Brenda Lipson 5093-1214-0875-1496

3 Mark Walfish 9905-8465-3208-5478

4 Artur Tork 5521-4785-0033-7408

5 Robert Koczinski 1457-9650-0897-0000

Oracle Findings

DB Unit Testing with Oracle SQL Developer: a serious and complete set up guide

Granting INDEX privilege gives access to your data