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.

Database set up: roles and users (no, you don’t have SYS access!)
Assuming that you don’t have SYS access, if you want to use the unit testing capabilities of SQL Developer you’ll have to go through some manual steps.

Step 1: create the required roles UT_REPO_ADMINISTRATOR and UT_REPO_USER
This is something that the DBA should be able to do without any problems and after receiving a proper request/ticket

create role UT_REPO_ADMINISTRATOR;
create role UT_REPO_USER;

grant create public synonym, drop public synonym to UT_REPO_ADMINISTRATOR;
grant select on dba_role_privs to UT_REPO_USER;
grant select on dba_role_privs to UT_REPO_ADMINISTRATOR;
grant select on dba_roles to UT_REPO_ADMINISTRATOR;
grant select on dba_roles to UT_REPO_USER;
grant select on dba_tab_privs to UT_REPO_ADMINISTRATOR;
grant select on dba_tab_privs to UT_REPO_USER;
grant execute on dbms_lock to UT_REPO_ADMINISTRATOR;
grant execute on dbms_lock to UT_REPO_USER;
grant create synonym to UT_REPO_USER;
grant UT_REPO_USER to UT_REPO_ADMINISTRATOR with admin option;

create role UT_REPO_ADMINISTRATOR;

create role UT_REPO_USER;

grant create public synonym, drop public synonym to UT_REPO_ADMINISTRATOR;

grant select on dba_role_privs to UT_REPO_USER;

grant select on dba_role_privs to UT_REPO_ADMINISTRATOR;

grant select on dba_roles to UT_REPO_ADMINISTRATOR;

grant select on dba_roles to UT_REPO_USER;

grant select on dba_tab_privs to UT_REPO_ADMINISTRATOR;

grant select on dba_tab_privs to UT_REPO_USER;

grant execute on dbms_lock to UT_REPO_ADMINISTRATOR;

grant execute on dbms_lock to UT_REPO_USER;

grant create synonym to UT_REPO_USER;

grant UT_REPO_USER to UT_REPO_ADMINISTRATOR with admin option;

Step 2: create users
As stated in the Oracle manual, there are 2 different types of UT repository that can be created: unshared and shared. The first one means that you have one Oracle user with its own repository. All developers will have to connect with the same admin user in order to use that unshared repository. Do you want all your developers to be admin of the UT repository? No, you don’t, believe me. My personal suggestion is to use a shared repository and create 2 users: one admin (let’s call it UT_ADMIN) and one normal user that will be shared across all developers (UT_USER). After that, grant the proper privileges and roles as shown below, another task for our DBA

-- Repository admin user
CREATE USER ut_admin IDENTIFIED BY "xxxxxx"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";

grant CONNECT, RESOURCE, CREATE VIEW, UNLIMITED TABLESPACE, UT_REPO_ADMINISTRATOR to "UT_ADMIN";
grant select on dba_roles to "UT_ADMIN";
grant select on dba_role_privs to "UT_ADMIN";

-- Repository user
CREATE USER ut_user IDENTIFIED BY "xxxxxx"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";

grant CONNECT, RESOURCE, UT_REPO_USER to "UT_USER";

-- Repository admin user

CREATE USER ut_admin IDENTIFIED BY "xxxxxx"

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP";

grant CONNECT, RESOURCE, CREATE VIEW, UNLIMITED TABLESPACE, UT_REPO_ADMINISTRATOR to "UT_ADMIN";

grant select on dba_roles to "UT_ADMIN";

grant select on dba_role_privs to "UT_ADMIN";

-- Repository user

CREATE USER ut_user IDENTIFIED BY "xxxxxx"

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP";

grant CONNECT, RESOURCE, UT_REPO_USER to "UT_USER";

Step 3: create the UT repository
Now we are ready to finally set up our repository. In SQL Developer, create 2 new connections for UT_ADMIN and UT_USER then

