Toad World® Forums

Need an example on how to setup a collection of expected values by a query


#1

Hi,

I want to test a PL/SQL package function that returns a collection (nested table) and check if the values returned are as expected. In my case I do not know the values of the resulting collection upfront (like coll(1) = ‘value1’).

But I can determine the expected values of the resulting collection from a query.

So my question is if you could provide an example on how to setup an resulting collection from a query. From the examples I found I could not find out how to do it. It probably can be done on the ‘Initialization’ tab of the outcome. But how?

Best regards,

Thomas Baumgärtler

BTW: Code Tester version is v2.6.1


#2

Hi Thomas,

It looks like Code Tester has a problem to do what you need. I am checking this now and at the same time thinking about a workaround.

How “wide” your nested table is? I mean how many elements in the record, which a row of your nested table belongs to?

Thanks,

Leonid Khukhlovich,

Code Tester Team.


#3

Hi Thomas,

I created this document for you as a response to your post but I think it can be useful for other customers creating tests of type “Collection is equal to query”.

I created a very simple example and I hope using it I will be able to clarify what is the Code Tester approach to this problem and what might be a workaround for you.

My example works with well known SCOTT schema, with EMP table:

CREATE OR REPLACE PACKAGE ct_avsi

IS

TYPE emp_rt IS RECORD

(

ename emp.ename%TYPE

, job emp.job%TYPE

, sal emp.sal%TYPE

);

TYPE emp_aa IS TABLE OF emp_rt

INDEX BY BINARY_INTEGER;

TYPE emp_nt IS TABLE OF emp_rt;

FUNCTION get_nt (where_in IN VARCHAR2)

RETURN emp_nt;

END ct_avsi;

As you can see I declared function get_nt in this package, which returns nested table of emp_nt type. Let’s see how to test this function.

First of all the body of declared CT_AVSI package is:

CREATE OR REPLACE PACKAGE BODY ct_avsi

IS

FUNCTION get_nt (where_in IN VARCHAR2)

RETURN emp_nt

IS

TYPE emp_aa IS TABLE OF emp_rt

INDEX BY BINARY_INTEGER; – for SELECT

l_aa emp_aa;

retval emp_nt; – Result

BEGIN

– Select needed rows from EMP table

EXECUTE IMMEDIATE ‘SELECT ename, job, sal FROM emp WHERE empno in (’ || where_in || ‘) ORDER BY empno’ BULK COLLECT INTO l_aa;

– Populate information in nested table

IF l_aa.COUNT > 0

THEN

retval := emp_nt ();

FOR i IN l_aa.FIRST … l_aa.LAST

LOOP

retval.EXTEND;

retval (retval.COUNT) := l_aa (i);

END LOOP;

END IF;

RETURN retval;

END get_nt;

END ct_avsi;

As you can see the parameter where_in of the tested function is part of WHERE clause of query from EMP table. This parameter is a list of EMP_NO keys from EMP table. I will use ‘7839,7369,7566’ string as where_in parameter in my example.

I compiled this package in SCOTT schema, connected Code Tester to it and created 3 different test cases, which explain how to test nested tables in Code Tester for Oracle.

As the result I created 2 video clips for you.

In the first clip I create classic test case of “Collection is equal to collection” type.

You write that in your situation you do not have values for expected collection but you have a query, which can bring these values upon request. For this situation I created another test case of “Collection is equal to query” type. I would recommend you this approach as a solution but unfortunately this type of test works fine with associative arrays but does not work with nested tables and varrays. We are going to fix this issue in the next release. All this is explained in the first clip.

In my second clip I show you how to use “Initialization” section of outcome to bypass the issue and propose you to use this approach as a workaround.

Thanks,

Leonid Khukhlovich,

Code Tester team.

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/71/1st_5F00_clip.wmv:550:0]

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/71/2nd_5F00_clip.wmv:550:0]


#4

Hi Leonid,

I think this is what I need to get it done. Good job :wink:

Best regards,

Thomas