PL/SQL - Utplsql (Unit Test)

About

Utplsql is Xunit test Framework for PL/SQL.

Articles Related

Installation

• Follow the normal guide.
  • Create a user

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;

• Download: https://github.com/utPLSQL/utPLSQL/releases
• Install

@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

• Jenkins: Add a Post-build action to parse the test output file with the utplsql Jenkins jenkinsci/utplsql-plugin