CODEWITHSUNDEEP
oracle-databaseoracle11gtdd
Lance Kind
Lance Kind

Reputation: 1091

utPL/Sql testing contents of a table

I've got a table like this:

colors
----- 
red 
green 
blue

Is there a way to unit test for the existence of these other than writing a utAssert.eqqueryvalue(...) for each record?

Upvotes: 1

Views: 288

Answers (2)

APC
APC

Reputation: 146239

One option is to build a test table with the expected results in the test fixture, and use utAssert.eqTable() to validate the outcome of running the code under test. Find out more.

The need to create heap tables to compare is unfortunate. It puts a strain on the Setup() and requires transaction management. Ideally we would like to have the comparison values in a collection, but there's no utAssert interface to compare a collection and a table. But you could build your own.

Alternatively do this:

utassert.eqquery (
  'Insert three colourns',
  'select * from YOUR_TABLE',
  'select ''red'' as colour from dual union all
   select ''green'' as colour from dual union all
   select ''blue'' as colour from dual'
);

Alas the more columns in your table the more tedious this will get.

Upvotes: 1

are
are

Reputation: 2615

if you need complex solution - you can pass array of comma separated values into query and check that all the values exists in your table if query below returns something - it means that test fails

SELECT EXTRACT (VALUE (d), '//row/text()').getstringval () as test_val
  FROM (SELECT XMLTYPE (   '<rows><row>'
                        || REPLACE ('red,green,blue', ',', '</row><row>')
                        || '</row></rows>'
                       ) AS xmlval
          FROM DUAL) x,
       TABLE (XMLSEQUENCE (EXTRACT (x.xmlval, '/rows/row'))) d
except
select colors from my_table

Upvotes: 0

Related Questions