a

a

Sample code

Test a Procedure

This Article is taken from utPLSQL site just to 
kickstart your dev , 
for more details visit utPLSQL site. 


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',
         ''
         );
   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;
/
Subscribe to: Comments (Atom)