– go under “View” and click on “Unit Test” to activate the UT panel
– go under “Tools”, “Unit Test” and “Select Current Repository…”. You will be asked to specify the connection: choose UT_ADMIN and press OK. A message will warn you that there is no repository under that schema and will ask if you want to create one. Press “Yes”.

Normally, if we hadn’t done the previous step, we would be asked to provide SYS credentials. Users and privileges are already in place so SQL Developer now only needs to create the repository objects.
At this point, you should be able to see the repository structure on the bottom left with all the folders. At the moment this is an unshared repository, which we already decided to change.

– go to “Tools”, “UnitTest” and click on “Select As Shared Repository”. You’ll get a confirmation message after a few seconds. Done.
Now, very important for understanding what is coming next, let me explain what just happened. The repository objects are all created under the UT_ADMIN schema, no surprise. When you select the shared repository option, the following 2 things happen:
– all tables/views on the UT_ADMIN schema are granted select, update and delete privilege to the role UT_REPO_USER (plus an execute privilege on a package);
– public synonyms pointing to the UT_ADMIN tables/views are created.

This means that starting from now any user with the role UT_REPO_USER can access the repository objects. In fact, now you can access the repository with UT_USER by selecting “Tools”, “Unit Test”, “Deselect Current Repository” then again “Tools”, “Unit Test”, “Select Current” and choosing, this time, the connection UT_USER. This is what all developers will do if you don’t want all of them to be administrators. There are a couple of things to clarify: I don’t really understand and like this choice of Oracle to create public synonyms. The reason is that if you want to create more separate shared repositories for different projects on the same database, these public synonyms become a problem. No panic, there is a workaround for that and I will explain it later. Another point here is that you should create an Oracle user for each person that accesses the repository and assign them the UT_USER role. My personal opinion is that this is not needed but you are free to do differently. I prefer to let all developers use the same UT_USER access and, with a small code change which I will explain later, you can still have the information of who inserted/changed a row by replacing USER with OSUSER in the triggers. So you don’t really need to create a DB user for each repository user but, again, it’s your choice.

Step 4: let’s make some changes to improve our repository
As previously explained, now there are public synonyms pointing to the newly created ut repository objects on our database. We cannot remove them because Oracle will think that the repository does not exist anymore and will prompt us again to create a new one. Also, grants are given to the role UT_REPO_USER so any other database user with that role will have access to the repository objects we have just created. For these reasons let’s take the following actions:
– forget about the public synonyms, they cannot be touched, accept it!
– create synonyms local to the UT_USER schema and make them point to the UT_ADMIN objects. Use the following select to extracts all commands (in case you used a username different from UT_ADMIN make the proper replacements)

-- Execute on the UT_ADMIN schema then copy the result and execute it with UT_USER. Change UT_ADMIN accordng to the username you are uasing as the repository administrator
select 'create synonym '||object_name||' for UT_ADMIN.'||object_name||';' as create_synonym_cmd
  from user_objects
 where object_type in ('TABLE','VIEW','PACKAGE')
   and object_name not in ('ALL_UT_STARTUPS', 'ALL_UT_TEARDOWNS');

-- Execute on the UT_ADMIN schema then copy the result and execute it with UT_USER. Change UT_ADMIN accordng to the username you are uasing as the repository administrator

select 'create synonym '||object_name||' for UT_ADMIN.'||object_name||';' as create_synonym_cmd

from user_objects

where object_type in ('TABLE','VIEW','PACKAGE')

and object_name not in ('ALL_UT_STARTUPS', 'ALL_UT_TEARDOWNS');

– remove all privileges on the UT_ADMIN objects granted to the role UT_REPO_USER and grant them directly to the user UT_USER. Run the following script from the UT_ADMIN schema

