1 Reply Latest reply: Dec 28, 2012 6:24 AM by Andrew Miller RSS

    How do I check for null parameters in  Process Validations?

    Andrew Miller
      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
      insert into TEST_TAB values (P_A, P_B);

      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