This discussion is archived
1 Reply Latest reply: Dec 28, 2012 4:24 AM by AndrewMiller RSS

How do I check for null parameters in  Process Validations?

AndrewMiller Newbie
Currently Being Moderated
I am trying to create a Unit Test that validates that a record has been inserted into a table. However I am having problems when dealing with null parameters.

I created a simple table and insert procedure:

create table TEST_TAB (
A varchar2 (30) not null,
B varchar2 (30));

create or replace procedure TEST_TAB_INS (P_A in varchar2, P_B in varchar2) is
begin
insert into TEST_TAB values (P_A, P_B);
end;

Then I created a Unit Test with two implementations:

The first executes TEST_TAB_INS ('One', 'Two')
The second executes TEST_TAB_INS ('One', null)

I attached the following Query Returning Rows Process Validation to both implementations:

select *
from TEST_TAB
where A = '{P_A}'
and (B = '{P_B}' or '{P_B}' is null)

This should allow for the case where P_B is null. However the second test fails with a "Query check returned no rows" error.

I've tried altering the test to use nvl, but get the same results:

select *
from TEST_TAB
where A = '{P_A}'
and (B = '{P_B}' or nvl ('{P_B}', 'XXXX') = 'XXXX')

However, the following test succeeds:

select *
from TEST_TAB
where A = '{P_A}'
and (B = '{P_B}' or nvl ('{P_B}', 'XXXX') = '{P_B}')

Which suggests that null parameters are not being treated as nulls in Process Validations

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points