< Previous Section: Examples | Next Section: Test a Function >

Test a Procedure

There are a couple of scenarios to consider:

The procedure runs some code and then passes back results through the parameter list. In this case, I can write a unit test that analyzes the OUT and IN OUT argument values.
The procedure runs some code, which changes other elements of the application (such as a database table or a file). The parameter list does not contain arguments that can be analyzed for successful execution. So to assert success, I will need to analyze/compare the data structures that have been modified.

Test Success Through Parameters

We'll start with a really simple example. I have built a procedure that accepts two dates and returns the number of seconds between them. Here it is:

/*file calc_secs_between.sp */
CREATE OR REPLACE PROCEDURE calc_secs_between (
   date1 IN DATE,
   date2 IN DATE,
   secs OUT NUMBER)
IS
BEGIN
   -- 24 hours in a day, 
   -- 60 minutes in an hour,
   -- 60 seconds in a minute...
   secs := (date2 - date1) * 24 * 60 * 60;
END;
/

After compiling my code cleanly, I generate my test package:

SQL> SET SERVEROUTPUT ON FORMAT WRAPPED
SQL> exec utGen.testpkg ('calc_secs_between ')
CREATE OR REPLACE PACKAGE ut_calc_secs_between
IS
   PROCEDURE ut_setup;
   PROCEDURE ut_teardown;

   -- For each program to test...
   PROCEDURE ut_CALC_SECS_BETWEEN;
END ut_calc_secs_between;
/
CREATE OR REPLACE PACKAGE BODY ut_calc_secs_between
IS
   PROCEDURE ut_setup
   IS
   BEGIN
      NULL;
   END;

   PROCEDURE ut_teardown
   IS
   BEGIN
      NULL;
   END;

   -- For each program to test...
   PROCEDURE ut_CALC_SECS_BETWEEN IS
   BEGIN
      CALC_SECS_BETWEEN (
            DATE1 => ''
            ,
            DATE2 => ''
            ,
            SECS => ''
       );

      utAssert.this (
         'Test of CALC_SECS_BETWEEN',
         '<boolean expression>'
         );
   END ut_CALC_SECS_BETWEEN;

END ut_calc_secs_between;
/

I generated the output to the screen, but it is actually easier to deposit the code directly into two separate files for package spec and body, ut_calc_secs_between.pks and ut_calc_secs_between.pkb, which I do as follows:

SQL> exec utGen.testpkg ('calc_secs_between ', output_type_in => utGen.c_file)

By conforming to this standard, utPLSQL can automatically compile this code before each test. I now edit the ut_calc_secs_between procedure to test for various cases:

PROCEDURE ut_CALC_SECS_BETWEEN 
IS
   secs PLS_INTEGER;
BEGIN
   CALC_SECS_BETWEEN (
         DATE1 => SYSDATE
         ,
         DATE2 => SYSDATE
         ,
         SECS => secs
    );

   utAssert.eq (
      'Same dates',
      secs, 
      0
      );
      
   CALC_SECS_BETWEEN (
         DATE1 => SYSDATE
         ,
         DATE2 => SYSDATE+1
         ,
         SECS => secs
    );

   utAssert.eq (
      'Exactly one day',
      secs, 
      24 * 60 * 60
      );
      
END ut_CALC_SECS_BETWEEN;

and now I can run my test:

SQL> exec utplsql.test ('calc_secs_between')
.
>    SSSS   U     U   CCC     CCC   EEEEEEE   SSSS     SSSS
>   S    S  U     U  C   C   C   C  E        S    S   S    S
>  S        U     U C     C C     C E       S        S
>   S       U     U C       C       E        S        S
>    SSSS   U     U C       C       EEEE      SSSS     SSSS
>        S  U     U C       C       E             S        S
>         S U     U C     C C     C E              S        S
>   S    S   U   U   C   C   C   C  E        S    S   S    S
>    SSSS     UUU     CCC     CCC   EEEEEEE   SSSS     SSSS
.
 SUCCESS: "calc_secs_between"

Certainly, there are a variety of other conditions to test, but this should give you a good idea of how to go about it!

Test Success by Analyzing Impact

Now let's consider a more complicated situation. I have a procedure that truncates all the rows in the specified table. To do this I just use dynamic SQL, as you can see in:

/*file truncit.sp */
CREATE OR REPLACE PROCEDURE truncit (
   tab IN VARCHAR2,
   sch IN VARCHAR2 := NULL
)
IS
BEGIN
   EXECUTE IMMEDIATE 'truncate table ' || NVL (sch, USER) || '.' || tab;
END;
/

After I run this test, I cannot simply check the value returned by the procedure. Instead, I must check to see how many rows are left in the table. Fortunately, I have another dynamic SQL utility to help me out here, one that returns the count of rows in any table: (Note that you could also use utAssert.eqqueryvalue here.)

/*file tabcount.sf */
CREATE OR REPLACE FUNCTION tabcount (
   sch IN VARCHAR2,
   tab IN VARCHAR2)
   RETURN INTEGER
IS
   retval  INTEGER;
BEGIN
   EXECUTE IMMEDIATE 
      'SELECT COUNT(*) FROM ' || sch || '.' || tab
      INTO retval; 
   RETURN retval;
EXCEPTION
    WHEN OTHERS 
    THEN
       RETURN NULL; 
END;
/

So I will generate a package to test truncit and then modify the package body:

SQL> SET SERVEROUTPUT ON FORMAT WRAPPED
SQL> exec utGen.testpkg ('truncit', output_type_in => utGen.c_file)

To run my test, I need to truncate a table. That is an irreversible action, so I will create a "temporary" table in the setup procedure and drop it in the teardown procedure. Then I will run my code and use tabCount to validate the results:

/*file ut_truncit.pkb */
CREATE OR REPLACE PACKAGE BODY ut_truncit
IS
   PROCEDURE ut_setup
   IS
   BEGIN
      EXECUTE IMMEDIATE 
         'CREATE TABLE temp_emp AS SELECT * FROM employee';
   END;
   
   PROCEDURE ut_teardown
   IS
   BEGIN
      EXECUTE IMMEDIATE 
         'DROP TABLE temp_emp';
   END;

   -- For each program to test...
   PROCEDURE ut_TRUNCIT IS
   BEGIN
      TRUNCIT (
            TAB => 'temp_emp'
            ,
            SCH => USER
       );

      utAssert.eq (
         'Test of TRUNCIT',
         tabcount (USER, 'temp_emp'),
         0
         );
   END ut_TRUNCIT;

END ut_truncit;
/

Not quite as straightforward as checking values returned in OUT or IN OUT arguments, but not too awful, right? Of course, things can get considerably more complicated as your code (and the results you must test for) grows more complex. Regardless, you will find it easier to build and run your tests through utPLSQL than through more ad hoc and considerably less organized approaches.

< Previous Section: Examples | Next Section: Test a Function >