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

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

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)

– 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

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

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

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

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:

  1. copy the SQL Developer installation zip archive to the Linux machine, let’s say into /home/rob
  2. connect to the machine through telnet/ssh and unzip the archive. Once unzipped, we have the new folder /home/rob/sqldeveloper
  3. add the sdcli executable path in the system (/home/rob/sqldeveloper/sqldeveloper/bin/sdcli)
  4. 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
  5. go back to Windows and let’s make sure our connections are saved including the passwords
  6. 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
  7. 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? 🙂

Share this post on Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin
Email this to someone
email

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

  1. Avatar
    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.

Leave a Reply

Your email address will not be published. Required fields are marked *