Skip to content

PL/SQL Unit Tests

Unit Tests framework provides the ability to test PL/SQL functions and procedures, and monitoring the results of such tests over time. Developers create unit tests by providing information about what is to be tested and what result is expected.

Create Unit Test File

Open the plsql/plsvc file in Developer Studio and choose 'Create PL/SQL test file' RMB menu option in the editor. This creates a connected pltst file and opens it for editing. A pltst file can contain multiple unit tests.

Write Unit Test

IntelliSense (Ctrl+Space in the editor) can be used to insert unit test skeleton for a given function/procedure.

The text 'should... when...' within the header of the generated skeleton should be replaced with a short description of the unit test. The signature of the function/procedure for which the unit test is being written should be left unchanged.

The behavior of function/procedure, for which the unit test is being written, alone should be tested. Any external objects (e.g. tables, methods) the function/procedure uses should be mocked within the USING section of the unit test.

Each unit test is run by passing a set of test data and ASSERTing the test outcome against the expected result. This is done in the BEGIN section of the unit test. The test data can be passed to the function/procedure using an optional FOR LOOP.

Mock Annotations

Listed below are the supported annotations which are used to mock external objects (e.g. tables, methods).

  • @Mock: Used to mock a function/procedure definition from connected plsql/plsvc
  • @MockPackage: Used to mock a function/procedure definition of a specific package
  • @MockPassThrough: Used to make direct API calls without having to mock them
  • @MockTable: Used to mock a database table/view
  • @MockCursor: Used to mock an external cursor declaration

Run Unit Tests

The unit tests written in a pltst file can be run using 'Run Test' RMB menu option in the editor. Unit test passes if the boolean expression in the ASSERT statement of the BEGIN section evaluates to TRUE, else the test fails.