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!

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

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

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

  2. Avatar
    Godfrey

    Thanks, Roberto. I am also waiting for someone from Oracle to respond back to me. In the meantime time, I am trying to see if I could rewrite the exception block in a different way but I have got no luck yet. Still getting java null pointer exception.

    1. Roberto Rigliaco
      Roberto Rigliaco

      Hi Godfrey, the issue regarding tests on exceptions inside functions is being addressed by Oracle. Hopefully next release will have it fixed.

  3. Avatar
    David Tinney

    Applause! Clap, clap, clap! Thank you for taking to time to document these shortcomings. I too have voiced my opinion on a few matters; SQL Developer code editor and my suggestions and issues were addressed. With “effective communication”, issues are properly explained with reproducible examples; that is the key to getting Oracle’s attention on matters important to us. “The squeaky wheel gets the grease”. If more folks would follow this example, the product will continue to improve.

    1. Roberto Rigliaco
      Roberto Rigliaco

      Hey David, thanks, I totally agree with you! I’ve created a video capture showing exactly how the issue happens when testing exceptions in functions and raised a SR through a friend. Well, Oracle is taking care of it now 🙂

  4. Roberto Rigliaco
    Roberto Rigliaco

    Good news: after pinging Oracle multiple times, with the version 18.3 they finally fixed the “java.sql.SQLException: Missing defines” error that was triggering when testing exceptions on functions.
    I’m going to write a full guide about Unit Testing with SQL Developer: I’ll cover some “tweaks” and configuration steps and that nobody ever mentions even though are very important to make everything work properly. Moreover, I will detail how to test REF CURSORs in a more comprehensive way.

  5. Avatar
    Dipak Patil

    Hi Roberto,

    Have you ever faced a problem while writing a unit test for,
    1. Procedure/Function with input parameter of table type data type.
    2. Overloaded Procedure/Function – while writing unit test it picks only firsts Procedure/Function and not allowing us to create for the overloaded one.

    Regards,
    Dipak

    1. Roberto Rigliaco
      Roberto Rigliaco

      Hi Dipak,
      I have tested an overloaded function and it works fine (SQL Developer version 19.4 on Oracle RDBMS 19.3). When I create the unit test by right-clicking on the overloaded functions, it shows me the correct parameters and the execution works fine as well.
      I have also tested a procedure with a table type and I cannot make it work. It’s not possible to correctly specify the expected return value in the GUI and even if I write my own PlSql block I get the error:

      Expected exception: [NONE], Received: [6533: ORA-06533: Subscript beyond count

      It seems Oracle is not good at testing the software that we are supposed to use for our tests 🙁
      I have also tried a procedure with an object type parameter and that works surprisingly well, at least the simple one I have used.
      Here is the code that I have used for my test:

      CREATE OR REPLACE
      PACKAGE TEST AS

      function get_id(p_num in number) return number;

      function get_id(p_num in number, p_type in varchar2) return number; — Overloaded

      procedure test_type(p_tab in INT_TAB, p_res1 out number);

      procedure test_obj(p_obj in myobj, p_res1 out varchar2, p_res2 out number);

      END TEST;
      /
      CREATE OR REPLACE
      PACKAGE BODY TEST AS

      function get_id(p_num in number) return number AS
      BEGIN
      RETURN 1;
      END get_id;

      function get_id(p_num in number, p_type in varchar2) return number AS
      BEGIN
      RETURN 2;
      END get_id;

      procedure test_type(p_tab in INT_TAB, p_res1 out number) AS
      BEGIN
      p_res1 := p_tab(1);
      END;

      procedure test_obj(p_obj in myobj, p_res1 out varchar2, p_res2 out number) AS
      BEGIN
      p_res1 := p_obj.col1;
      p_res2 := p_obj.col2;
      END;

      END TEST;
      /

      and the PlSql block I have used for the table type is

      declare
      l_var INT_TAB;
      l_res number;
      begin
      select 1
      bulk collect into l_var
      from dual;

      test.test_type(l_var, l_res);
      if nvl(l_res, 0) != 1 then
      raise_application_error(-20000, ‘Test failed’);
      end if;
      end;

      ———
      UPDATE:
      Dipak,
      the test on my procedure with a table type (test.test_type in my previous reply) works for me when using the “Dynamic query” with the following content:

      select INT_TAB(3,5,8) as P_TAB, 3 as P_RES1$ from dual

Leave a Reply

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