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.

Let’s start creating a simple table with 3 rows

create table contacts(id         number primary key,
                      first_name varchar2(50),
                      last_name  varchar2(50),
                      city       varchar2(50),
                      active     varchar2(1) default 'Y');
insert into contacts(id, first_name, last_name, city, active) values(1, 'Mickey', 'Mouse', 'Minneapolis', 'Y');
insert into contacts(id, first_name, last_name, city, active) values(2, 'Donald', 'Duck', 'New York', 'N');
insert into contacts(id, first_name, last_name, city, active) values(3, 'Minnie', 'Mouse', 'Minneapolis', 'Y');
commit;

create table contacts(id number primary key,

first_name varchar2(50),

last_name varchar2(50),

city varchar2(50),

active varchar2(1) default 'Y');

insert into contacts(id, first_name, last_name, city, active) values(1, 'Mickey', 'Mouse', 'Minneapolis', 'Y');

insert into contacts(id, first_name, last_name, city, active) values(2, 'Donald', 'Duck', 'New York', 'N');

insert into contacts(id, first_name, last_name, city, active) values(3, 'Minnie', 'Mouse', 'Minneapolis', 'Y');

commit;

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

CREATE OR REPLACE PACKAGE PKG_CONTACTS AS

procedure get_contacts(pi_active   in varchar2,
                       po_contacts out sys_refcursor);

END PKG_CONTACTS;
/
CREATE OR REPLACE PACKAGE BODY PKG_CONTACTS AS

procedure get_contacts(pi_active   in varchar2,
                       po_contacts out sys_refcursor) is
begin
  open po_contacts for select id, first_name, last_name, city, active from contacts where active = pi_active;
end;

END PKG_CONTACTS;
/

CREATE OR REPLACE PACKAGE PKG_CONTACTS AS

procedure get_contacts(pi_active in varchar2,

po_contacts out sys_refcursor);

END PKG_CONTACTS;

CREATE OR REPLACE PACKAGE BODY PKG_CONTACTS AS

procedure get_contacts(pi_active in varchar2,

po_contacts out sys_refcursor) is

begin

open po_contacts for select id, first_name, last_name, city, active from contacts where active = pi_active;

end;

END PKG_CONTACTS;

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

select ? as PI_ACTIVE, ? as PO_CONTACTS$ from ? where ?

1	select ? as PI_ACTIVE, ? as PO_CONTACTS$ from ? where ?

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

create or replace function get_refcur(pi_query in varchar2) return sys_refcursor is
  l_cur sys_refcursor;
begin
  open l_cur for pi_query;
  return(l_cur);
end get_refcur;
/

create or replace function get_refcur(pi_query in varchar2) return sys_refcursor is

l_cur sys_refcursor;

begin

open l_cur for pi_query;

return(l_cur);

end get_refcur;

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

