PL/SQL - Utplsql (Unit Test)

> Procedural Languages > PL/SQL - (Procedure Language|PL) SQL

1 - About

Utplsql is Xunit test Framework for PL/SQL.

3 - 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;
Advertising

4 - 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

5 - 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

Advertising
lang/plsql/utplsql.txt · Last modified: 2017/09/06 22:27 by 108.162.245.8