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

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.

Granting INDEX privilege gives access to your data

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

Switch Pl/Sql logging on/off for specific sessions

As we know Oracle provides several nice tools to debug our PlSql code and even a profiler to get all the details about code execution. But what if you want to use your own debug code and be able to switch it on for specific sessions whenever you want? And I mean, obviously, without changing the code! I’ve seen around some examples in which people use a configuration table to do that: the debug code keeps querying the table and logs the output only if the flag is Y. This way of doing it is absolutely fine and works well but I was looking for something more elegant that does not involve a table. My idea takes advantage of the Oracle application contexts and it’s really simple.

DB Unit Testing with SQL Developer: REF CURSORs still having problems [UPDATE!]

Database Unit Testing is underestimated by many (too many!) IT companies. Personally, in my long career, I’ve never seen it properly implemented. In the past there might have been lack of right tools for testing database objects but nowadays the market offers a good choice of software able to fill this gap. Because of my deep love for Oracle products (and I’ve never kept it secret) I’ve recently decided to spend some time analyzing Oracle SQL Developer and give it a shot to see how good is the unit testing support. This post refers to SQL Developer version 18.1 connected to an Oracle database release 12.1.0.2.

The version 18.1, latest release at the moment, has a ton of interesting features but the purpose of this post is to just focus on the db unit testing part, specifically on REF CURSORS. I really like how Oracle implemented this testing module in SQL Developer: it’s clear, it has a guided wizard which makes it easy to create tests, it supports shared repositories and allows to save libraries for reusing code. The only problem I had in the past releases is that REF CURSORS were not properly supported. I was hoping to see an improvement in this latest update but unfortunately it’s not the case and I will show in this post what I mean.

How to easily test a complex REF CURSOR

In my personal experience, quite often I came across the need to test and optimize queries used by REF CURSORS. Sometimes it’s a really painless operation, other times it can really be a very long and complex operation that can make you waste a lot of time. Let’s see what we would normally do in a simple case: consider a procedure GET_DATA which returns a SYS_REFCURSOR

create or replace procedure get_data(pi_table_name in VARCHAR2,
                                     po_resultset out SYS_REFCURSOR) is
begin
  open po_resultset for
	   select atc.OWNER, atc.COLUMN_NAME, atc.DATA_TYPE
             from all_tab_columns atc
            where table_name = pi_table_name
            order by column_id;
end get_data;
/

create or replace procedure get_data(pi_table_name in VARCHAR2,

po_resultset out SYS_REFCURSOR) is

begin

open po_resultset for

select atc.OWNER, atc.COLUMN_NAME, atc.DATA_TYPE

from all_tab_columns atc

where table_name = pi_table_name

order by column_id;

end get_data;

The query used for the ref cursor is quite simple, it extracts 3 columns and uses just one parameter as a filter. In such case, if I wanted to test the query, I would probably take it outside and run it manually to perform my test.

Switching between range and interval partitioning: automatically create partitions in a range-partitioned table

INTERVAL partitioning has been introduced by Oracle as an extension of RANGE partitioning. There are few limitations like the fact that’s not supported at subpartition level and the partitioning key has to be a DATE or NUMBER but also some interesting advantages. When a table is partitioned by INTERVAL, in fact, partitions are created automatically as data is loaded into the table so we don’t have to bother creating anything in advance. We might, anyway, prefer to maintain our historical data in tables that are partitioned by RANGE. This because it’s common to keep inside the system a “rolling window” (e.g. last 90 days) of historical data meaning oldest partitions are dropped as soon as they become obsolete and with INTERVAL partitioning the last partition in the range section cannot be dropped. Moreover, in many processes it’s preferrable to have a full control of which partitions are created because data loaded into the system is not guaranteed to be “clean” and rows that don’t map to existing partitions have to be rejected.
After all these considerations, let’s have a look at what we can do to make the best out of both partitioning options (tested on 11g).

How to use a custom index on primary key constraints

I admit it, I had a completely wrong belief when thinking about primary key constraints and their related index. When creating a PK on a table, Oracle automatically associates a unique index to it

create table mytab(id         number(10),
                   run_date   date,
                   run_region number(2));

create index mytab_idx on mytab(id, run_date);
alter table mytab add primary key (id);

SQL> select index_name, index_type, table_name, uniqueness
  2  from user_indexes
  3  where table_name = 'MYTAB';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
SYS_C0033765                   NORMAL                      MYTAB                          UNIQUE


SQL> select constraint_name, constraint_type, status, deferrable, index_name
  2  from user_constraints
  3  where table_name = 'MYTAB';

CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS   DEFERRABLE     INDEX_NAME
------------------------------ --------------- -------- -------------- ------------------------------
SYS_C0033765                   P               ENABLED  NOT DEFERRABLE SYS_C0033765