select 'Y' as PI_ACTIVE, get_refcur('select 1 as id, ''Mickey'' as first_name,
                                               ''Mouse'' as last_name,
                                               ''Minneapolis'' as city,
                                               ''Y'' as active
                                               from dual
                                     union all
                                     select 3 as id, ''Minnie'' as first_name,
                                               ''Mouse'' as last_name,
                                               ''Minneapolis'' as city,
                                               ''Y'' as active
                                               from dual') as PO_CONTACTS$ from dual

select 'Y' as PI_ACTIVE, get_refcur('select 1 as id, ''Mickey'' as first_name,

''Mouse'' as last_name,

''Minneapolis'' as city,

''Y'' as active

from dual

union all

select 3 as id, ''Minnie'' as first_name,

''Mouse'' as last_name,

''Minneapolis'' as city,

''Y'' as active

from dual') as PO_CONTACTS$ from dual

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

Expected: [
        ID FIRST_ LAST_ CITY        A
---------- ------ ----- ----------- -
         1 Mickey Mouse Minneapolis Y
         3 Minnie Mouse Minneapolis Y
], Received: [
        ID FIRST_NAME                                         LAST_NAME                                          CITY                                               A
---------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -
         1 Mickey                                             Mouse                                              Minneapolis                                        Y
         3 Minnie                                             Mouse                                              Minneapolis                                        Y
]

Expected: [

ID FIRST_ LAST_ CITY A

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

1 Mickey Mouse Minneapolis Y

3 Minnie Mouse Minneapolis Y

], Received: [

ID FIRST_NAME LAST_NAME CITY A

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

1 Mickey Mouse Minneapolis Y

3 Minnie Mouse Minneapolis Y

]

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

select 'Y' as PI_ACTIVE, get_refcur('select 1 as id, rpad(''Mickey'', 50, '' '') as first_name,
                                               rpad(''Mouse'', 50, '' '') as last_name,
                                               rpad(''Minneapolis'', 50, '' '') as city,
                                               ''Y'' as active
                                               from dual
                                     union all
                                     select 3 as id, rpad(''Minnie'', 50, '' '') as first_name,
                                               rpad(''Mouse'', 50, '' '') as last_name,
                                               rpad(''Minneapolis'', 50, '' '') as city,
                                               ''Y'' as active
                                               from dual') as PO_CONTACTS$ from dual

select 'Y' as PI_ACTIVE, get_refcur('select 1 as id, rpad(''Mickey'', 50, '' '') as first_name,

rpad(''Mouse'', 50, '' '') as last_name,

rpad(''Minneapolis'', 50, '' '') as city,

''Y'' as active

from dual

union all

select 3 as id, rpad(''Minnie'', 50, '' '') as first_name,

rpad(''Mouse'', 50, '' '') as last_name,

rpad(''Minneapolis'', 50, '' '') as city,

''Y'' as active

from dual') as PO_CONTACTS$ from dual

In this case the test is successful with the message

Expected: [
        ID FIRST_NAME                                         LAST_NAME                                          CITY                                               A
---------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -
         1 Mickey                                             Mouse                                              Minneapolis                                        Y
         3 Minnie                                             Mouse                                              Minneapolis                                        Y
], Received: [
        ID FIRST_NAME                                         LAST_NAME                                          CITY                                               A
---------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -
         1 Mickey                                             Mouse                                              Minneapolis                                        Y
         3 Minnie                                             Mouse                                              Minneapolis                                        Y
]

Expected: [

ID FIRST_NAME LAST_NAME CITY A

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

1 Mickey Mouse Minneapolis Y

3 Minnie Mouse Minneapolis Y

], Received: [

ID FIRST_NAME LAST_NAME CITY A

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

1 Mickey Mouse Minneapolis Y

3 Minnie Mouse Minneapolis Y

]

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

DECLARE
    l_refcur sys_refcursor;
BEGIN
    null;
END;

DECLARE

l_refcur sys_refcursor;

BEGIN

null;

END;

the test fails with the exception

Validation User PL/Sql Code failed: Unable to convert <oracle.jdbc.driver.ForwardOnlyResultSet@284369c0> to REF CURSOR.

1	Validation User PL/Sql Code failed: Unable to convert <oracle.jdbc.driver.ForwardOnlyResultSet@284369c0> to REF CURSOR.

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)

create or replace procedure empty as 
begin
  null;
end empty;
/

create or replace procedure empty as

begin

null;

end empty;

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

DECLARE
    l_refcur   sys_refcursor;
    l_count_rc number := 0;
    l_count    number;
    l_row      CONTACTS%rowtype;
BEGIN
    -- Count active contacts from table
    select count(*)
      into l_count
      from CONTACTS
    where ACTIVE = 'Y';

    -- Count active contacts returned by the refcur
    pkg_contacts.get_contacts('Y', l_refcur);
    loop
        fetch l_refcur into l_row;
        exit when l_refcur%notfound;
        l_count_rc := l_count_rc + 1;
    end loop;
    
    if l_count_rc != l_count then
        raise_application_error(-20000, 'Wrong number of contacts');
    end if;
END;

DECLARE

l_refcur sys_refcursor;

l_count_rc number := 0;

l_count number;

l_row CONTACTS%rowtype;

BEGIN

-- Count active contacts from table

select count(*)

into l_count

from CONTACTS

where ACTIVE = 'Y';

-- Count active contacts returned by the refcur

pkg_contacts.get_contacts('Y', l_refcur);

loop

fetch l_refcur into l_row;

exit when l_refcur%notfound;

l_count_rc := l_count_rc + 1;

end loop;

if l_count_rc != l_count then

raise_application_error(-20000, 'Wrong number of contacts');

end if;

END;

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

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

select 'Y' as PI_ACTIVE, get_refcur('select id,
                                     cast(first_name as varchar2(50)) as first_name,
                                     cast(last_name as varchar2(50)) as last_name,
                                     cast(city as varchar2(50)) as city,
                                     cast(active as varchar2(1)) as active
                                     from (
                                     select 1 as id, ''Mickey'' as first_name,
                                               ''Mouse'' as last_name,
                                               ''Minneapolis'' as city,
                                               ''Y'' as active
                                               from dual
                                     union all
                                     select 3 as id, ''Minnie'' as first_name,
                                               ''Mouse'' as last_name,
                                               ''Minneapolis'' as city,
                                               ''Y'' as active
                                               from dual
                                               )') as PO_CONTACTS$ from dual

select 'Y' as PI_ACTIVE, get_refcur('select id,

cast(first_name as varchar2(50)) as first_name,

cast(last_name as varchar2(50)) as last_name,

cast(city as varchar2(50)) as city,

cast(active as varchar2(1)) as active

from (

select 1 as id, ''Mickey'' as first_name,

''Mouse'' as last_name,

''Minneapolis'' as city,

''Y'' as active

from dual

union all

select 3 as id, ''Minnie'' as first_name,

''Mouse'' as last_name,

''Minneapolis'' as city,

''Y'' as active

from dual

)') as PO_CONTACTS$ from dual

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

create table tmp#cnt#test(id         number,
                          first_name varchar2(50),
                          last_name  varchar2(50),
                          city       varchar2(50),
                          active     varchar2(1));
insert into tmp#cnt#test(id, first_name, last_name, city, active) values(1, 'Mickey', 'Mouse', 'Minneapolis', 'Y');
insert into tmp#cnt#test(id, first_name, last_name, city, active) values(3, 'Minnie', 'Mouse', 'Minneapolis', 'Y');
commit;

create table tmp#cnt#test(id number,

first_name varchar2(50),

last_name varchar2(50),

city varchar2(50),

active varchar2(1));

insert into tmp#cnt#test(id, first_name, last_name, city, active) values(1, 'Mickey', 'Mouse', 'Minneapolis', 'Y');

insert into tmp#cnt#test(id, first_name, last_name, city, active) values(3, 'Minnie', 'Mouse', 'Minneapolis', 'Y');

commit;

and compare it with the tested ref cursor

select 'Y' as PI_ACTIVE, get_refcur('select id,
                                            first_name,
                                            last_name,
                                            city,
                                            active from tmp#cnt#test') as PO_CONTACTS$ from dual

select 'Y' as PI_ACTIVE, get_refcur('select id,

first_name,

last_name,

city,

active from tmp#cnt#test') as PO_CONTACTS$ from dual

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!

Facebook

Twitter

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

- June 11, 2018 at 18:51 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. - June 11, 2018 at 19:00 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. - June 12, 2018 at 12:32 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. - June 12, 2018 at 12:47 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. - June 12, 2018 at 12:59 Roberto Rigliaco
      
      Sure, I will do it with pleasure!
      
    2. - June 21, 2018 at 13:24 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.
      
- August 8, 2018 at 16:29 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. - August 19, 2018 at 17:47 Roberto Rigliaco
  
  Hi Godfrey, the issue regarding tests on exceptions inside functions is being addressed by Oracle. Hopefully next release will have it fixed.
  
- August 19, 2018 at 15:58 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. - August 19, 2018 at 17:45 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 🙂
  
- October 20, 2018 at 21:11 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.

- May 15, 2020 at 07:16 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. - May 16, 2020 at 18:13 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
  
  1. - May 18, 2020 at 14:34 Dipak Patil
    
    Thanks a lot Roberto. I will give a try.

Oracle Findings

UPDATE!

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

Leave a Reply Cancel reply