Toad World® Forums

Test a ref cursor which contains a custom object



I’m doing some research on different possibilities for unit testing in PL/SQL. I setup a little test database to do some testing. I have a custom object called ‘obj_customer’ which contains information about a customer. I have 2 functions which use this custom object. 1 function which gives back a collection of these custom objects and another function which gives back a ref cursor containing these custom objects.

I wrote unit tests for the function which returns a collection. This worked without any problems. But now I want to write tests for the function which gives back the ref cursor containing the custom objects. I have seen that you can count the elements of the ref cursor, this doesn’t work also. But is it possible to test custom objects in a ref cursor? Because I didn’t find an option for it or I must have overlooked it.

Thanks in advance

Message was edited by: NielsC


The most common way that a cursor variable is tested is to compare it to a query that returns the expected contents. So you do have to go through the steps to set up that query, but then Code Tester will do all the comparisons from there.


Yes that’s true. I have tested the usage of a ref cursor that just contains information of a select statement and that worked. But in the company where I’m doing an internship they use a ref cursor to return a number of objects to a Java webservice. I did not find out how I can make a test for checking those objects in the ref cursor.
My question is how can I build up a ref cursor with objects to use as Expected result for the test?
Or does this need a different approach?


I will try to find some time to put together an example soon, but you should be able to:

  1. First, create a MAP or ORDER method in the object type so that Oracle can compare two instances of this type.

  2. In initialization code, create a table function that returns a nested table of these object types.

  3. Write a query against the table function.


I’ll try to get that working. I didn’t know about the MAP and ORDER methods in an object, thank you! That’s maybe also the reason I couldn’t test this in utPLSQL.

The first step works. But I don’t know how to get further with step 2. Where can I find the correct initialization block to create that nested table? Because I see a initialization tab under “Inputs” and “Outcomes”. Or do you mean the initialization block in the tab “Customizations”? I tried to put the code in the different places but it didn’t work.

But another thing when I try to set up the outcome. I tried (just for testing) to do a test “COUNT = 0” on the return value (ref cursor) of the function I want to test and I get the following error:
Code Tester cannot determine the list of elements in the select list for this test. You must either specify a valid table, view or query in the Properties Window of the cursor variable to a static table, view or query (not an expression).
Is this normal?

Message was edited by: NielsC



It looks like I gave you some bad info and sent you on a bit of a wild goose chase.

My apologies.

Currently, Code Tester cannot compare cursor variables that fetch object types as part of the select list. That may be something we add in the future, but it’s not there now.

You will need to “wrap” that function (returning a CV of object types) with another function that fetches the data from the CV and moves it into a structure that can be compared - such as a collection of object types or a CV that retrieves the individual attributes of the object type.

That will work.

I will notify the dev team of this issue.

My apologies, SF



Thanks for your help and to clear this up! It would be handy to have this function in a future version of Code Tester. In the meantime I will recommend this software to the company because it is a very helpful tool -despite of the lack of this function-.

The next thing I need to research is the setup of a Continuous Integration environment. I will have a look how I can integrate the tests I’ve made with Code Tester.