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.

Let’s start creating a simple table with 3 rows

Now let’s compile a package containing a procedure to retrieve rows from the CONTACTS table

As you can see the procedure GET_CONTACTS returns rows through a ref cursor filtered by ACTIVE.
Now we are ready to create a unit test for this procedure. When I start the creation process, SQL Developer shows a warning telling me that in this case I should use a dynamic value query because it contains a REF CURSOR. Fine, but I’m already not very happy about it because this limits my possibilities and doesn’t allow me to create a proper test covering all cases. Anyway, let’s keep going. Once the test is ready, let’s check the “Gather Code Coverage Statistics” option and have a look

The check on “Test Result” means we want SQL Developer to test the recordset returned by the ref cursor. Now we have to specify the dynamic value query for our test, the default looks like

where the first ? is the input parameter PI_ACTIVE while the second one refers to PO_CONTACTS$ which is the ref cursor returned by our procedure. So, how do we test this? I haven’t found any documentation explaining this point but it comes quite natural to think that we need to compare PO_CONTACTS$ to another ref cursor containing the rows we expect. It would be nice if Oracle allowed us to do it inside this wizard but we need to leverage an external function. For this reason, I have created a simple function GET_REFCURSOR which gets as input a query text and returns the recordset as a SYS_REFCURSOR

So now we can proceed and write the dynamic value query. Let’s say we want to test that when we call the GET_CONTACTS procedure passing ACTIVE=’Y’ we should get the 2 rows that are active (see the data inserted at the beginning of this post). To do so I can write the following dynamic value query

A couple of words about what I’m doing here. I have created a query which returns the values I expect.  Beware that not only the values but also column names must match. This query is passed to my GET_REFCUR function which transforms it into a ref cursor. This one is then compared to the one we are testing. All clear, right? For how I have designed my table and data, I’d expect this test to be successful. Wrong, it fails! Look at the message explaining what went wrong

Do you notice anything? Yes, the refcursor that Oracle returns from our procedure has all VARCHAR2 values with trailing spaces up to the maximum length. If I want this test to succeed I have to modify my dynamic value query accordingly by applying RPAD to all text columns

In this case the test is successful with the message

Seriously? I’m sorry Oracle but this is AWFUL! Can you imagine comparing cursors having a higher number of columns? There is another problem with this way of testing ref cursor. Let’s be honest, the test we just created makes not much sense. A real test for this kind of procedure should be “If I request all active contacts I want to verify that the procedure returns all of them”. This would be a more proper test. So, this is actually about counting the active rows on the table and compare the number to what the procedure. returns This is not possible with the current implementation that has to go through the dynamic value query.
Ok, no panic, you know what? SQL Developer gives us the option to write our own Pl/Sql code in the validation section. We uncheck the “Test Result” flag for the test implementation, so that Oracle won’t use the dynamic value query to test our ref cursor, and create a “User Pl/Sql  Code” for the validation. Here I would like to define my own ref cursors and make the comparison by counting the returned rows. The problem is that whatever ref cursor you define in the Pl/Sql code, even an empty one like

the test fails with the exception

so forget to use your own code in this case. There is a workaround for this issue but I don’t really love it. The Pl/Sql code works if there are no ref cursor parameters in the procedure you want to test so we can simply create a “dummy” procedure (called EMPTY in this case)

and create a test unit on it. It won’t have a test implementation because there are no parameters at all. In this case, we can create our custom Pl/Sql validation code like

in which we properly compare the number of rows returned by my procedure with the rows on the table. This works fine so why I don’t like it? Do you remember when we’ve put the check on “Gather Code Coverage Statistics”? Having this test created on a dummy procedure, will not allow us to see the real statistics of all procedure/functions with ref cursors. You will see something like

ut2

which is totally useless because it tells us nothing about the code coverage of PKG_CONTACTS.GET_CONTACTS.

So as a conclusion I’d kindly ask Oracle the following things:

  • fix the returned values of a ref cursor in the dynamic value query so that there are no trailing spaces;
  • allow users to create “User Pl/Sql  Code” with ref cursors in case of tests on procedures/functions returning ref cursors;
  • nice to have, in the “User Pl/Sql  Code” make it possible to reference the returned ref cursor with {NAMEOFREFCUR$}. That would be awesome!

UPDATE!

I’ve made additional tests and realized something very important that I completely ignored before. When querying values from DUAL, we are not specifying any datatype. This means that if we compare a ref cursor to a set of values we generate from dual, Oracle doesn’t really know how to deal with it. The proper way to do such comparison in a dynamic value query based on dual is to CAST each column so that the proper data type is specified

This is how our test should look like and it works! Instead of using dual, a better way to run this test would be to create a temporary table with proper columns which reflect the data types used in the cursor, populate it with the expected result set

and compare it with the tested ref cursor

Just keep in mind that in order for such test to succeed you need to get

  • same column names
  • same column data types
  • same rows order

The last point can be tricky if in your package you don’t have an order by clause and it might require some additional coding.

Thanks for getting this far in reading my post, hope to get good news soon about the topic.
Cheers!

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

  1. Godfrey

    Thanks Roberto for making this post, it was really helpful. Have you tried testing for exception with NULL input parameter using the same user case in your post? when I tried iI keep getting java.lang.NullPointerException:null…..

    1. Roberto Rigliaco
      Roberto Rigliaco

      Hi Godfrey, many thanks, happy it was useful 🙂
      I haven’t tried that but will do it soon.
      I’m having also problems when testing Exceptions in case of:
      – functions;
      – procedures with OUT parameters.
      In these 2 cases, SQL Developer raises an exception “java.sql.SQLException: Missing defines”. I have feeling it’s because if the exception is raised the return value is not set. It should be handled by the frameworks but at the moment it doesn’t work. I’ve pinged Jeff Smith on Twitter and he answered that the dev team will be looking into the REF CURSOR issue soon. Fingers crossed, we really need that to work!

    2. Roberto Rigliaco
      Roberto Rigliaco

      Hi Godfrey, I’ve tried to test the same user case for exception (I’ve added a raise_application_error into my code just to try because null parameter doesn’t raise any errors).
      I have the same error you mentioned:
      java.lang.NullPointerException: null
      It seems they have to work on different issues in case of REF CURSORs. That’s really a pity, I’ll try to find a workaround to make it at least easier to test ref cursors.

      1. Godfrey

        Thanks Roberto for taking the time to test out the use case, I have been trying for weeks to find a workaround for weeks now but unsuccessful. Please do share if you find a solution.

        1. Roberto Rigliaco
          Roberto Rigliaco

          Hi Godfrey, there is a solution for the ref cursor trailing spaces issue I’ve described in the post, check the UPDATE section at the end. About the exception error, still waiting for an answer from Oracle.

Leave a Reply

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