create table mytab(id number(10),

run_date date,

run_region number(2));

create index mytab_idx on mytab(id, run_date);

alter table mytab add primary key (id);

SQL> select index_name, index_type, table_name, uniqueness

2 from user_indexes

3 where table_name = 'MYTAB';

INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS

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

SYS_C0033765 NORMAL MYTAB UNIQUE

SQL> select constraint_name, constraint_type, status, deferrable, index_name

2 from user_constraints

3 where table_name = 'MYTAB';

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS DEFERRABLE INDEX_NAME

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

SYS_C0033765 P ENABLED NOT DEFERRABLE SYS_C0033765

The index name is internally generated and associated to the constraint. Fact is that, as I discovered some time ago, it’s not the index that enforces the uniqueness but the constraint itself. The constraint uses the underlying index to go faster but the index itself does not enforce anything. But you can’t really blame me for thinking differently in the beginning, not after seeing that Oracle creates a unique index for the pk!

How to use RETURNING INTO with a multi-row insert

The RETURNING INTO clause is a very convenient way to retrieve values from rows that are being inserted/deleted/updated. When used with UPDATE and DELETE statements, it’s possible to retrieve values of multiple rows by using the RETURNING BULK COLLECT INTO. This is something that does not work with the INSERT statements: in that case you will be able to only return values from a single row.
Let’s see how this works and create a simple test table that will be the target of our DML operations

create table mytab(id       number(3),
                   run_date date,
                   cust_id  varchar2(30));

insert into mytab
   (select rownum, date '2016-01-01' + round(dbms_random.value(0, 30)), 'CLI'||round(dbms_random.value(100, 999))||upper(dbms_random.string('A', 3))
      from dual
    connect by level <= 100);
commit;

create table mytab(id number(3),

run_date date,

cust_id varchar2(30));

insert into mytab

(select rownum, date '2016-01-01' + round(dbms_random.value(0, 30)), 'CLI'||round(dbms_random.value(100, 999))||upper(dbms_random.string('A', 3))

from dual

connect by level <= 100);

commit;

First of all let’s use a simple case just to have a look at how this works.

Oracle Virtual Private Database is not so private!

Virtual Private Database (Oracle VPD) was introduced by Oracle in release 8i. It’s a security feature that provides access restriction at row/column level for privacy and regulatory compliance. It allows sophisticated logic to be applied through the use of custom functions in which rules can be written for fine-grained data access. If you want to know more about how it works check the official documentation.
The point of this post is that the column-level security of Oracle VPD can be easily tricked! It all started when I decided to check the new Redaction feature released with Oracle 12c. This is somehow similar to VPD but it works at a different level. I came across a very interesting article from David Litchfield that in his article demonstrates how this feature is broken and not safe at all. Well, starting from there I made my own similar investigations on VPD and got some really surprising results.

How locks work with move partition ONLINE on Oracle 12c

With the release 12c Oracle introduced the “online partition movement” making the operation a non-blocking DDL command. This means that it’s possible to run any DML on a partition while the same is being “moved” using the new ONLINE option.
Let’s see how that works on our usual test table populated with 1 million rows in the first partition

-- Create table
drop table mytab;
create table mytab(id       number(10),
                   run_date date,
                   status   varchar2(100))
partition by range(run_date)
(partition P1 values less than (date '2016-01-02'),
 partition P2 values less than (date '2016-01-03')
);

-- Populate data
insert into MYTAB select rownum, date '2016-01-01', 'STATUS'||round(dbms_random.value(1,100)) from dual connect by level <= 1000000;
insert into MYTAB select rownum, date '2016-01-02', 'STATUS'||round(dbms_random.value(1,100)) from dual connect by level <= 100;
commit;

-- Create one local and one global index
create index MYTAB_IDX_L on MYTAB(id) local;
create index MYTAB_IDX_G on MYTAB(status);

-- Create table

drop table mytab;

create table mytab(id number(10),

run_date date,

status varchar2(100))

partition by range(run_date)

(partition P1 values less than (date '2016-01-02'),

partition P2 values less than (date '2016-01-03')

);

-- Populate data

insert into MYTAB select rownum, date '2016-01-01', 'STATUS'||round(dbms_random.value(1,100)) from dual connect by level <= 1000000;

insert into MYTAB select rownum, date '2016-01-02', 'STATUS'||round(dbms_random.value(1,100)) from dual connect by level <= 100;

commit;

-- Create one local and one global index

create index MYTAB_IDX_L on MYTAB(id) local;

create index MYTAB_IDX_G on MYTAB(status);

We can now take a look at how move partition works normally when not running it in ONLINE mode.
Let’s execute

alter table MYTAB move partition P1 compress for OLTP;

1	alter table MYTAB move partition P1 compress for OLTP;