-- Execute from the UT_ADMIN user (or the administrator user you defined). Replace username UT_USER if needed
declare
l_ut_username varchar2(30) := 'UT_USER';
begin
  for cur_priv in (
                   select 'grant '||decode(object_type, 'TABLE', 'select, insert, update, delete', 'VIEW', 'select', 'execute')||' on '||object_name||' to '||l_ut_username as grant_cmd,
                          'revoke '||decode(object_type, 'TABLE', 'select, insert, update, delete', 'VIEW', 'select', 'execute')||' on '||object_name||' from UT_REPO_USER' as revoke_cmd
                     from user_objects
                    where object_type in ('TABLE','VIEW','PACKAGE')
                      and object_name not in ('UT_METADATA', 'ALL_UT_STARTUPS', 'ALL_UT_TEARDOWNS')
                    union all
                   select 'grant select on '||object_name||' to '||l_ut_username as grant_cmd,
                          'revoke select on '||object_name||' from UT_REPO_USER' as revoke_cmd
                     from user_objects
                    where object_name = 'UT_METADATA'
                  )
  loop
    execute immediate cur_priv.grant_cmd;
    execute immediate cur_priv.revoke_cmd;
  end loop;
end;
/

-- Execute from the UT_ADMIN user (or the administrator user you defined). Replace username UT_USER if needed

declare

l_ut_username varchar2(30) := 'UT_USER';

begin

for cur_priv in (

select 'grant '||decode(object_type, 'TABLE', 'select, insert, update, delete', 'VIEW', 'select', 'execute')||' on '||object_name||' to '||l_ut_username as grant_cmd,

'revoke '||decode(object_type, 'TABLE', 'select, insert, update, delete', 'VIEW', 'select', 'execute')||' on '||object_name||' from UT_REPO_USER' as revoke_cmd

from user_objects

where object_type in ('TABLE','VIEW','PACKAGE')

and object_name not in ('UT_METADATA', 'ALL_UT_STARTUPS', 'ALL_UT_TEARDOWNS')

union all

select 'grant select on '||object_name||' to '||l_ut_username as grant_cmd,

'revoke select on '||object_name||' from UT_REPO_USER' as revoke_cmd

from user_objects

where object_name = 'UT_METADATA'

)

loop

execute immediate cur_priv.grant_cmd;

execute immediate cur_priv.revoke_cmd;

end loop;

end;

If everything was done correctly, the UT_REPO_USER role should now have privileges only on 4 SYS objects and the UT_USER user has privileges granted on the UT_ADMIN schema.
We are almost done here but I suggest to do this last change. All the triggers compiled on the UT_ADMIN schema log the user information by using “user” which in our case is always UT_USER, right? I would recommend to replace it with something else like the operating system user aka UPPER(SYS_CONTEXT(‘userenv’, ‘os_user’)) like in the following example

-- Original trigger from Oracle
CREATE OR REPLACE TRIGGER "UT_SUITE_TRG"
BEFORE INSERT ON UT_SUITE
FOR EACH ROW 
BEGIN
  if (:NEW.UT_SID is null or :NEW.UT_SID = '') then 
      SELECT  SYS_GUID() INTO :NEW.UT_SID FROM DUAL;
  end if;
  if (:NEW.CREATED_ON is null or :NEW.CREATED_ON = '') then
      SELECT sysdate INTO :NEW.CREATED_ON FROM DUAL;
  end if;
  if (:NEW.CREATED_BY is null or :NEW.CREATED_BY = '') then
      SELECT user INTO :NEW.CREATED_BY FROM DUAL;
  end if;
  if (:NEW.UPDATED_ON is null or :NEW.UPDATED_ON = '') then
      SELECT sysdate INTO :NEW.UPDATED_ON FROM DUAL;
  end if;
  if (:NEW.UPDATED_BY is null or :NEW.UPDATED_BY = '') then
      SELECT user INTO :NEW.UPDATED_BY FROM DUAL;
  end if;
END;

