utplsql: unit testing for oracle pl/sql

utPLSQL

A Unit Testing Framework for Oracle PL/SQL Code

Achieving PL/SQL Excellence

Steven FeuersteinOriginal creator of utPLSQL, not currently active on project (2016)

Visit the Github Repo for utPLSQL: http://https://github.com/utPLSQL

Aw, Gee, Mom, do I have to test my code? Only if you want:

Successful applications– That is the whole point, after all.

Successful applications on time– "We don't have time to test"? Hah! Solid testing improves the

chances of putting out quality code on time.

Developer and user confidence– It's a wonderful feeling to know that our code really and truly

works. – Our users then love us and support us.

Different Kinds of Tests Functional, System, Integration tests

– Written by the software customer, it answers the question “What do I need to verify before I am confident this feature works?”

Many other types of tests– Stress tests – real-world workload testing– Monkey tests – tests responses to unusual conditions– Parallel tests – compares behavior of new and old systems

And then there are unit tests...– Written by the developer, they test

individual units (procedure or function in PL/SQL)

The focus in this

presentation.

Wouldn't it be great if... It was easy to construct tests

– An agreed-upon and effective approach to test construction that everyone can understand and follow

It was easy to run tests– And see the results, instantly and automatically.

Testing were completely integrated into my development, QA and maintenance processes– No program goes to QA until it has passed a battery of tests– Anyone can maintain with confidence, because my test suite

automatically validates my changes

POLL How do you (or your team) unit test your PL/SQL

code today? Possible answers:

– Everyone does their own thing and we hope for the best.

– Our users test our code.– We have a formal test process – We use automated testing software

Typical Development and Testing Scenario

Let's walk through a typical development/test flow, critique it, and then take a look at how you might do the same thing with utPLSQL

We'll use a very simple example: – building an improvement to the SUBSTR function

Improving upon SUBSTR SUBSTR returns the sub-string specified by start

position and number of characters.

just10 := SUBSTR (full_string, 3, 10); Grab 10 characters staring

from 3rd position

Suppose I have the starting and ending positions (or even sub-strings). How do I use SUBSTR to solve this problem?

mystring := SUBSTR (full_string, 5, 17); -- start and end? Nah...mystring := SUBSTR (full_string, 5, 12); -- end – start?mystring := SUBSTR (full_string, 5, 13); -- end – start + 1?mystring := SUBSTR (full_string, 5, 11); -- end – start - 1?

Grab everything between the 5th and 17th position

But which of these does the job?

A Straightforward Abstraction Create a “between string” function that works with

starting and ending positionsCREATE OR REPLACE FUNCTION betwnStr ( string_in IN VARCHAR2, start_in IN INTEGER, end_in IN INTEGER ) RETURN VARCHAR2ISBEGIN RETURN ( SUBSTR ( string_in, start_in, end_in – start_in + 1 ) );END;

myString := betwnStr (yourString, 5, 17);

A Truly Crude Testing Technique

Let’s see…what should I check for?SQL> exec DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', 3, 5));cde

And so on, and so forth...very time consuming, very haphazard...and what happens when you want to run the tests a second time?

Oh, and what about this?SQL> exec DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', 0, 2));ab

Here's a good one:SQL> exec DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', 3, 100));cdefgh

Slightly Better: Build a Test Script

Let’s see…what should I check for?BEGIN DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', 3, 5)); DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', 0, 2)); DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', NULL, 5)); DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', 3, NULL)); DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', 3, 100)); DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', -3, -5)); DBMS_OUTPUT.PUT_LINE (betwnstr ('abcdefgh', -3, 0));END;/

SQL> @betwnstr.tstcdeab

cdefgh

fgh

Yielding this output:

Which tells us what precisely?

Problems with Typical Testing Almost entirely ad hoc

– No comprehensive effort to compile test cases– No infrastructure to record cases and administer tests

Difficult to verify correctness– If you wrote the program you might know that it was supposed to

be “abc” or “abcd”– But what about somebody who comes along to maintain the

code? Reliance on the user community to test

– Since we are never really sure we’ve tested properly, we rely on our users (or, we are lucky, the QA department) to finish our job

There has got to be a better way!

How About Extreme Programming? "Extreme programming" sounds, well, extreme

– It really isn't– It takes well-accepted ideas about programming and

takes them to extremes Resources on extreme programming:

– www.xprogramming.com– www.extremeprogramming.org– Extreme Programming Explained by Kent Beck– Extreme Programming Installed by Ron Jeffries, et al– Planning Extreme Programming by Ron Jeffries, et al

Common Sense Taken to Extremes

If code reviews are good, we'll review code all the time (pair programming)

If testing is good, everybody will test all the time (unit testing), even the customers (functional testing)

If design is good, we'll make it a part of everybody's daily business (refactoring)

If simplicity is good, we'll always leave the system with the simplest design that supports its current functionality (the simplest thing that could possibly work)

A selection of extremes from XP Explained, Beck

Extreme Unit Testing Principles

Write unit tests first!

Code a little, test a lot

Build automated red light-green light tests

If testing is good, everybody will test all the time

Write Unit Tests First What is my program supposed to do?

– By writing the test first, you concentrate more on the interface than the implementation

Write unit tests…– Before you start writing your program– Before you fix a bug (turn each bug report into a test case)– To "document" each enhancement request -- then implement

All of string from starting point.

Positive # greater than length of string

Positive #NULLSmaller positive #Positive #NULLNULLNOT NULLNULLNULLNULLResultEnd ValueStart Value

Code a Little, Test a Lot We are all in a hurry to get our coding done, but the

reality is that most of our time is spent on debugging, not writing code – We need to develop our code cleaner the "first time"

Incremental development, coupled with comprehensive testing, will improve productivity and code quality simultaneously– Make small changes, then test– Add small pieces of functionality, then test– Constantly add to your test cases for the unit test

Automated, Red Light-Green Light Tests

If we are going to build lots of tests and run them often, we need to be able to run those tests easily– This includes the setting up and cleaning up of test data and

other elements– If this process is not automated, developers will not test

A developer should be able to determine at a glance whether the code passed its unit tests– Do not leave it up to the developer to analyze the results to

determine the status of the tests This is called the red-light, green-light approach

– Your code does not work until you get a "green light" – 100% success

Testing the utPLSQL Way A unit testing “framework” for PL/SQL developers

– Set of processes and code that conform to the Extreme Programming unit testing principles

SQL> exec utplsql.test ('betwnstr').> 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: "betwnstr"

utPLSQL Architecture utPLSQL is a fun demonstration of the use of dynamic SQL (really,

dynamic PL/SQL) and packaged data– utPLSQL.test constructs the names of and executes the setup, unit test

and teardown procedures based on the program name you provide. – utAssert populates a collection with results (failure) data

ut_Setup

ut_TestMe

ut_Teardown

utPLSQL.testTest Engine

Your Test Package

Results Array

Test for NullsInvalid IDValid ID

Start date too lateEnd date too early

Name unique

Report Results

12

34

Assert EQ

Assert NULL

Assert EqTable

Assertion API

5

Steps in Using utPLSQLStep 1. Download and install utPLSQL

Step 2. Choose a program to test and identify the test cases

Step 3. Build a test package that incorporates those test cases

Step 4. Run your test using the utPLSQL engine

Download and Install utPLSQL

Visit http://oracle.oreilly.com/utplsql

Unzip, open up the documentation, install the utPLSQL software base, and you are ready to test– Installs in Oracle7, Oracle8 and Oracle8i– You can create a separate schema to hold utPLSQL

source or install a version for each developer

Choose Program, Define Test Cases

You can test stand-alone procedures or packages, test the entire set of programs in a package or test a subset of those programs– Start with a small program to try out the approach.

Build your grid of input values and expected outputs.

Once test cases are defined, it is time to translate them into code...the test package!

Build a Test Package Build a test package that

contains a unit test for each program in your package

Must conform to the standard utPLSQL API:– Public setup and teardown

procedures– Separate procedures for each

unit test

Best approach: generate the starting point of the test package with the utGen package

SQL> exec utGen.testpkg ('betwnstr');CREATE OR REPLACE PACKAGE ut_betwnstrIS PROCEDURE ut_setup; PROCEDURE ut_teardown; PROCEDURE ut_BETWNSTR;END ut_betwnstr;/CREATE OR REPLACE PACKAGE BODY ut_betwnstrIS PROCEDURE ut_setup ... END;

PROCEDURE ut_teardown ... END;

PROCEDURE ut_BETWNSTR IS BEGIN utAssert.this ( 'Test of BETWNSTR', BETWNSTR( STRING_IN => '', START_IN => '', END_IN => '') ); END ut_BETWNSTR;END ut_betwnstr;

