PL/SQL - Utplsql (Unit Test)

About

Utplsql is Xunit test Framework for PL/SQL.

Installation

connect system/manager
create user utp identified by utp default tablespace
  users temporary tablespace temp;

grant create session, create table, create procedure,
  create sequence, create view, create public synonym,
  drop public synonym to utp;

alter user utp quota unlimited on users;
@ut_i_do install
-- @ut_i_do uninstall to unninstall
  • Then modify the function utplsql.progexists to return always true. The framework expect one package for one test package.
 FUNCTION progexists (
      prog_in   IN   VARCHAR2,
      sch_in    IN   VARCHAR2
   )
      RETURN BOOLEAN
   IS
     .....
   BEGIN
   -- Add the return statement below
      return true;

Basic Example

  • Package Spec
CREATE OR REPLACE PACKAGE ut_my_package_pk
IS

   PROCEDURE ut_SETUP;
   PROCEDURE ut_teardown;
   
   /*
	Test 
   */
   PROCEDURE ut_my_test;
  
END ut_my_package_pk;
  • Package Body
CREATE OR REPLACE PACKAGE body ut_my_package_pk
AS

    PROCEDURE ut_setup
    AS
    BEGIN
        -- Object created at the start
        NULL;
    END ut_setup;

    PROCEDURE ut_teardown
    AS
    BEGIN
        -- Object destroyed at the stop
        NULL;
    END ut_teardown;

    PROCEDURE ut_my_test
    AS
    BEGIN

        utAssert.eq( 'The values must be equals', 1, 1 );
        utAssert.isnotnull( 'Not Null test', 1 );

    END;

END ut_my_package_pk;
  • Run
set serveroutput on;
exec utplsql.test ('my_package_pk', recompile_in => FALSE);
  • Output
PL/SQL procedure successfully completed.

. 
>    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: ".my_package_pk"
. 
> Individual Test Case Results:
>
SUCCESS - my_package_pk.UT_MY_TEST: EQ "The values must be equals" Expected "1" and got "1"
>
SUCCESS - my_package_pk.UT_MY_TEST: ISNOTNULL "Not Null test" Expected "NOT NULL" and got "1"
>
>
> Errors recorded in utPLSQL Error Log:
>
> NONE FOUND

Jenkins Integration

  • Step one: Create the build test script buildTest.sql
set serveroutput on;
set line 1000;
exec utplsql.test ('test_pk', recompile_in => FALSE);
exec utplsql.test ('test2_pk', recompile_in => FALSE);
...
exit;
  • Database credential binding

  • Jenkins: Add a step that start the test script with Sqlplus and redirect the output to a file
sqlplus %SQL_USER%/\"%SQL_PWD%\"@hostname:1521/ServiceName @buildTest.sql > buildTestOut.txt


Powered by ComboStrap