-- Our updated trigger
CREATE OR REPLACE TRIGGER "UT_SUITE_TRG"
BEFORE INSERT ON UT_SUITE
FOR EACH ROW 
BEGIN
  if (:NEW.UT_SID is null or :NEW.UT_SID = '') then 
      SELECT  SYS_GUID() INTO :NEW.UT_SID FROM DUAL;
  end if;
  if (:NEW.CREATED_ON is null or :NEW.CREATED_ON = '') then
      SELECT sysdate INTO :NEW.CREATED_ON FROM DUAL;
  end if;
  if (:NEW.CREATED_BY is null or :NEW.CREATED_BY = '') then
      SELECT UPPER(SYS_CONTEXT('userenv', 'os_user')) INTO :NEW.CREATED_BY FROM DUAL;
  end if;
  if (:NEW.UPDATED_ON is null or :NEW.UPDATED_ON = '') then
      SELECT sysdate INTO :NEW.UPDATED_ON FROM DUAL;
  end if;
  if (:NEW.UPDATED_BY is null or :NEW.UPDATED_BY = '') then
      SELECT UPPER(SYS_CONTEXT('userenv', 'os_user')) INTO :NEW.UPDATED_BY FROM DUAL;
  end if;
END;

-- Original trigger from Oracle

CREATE OR REPLACE TRIGGER "UT_SUITE_TRG"

BEFORE INSERT ON UT_SUITE

FOR EACH ROW

BEGIN

if (:NEW.UT_SID is null or :NEW.UT_SID = '') then

SELECT SYS_GUID() INTO :NEW.UT_SID FROM DUAL;

end if;

if (:NEW.CREATED_ON is null or :NEW.CREATED_ON = '') then

SELECT sysdate INTO :NEW.CREATED_ON FROM DUAL;

end if;

if (:NEW.CREATED_BY is null or :NEW.CREATED_BY = '') then

SELECT user INTO :NEW.CREATED_BY FROM DUAL;

end if;

if (:NEW.UPDATED_ON is null or :NEW.UPDATED_ON = '') then

SELECT sysdate INTO :NEW.UPDATED_ON FROM DUAL;

end if;

if (:NEW.UPDATED_BY is null or :NEW.UPDATED_BY = '') then

SELECT user INTO :NEW.UPDATED_BY FROM DUAL;

end if;

END;

-- Our updated trigger

CREATE OR REPLACE TRIGGER "UT_SUITE_TRG"

BEFORE INSERT ON UT_SUITE

FOR EACH ROW

BEGIN

if (:NEW.UT_SID is null or :NEW.UT_SID = '') then

SELECT SYS_GUID() INTO :NEW.UT_SID FROM DUAL;

end if;

if (:NEW.CREATED_ON is null or :NEW.CREATED_ON = '') then

SELECT sysdate INTO :NEW.CREATED_ON FROM DUAL;

end if;

if (:NEW.CREATED_BY is null or :NEW.CREATED_BY = '') then

SELECT UPPER(SYS_CONTEXT('userenv', 'os_user')) INTO :NEW.CREATED_BY FROM DUAL;

end if;

if (:NEW.UPDATED_ON is null or :NEW.UPDATED_ON = '') then

SELECT sysdate INTO :NEW.UPDATED_ON FROM DUAL;

end if;

if (:NEW.UPDATED_BY is null or :NEW.UPDATED_BY = '') then

SELECT UPPER(SYS_CONTEXT('userenv', 'os_user')) INTO :NEW.UPDATED_BY FROM DUAL;

end if;

END;

Obviously, if you want to take the user information from somewhere else feel free to change this code according to your needs.
The repository set up is now completed and we can open it with UT_USER to start creating test units.
If we want to create a new repository on the same database for a different project we have to

– drop the public synonyms pointing to the last created repository (in this example the ones pointing to UT_ADMIN)
– follow the steps described before.

Remember that the public synonyms must exist but before creating a new repository they have to be dropped because the process creates new ones (otherwise SQL Developer raises an exception).

How do we run our test units from a Unix/Linux machine?