Example of Gen'd Test Code It can take lots of code

to properly test a program.

The more you can generate, the better.

PROCEDURE ut_BETWNSTRIS against_this VARCHAR2(2000); check_this VARCHAR2(2000);BEGIN -- Define "control" operation for "normal" against_this := SUBSTR ('abcdefg',0,2); -- Execute test code for "normal" check_this := BETWNSTR ( STRING_IN => 'abcdefgh' , START_IN => 0 , END_IN => 2 ); -- Assert success for "normal" utAssert.eq ( 'normal', check_this, against_this ); ...

100% Generation!

Complete Unit Test Proc Every unit test

procedure consists of three main parts (which are sometimes collapsed together, depending on the simplicity of the code being tested):– Set up the control

(which might already be done with the set up procedure).

– Run the code to be tested.

– Compare results using the utAssert package

PROCEDURE ut_del1IS fdbk PLS_INTEGER;BEGIN /* Delete that finds no rows. */ EXECUTE IMMEDIATE ' DELETE FROM ut_DEL1 WHERE employee_id = -1';

te_employee.del ( -1, rowcount_out => fdbk);

utassert.eqtable ( 'No rows deleted', 'EMPLOYEE', 'ut_DEL1');EXCEPTION WHEN OTHERS THEN utassert.this ( 'DEL1 exception ' || SQLERRM, SQLCODE = 0 );END;

Control

Test

Compare

Error Failure

Apply utAssertion Validators

The utAssert offers a set of pre-defined assertion programs that test for the condition you specify and record any failures for later red light-green light reports. You can assert that:– Two scalars, tables, collections, pipes, files and queries are equal– A value is NULL or is NOT NULL– A Boolean expression is TRUE

PROCEDURE ut_BETWNSTR IS BEGIN ... utAssert.eq ('Typical valid usage', BETWNSTR(STRING_IN => 'abcdefg', START_IN => 3, END_IN => 5), 'cde'); utAssert.eqFile ('Dump book data', file1, loc1, file2, loc2);

Set up & Tear Down Test Data The ut_setup and ut_teardown procedures manage

any data structures needed to run your tests– These programs are run automatically before and after

tests procedures are executed.

PROCEDURE ut_teardown ISBEGIN BEGIN EXECUTE IMMEDIATE 'DROP TABLE ut_employee'; EXCEPTION WHEN OTHERS THEN NULL; END;

BEGIN EXECUTE IMMEDIATE 'DROP TABLE ut_DEL1'; EXCEPTION WHEN OTHERS THEN NULL; END; ...

This is an example of a portion of the

tear-down used to test a table encapsulation

package

Run Your Test Once you have built your test package, you can

simply "hand it over" to utPLSQL for testing with a simple one-line call:

You can also construct test "suites" of multiple packages to test and then run that suite:

SQL> exec utPLSQL.test ('betwnstr');

SQL> exec utPLSQL.testSuite ('PLVision');

See Your Results Instantly You are notified of success or failure

– When there is a failure, you see the descriptions of failed test cases, plus information about the cause of the failure

SQL> exec utplsql.test ('betwnstr').> FFFFFFF AA III L U U RRRRR EEEEEEE> F A A I L U U R R E> F A A I L U U R R E> F A A I L U U R R E> FFFF A A I L U U RRRRRR EEEE> F AAAAAAAA I L U U R R E> F A A I L U U R R E> F A A I L U U R R E> F A A III LLLLLLL UUU R R EEEEEEE. FAILURE: "betwnstr".UT_BETWNSTR: Typical valid usage; expected "cde", got "cd"UT_BETWNSTR: IS NULL: NULL startUT_BETWNSTR: IS NULL: End smaller than start

Change Your Testing Ways utPLSQL can make a dramatic difference in your ability

to test and your confidence in the resulting code

With utPLSQL, you build a comprehensive "library" of unit tests as you build your application– These tests and all their test cases can be passed on to other

developers– Anyone can now enhance or maintain the code with

confidence. Make your changes and run the tests. If you get a green light, you're OK!

Challenges to Using utPLSQL Build the test packages

– This can be almost as complicated as writing your application code.

Set up/change the test process in your group– It can be very difficult to move from ad-hoc testing to formal

testing. Powerful, well-designed GUI interfaces can make a big

difference.– Quest is currently exploring how to support utPLSQL in its

development tools.

utplsql: unit testing for oracle pl/sql

Technology