Now that we have our nice set of test units, most likely we want to run them periodically to make sure our code is not broken. Obviously, we don’t want to do it manually from SQL Developer but take advantage of the command line interface: sdcli.
The Sql Developer Command Line Interface is a simple tool that can be used to do different things but we will focus on the execution of unit tests. It is possible to run single test units or entire suites containing several tests. By checking the documentation, in case we want to run all tests from a suite, the usage is

sdcli unittest -run -suite -name  -repo  -db  -log

1	sdcli unittest -run -suite -name -repo -db -log

The first thing I had noticed when I started using this tool some time ago is that there are no connection details, just the “connection name” that we have inside SQL Developer. This means that if we want it to work we must save our connections with the passwords. I will go back to this extremely important point a bit later.
So, let’s say that we have a db connection pointing to our UT repository called “UT DB” and a suite called MYTESTS containing a few test units and I want to run them against a database whose connection is saved with the name “MY DATABASE”, I will have to run

sdcli unittest -run -suite -name MYTESTS -repo "UT DB" -db "MY DATABASE" -log 1

1	sdcli unittest -run -suite -name MYTESTS -repo "UT DB" -db "MY DATABASE" -log 1

Log level 1 is fine for our needs. Just remember to enclose the connection names between ” in case they contain spaces. All works fine, but…yes there is a but, why would you read this post otherwise? This works perfectly on our client, where we have SQL Developer installed, where we can run it and save our connection details. But what if we want to run it on a Unix/Linux server, maybe schedule a job that runs the tests periodically? What if I cannot run the SQL Developer graphic interface on that machine? I asked directly Oracle and the answer was short and clear: “if you cannot run SQL Developer then sdcli cannot work”.
False. It works, but we have to do a couple of things first!
The main problem is that sdcli does not accept any connection string in any format and only accepts “connection names” taken from the SQL Developer connection file. This is the file where all connections are saved. So how do we proceed? I’ll get straight to the point, here are the steps:

copy the SQL Developer installation zip archive to the Linux machine, let’s say into /home/rob
connect to the machine through telnet/ssh and unzip the archive. Once unzipped, we have the new folder /home/rob/sqldeveloper
add the sdcli executable path in the system (/home/rob/sqldeveloper/sqldeveloper/bin/sdcli)
navigate back to the home dir /home/rob/ and execute sdcli without any parameters. This will create the folder /home/rob/.sqldeveloper which contains the configuration files that we need to change to make sdcli work on Linux
go back to Windows and let’s make sure our connections are saved including the passwords
navigate to the folder %APPDATA%\SQL Developer\system<version>\o.jdeveloper.db.connection (where <version> is the software version) and copy the file connections.json (this is the one containing our connection details, it might be connections.xml in case you own an older version) to the Linux machine in the location /home/rob/.sqldeveloper/system/o.jdeveloper.db.connection. Now we have the connection details but if you look inside the JSON file you’ll see that all passwords are encrypted. No worries, Oracle saved the encryption key in another file that we will take care of copying as well
navigate to the Windows folder %APPDATA%\SQL Developer\system\o.sqldeveloper and copy the file product-preferences.xml to the Linux location /home/rob/.sqldeveloper/system<version>/o.sqldeveloper. Actually we don’t need to copy all the content of this file but only the key db.system.id. After doing this, sdcli is able to decrypt the passwords and use our saved connection details.

Now we can run sdcli from Linux every time we want without the need to run the SQL Developer GUI on the server. Happy? 🙂

Facebook

Twitter

One thought on “DB Unit Testing with Oracle SQL Developer: a serious and complete set up guide”

- March 4, 2021 at 05:32 M

Oracle has stopped supporting this framework and recommends using utPLSQL. That’s pretty official, we raised an SR with them to get clarification and found it’s indeed the case.

Also, there has been a long-time open code coverage issue relating to incorrect coverage being thrown up, which they never fixed. So IMO, stay away from it and think about other frameworks.

Oracle Findings

One thought on “DB Unit Testing with Oracle SQL Developer: a serious and complete set up guide”

Leave a Reply Cancel reply