< Previous Section: utAssert Package | Next Section: utOutput Package >

utGen Package

This package contains the following procedures and functions:

utGen.testpkg (basic version)	Generate skeleton test packages
utGen.testpkg (grid version) utGen.testpkg_from_file utGen.testpkg_from_string	Generate skeleton test packages with test cases
utGen.pkgstring	Get skeleton as a string
utGen.countRows utGen.firstRow utGen.firstBodyRow utGen.atFirstRow utGen.lastRow utGen.atLastRow utGen.setRow utGen.getRow utGen.nextRow utGen.prevRow utGen.showRows utGen.nthRow	Get rows from generated skeleton test package as array

Generate Skeleton Test Packages

The utGen contains a procedure that allows you to generate a starting point for a unit test package. This package can be sent to the screen, a file, a delimited string or an array (best for interfacing with a front end). You can generate a stand-alone test package or code "fragments" to be placed inside an existing source package.

We strongly recommend that you use utGen.testpkg as a starting point for all of your utPLSQL unit test construction. By taking this approach, you will most easily (and transparently) conform to the most up to date guidelines for utPLSQL test packages.

Note: While utGen.testpkg goes as far as possible to generate sensible unit test code, you will need to edit this code before you can compile and use it.

Here is the header of the testpkg procedure:

   PROCEDURE utGen.testpkg (
      package_in IN VARCHAR2,
      program_in IN VARCHAR2 := '%',
      samepackage_in IN BOOLEAN := FALSE,
      prefix_in IN VARCHAR2 := NULL,
      schema_in IN VARCHAR2 := NULL,
      output_type_in IN PLS_INTEGER := c_screen,
      dir_in IN VARCHAR2 := NULL,
      delim_in IN VARCHAR2 := c_delim
   );

And here is a description of the parameters:

Parameter Name	Usage
package_in	The name of the package or stand-alone program for which a test package is to be generated.
program_in	The filter to be applied to the list of programs for which unit test procedures will be generated. So if you only wanted to generate unit tests for programs that start with "UPD", you would pass 'UPD%' for this argument.
samepackage_in	TRUE if you plan to insert the generated code into the source package, FALSE if you want a stand-alone test package.
prefix_in	The prefix to be used for the test package and/or unit test procedures. See section " Organizing Your Test Code" for details.
schema_in	The schema that owns the package or program specified by package_in. The default is the currently connected schema.
output_type_in	The type of output that will receive the generated code. Valid options are defined as packaged constants: `utGen.c_file` `utGen.c_screen` `utGen.c_string` `utGen.c_array` The following sections explain the way you would work with these constants and the resulting generated code.
dir_in	The location of the file containing the generated code. Used only if you specify utGen.c_file for the output type.
delim_in	The delimiter used to separate lines of generated code. Used only if you specify utGen.c_string for the output type.

Before you use utGen.testpkg, you should make a few decisions about your generated code:

Do you want the generated test code to be a stand-alone package or to be inserted into an existing package? The default is stand-alone. Pass TRUE for samepackage_in if you want to generate code that can be easily cut and pasted into your source package. Note that utGen.testpkg does not actually modify your source package.
Do you want to generate a unit test program for every procedure and function in your package? If so, then go with the default for program_in. If, on the other hand, your package has one hundred programs and you only want to test, say, only those programs that perform updates, you might want to pass a non-trivial filter, such as "UPD%".
Where do you want to send your output? You can display to the screen, which can then be grabbed and put into a file (or in SQL*Plus spool it directly to a file). You can send the code to a file. You can deposit the code in a delimited string and then parse it within your own environment. Finally -- and of most relevance if you are building a GUI interface to utPLSQL -- you can generate to an internal array and then retrieve individual rows of the arrays through the utGen API.

Here are some examples of using utGen.testpkg:

Generate to the screen a stand-alone test package for the STR package:

SQL> exec utGen.testpkg ('str')

Generate to the screen unit test code to be embedded inside the STR package:

SQL> exec utGen.testpkg ('str', samepackage_in => TRUE)

Generate to the screen unit test code for all programs whose names contain "STR" to be embedded inside the STR package,.

SQL> exec utGen.testpkg ('str', '%STR%', samepackage_in => TRUE)

Now let's explore how to direct the generated code to different types of output.

Generating to Screen

The default behavior of utGen.testpkg is to generate code to your screen (via DBMS_OUTPUT.PUT_LINE). So unless you specify some other value for output_type_in, the code will be displayed on your screen or within a window of your PL/SQL IDE (such as TOAD or SQL*Programmer and so on). You can then transfer that content to a file, or move it to another window for immediate editing and compilation.

Here is an example of using utGen.testpkg, while also spooling to a file:

SQL> set serveroutput on size 1000000
SQL> spool str.pkg
SQL> exec utgen.testpkg ('str')

...out comes the code...

SQL> spool off

If DBMS_OUTPUT is not enabled in your session, then utGen.testpkg will not generate any output.

Generating to File

If you are working with utGen in a command line style (ie, you are not using a utGen-enabled GUI), then you will probably find it most useful to generate testing code directly to file. You do this by specifying utGen.c_file for the output type. You must also specify the directory in which you want the files (one for the package specification and another for the body)created.

Here's a generation request that creates two files named ut_str.pks and ut_str.pkb in the /newcode directory:

SQL> exec utgen.testpkg ('str', output_type_in => utGen.c_file, dir_in => '/newcode')

Notes on generating to file:

You do not have to specify a directory if you have previously (in your current session) called utConfig.setdir to set the default directory for all file-related utPLSQL operations. The following two lines of code are, in other words, equivalent to the single line shown above:

SQL> exec utconfig.setdir ('/newcode')
SQL> exec utgen.testpkg ('str', output_type_in => utGen.c_file);

You set up the UTL_FILE package (add at least one utl_file_dir entry in your database parameter initialization file) and make sure your directory is accessible through UTL_FILE, before this operation can succeed.

Generating to String

If you are accessing utPLSQL functionality through a GUI, you might find it more useful to direct output to a string (or array, see next section). You probably don't want to hassle with UTL_FILE (server-based file IO) and grabbing information from DBMS_OUTPUT.PUT_LINE is just a general hassle.

If you generate to a string, you can then retrieve that string value into a local variable and then parse it for display and manipulation. Here is an example of redirection to string:

BEGIN
   utgen.testpkg ('str', output_type_in => utGen.c_string);
END;

The generated code is composed of multiple lines of information, so they need to be separated by a delimiter. The default delimiter is the vertical bar, '|'. You can override that and provide your own delimiter. In the following example, I have decided to use the carriage return character as my delimiter:

BEGIN
   utgen.testpkg (
      'str',
      output_type_in => utGen.c_string,
      delim_in => CHR(10));
END;

Great, so the code has been put in a string. How do you get all that generated code? Call the utGen.pkgstring function:

FUNCTION utGen.pkgString RETURN VARCHAR2;

Generating to Array

If you are accessing utPLSQL functionality through a GUI, you might find it more useful to direct output to an array. You probably don't want to hassle with UTL_FILE (server-based file IO) and grabbing information from DBMS_OUTPUT.PUT_LINE is just a general hassle.

If you generate to an array, you can then retrieve the individual lines of code in the array through an API provided by utGen (the array itself is "hidden"). Here is an example of redirection to the utGen array:

BEGIN
   utgen.testpkg ('str', output_type_in => utGen.c_array);
END;

Great, so the code has been put in an array. How do you get all that generated code? Take advantage of the utGen API to retrieve individual rows in the array, which offers these features:

Get the number of rows currently in the array:

   FUNCTION utGen.countRows RETURN PLS_INTEGER;

Get the absolute index of the first row in the array:

   FUNCTION utGen.firstRow RETURN PLS_INTEGER;

Get the absolute index of the last row in the array:

   FUNCTION utGen.lastRow RETURN PLS_INTEGER;

The API offers a set of programs to iterate through the array, by maintaining a "current row" inside the package. You can:

Find out if you are positioned at the first row in the set:

   FUNCTION utGen.atFirstRow RETURN BOOLEAN;

Find out if you are positioned at the last row in the set:

   FUNCTION utGen.atLastRow RETURN BOOLEAN;

Find the first relative row containing the start of the package body definition. This is handy when you want to put the code for the specification and body in separate windows and/or files:

   FUNCTION utGen.firstBodyRow RETURN PLS_INTEGER;

Retrieve the text in the Nth row of the array. This gives you "random access" to the contents of the array. You can even specify a negative direction to get the Nth row from the end of the array.

   FUNCTION utGen.nthRow (nth IN PLS_INTEGER, direction utGen.IN SIGNTYPE := 1) RETURN codeline_t;

Set the pointer in the array to the specified row number. This allows you then move either forward or backward from that row in the array (using nextRow and prevRow, respectively):

   PROCEDURE utGen.setRow (nth IN PLS_INTEGER);

Retrieve the line of code stored in the current row in the array (set via setRow, nextRow or prevRow):

   FUNCTION utGen.getRow RETURN codeline_t;

Go to the next row in the array:

   PROCEDURE utGen.nextRow;

Go to the previous row in the array:

   PROCEDURE utGen.prevRow;

Show the contents of the array using DBMS_OUTPUT.PUT_LINE:

   PROCEDURE utGen.showRows (
      startRow IN PLS_INTEGER := NULL,
      endRow IN PLS_INTEGER := NULL);

Here is the code I would write in PL/SQL using this API to display the contents of the array (actually, it is the implementation of showRows):

   PROCEDURE showrows (
      startrow IN PLS_INTEGER := NULL,
      endrow IN PLS_INTEGER := NULL
   )
   IS
      v_start PLS_INTEGER
                    := NVL (startrow, 1);
      v_end PLS_INTEGER
         := NVL (endrow, utGen.countRows);
   BEGIN
      FOR indx IN 1 .. utGen.countRows
      LOOP
         DBMS_OUTPUT.put_line (utGen.getRow (indx));
      END LOOP;
   END;

Here is the code I would write to separate out the contents of the specification from the body:

   PROCEDURE showrows (
      startrow IN PLS_INTEGER := NULL,
      endrow IN PLS_INTEGER := NULL
   )
   IS
      v_start PLS_INTEGER
                    := NVL (startrow, 1);
      v_end PLS_INTEGER
         := NVL (endrow, utGen.countRows);
   BEGIN
      FOR indx IN 1 .. utGen.countRows
      LOOP
         IF indx = utGen.firstBodyRow
         THEN
            -- switch to Body window or file
         END IF;
         write_to_target (utGen.getRow (indx));
      END LOOP;
   END;

Generating Test Packages with Test Cases

The procedures to generate test packages with test cases are similar to testpkg above, but with a number of extra parameters:

 PROCEDURE testpkg (
      package_in           IN   VARCHAR2,
      grid_in              IN   grid_tt,
      program_in           IN   VARCHAR2 := '%',
      samepackage_in       IN   BOOLEAN := FALSE,
      prefix_in            IN   VARCHAR2 := NULL,
      schema_in            IN   VARCHAR2 := NULL,
      output_type_in       IN   PLS_INTEGER := c_screen,
      dir_in               IN   VARCHAR2 := NULL,
      delim_in             IN   VARCHAR2 := c_delim,
      date_format_in       IN   VARCHAR2 := 'MM/DD/YYYY',
      only_if_in_grid_in   IN   BOOLEAN := FALSE
   );

   PROCEDURE testpkg_from_file (
      package_in           IN   VARCHAR2,
      gridfile_loc_in      IN   VARCHAR2,
      gridfile_in          IN   VARCHAR2,
      program_in           IN   VARCHAR2 := '%',
      samepackage_in       IN   BOOLEAN := FALSE,
      prefix_in            IN   VARCHAR2 := NULL,
      schema_in            IN   VARCHAR2 := NULL,
      output_type_in       IN   PLS_INTEGER := c_screen,
      dir_in               IN   VARCHAR2 := NULL,
      field_delim_in       IN   VARCHAR2 := '|',
      arg_delim_in         IN   VARCHAR2 := c_delim,
      date_format_in       IN   VARCHAR2 := 'MM/DD/YYYY',
      only_if_in_grid_in   IN   BOOLEAN := FALSE
   );

   PROCEDURE testpkg_from_string (
      package_in           IN   VARCHAR2,
      grid_in              IN   VARCHAR2,
      program_in           IN   VARCHAR2 := '%',
      samepackage_in       IN   BOOLEAN := FALSE,
      prefix_in            IN   VARCHAR2 := NULL,
      schema_in            IN   VARCHAR2 := NULL,
      output_type_in       IN   PLS_INTEGER := c_screen,
      dir_in               IN   VARCHAR2 := NULL,
      line_delim_in        IN   VARCHAR := CHR (10),
      field_delim_in       IN   VARCHAR2 := '|',
      arg_delim_in         IN   VARCHAR2 := c_delim,
      date_format_in       IN   VARCHAR2 := 'MM/DD/YYYY',
      only_if_in_grid_in   IN   BOOLEAN := FALSE
   );

In each case, the idea is the same. We have to provide not only the arguments supplied to the basic version of testpkg, but also details of each of the test cases in a grid. In the first case, this is as a PL/SQL table, in the second this is as a file and in the final case, this is as a string.

The PL/SQL table passed to testpkg is defined as follows:

 
TYPE grid_rt IS RECORD (
      progname                      VARCHAR2 (100),
      overload                      PLS_INTEGER,
      tcname                        VARCHAR2 (100),
      message                       VARCHAR2 (2000),
      arglist                       VARCHAR2 (2000),
      return_value                  VARCHAR2 (2000),
      assertion_type                VARCHAR2 (100));

   TYPE grid_tt IS TABLE OF grid_rt
      INDEX BY BINARY_INTEGER;

Where the definitions of the fields are as follows:

progname - This is the name of the subprogram to be tested.
overload - This is the version of the subprogram where overladed versions exist. (You may have to look in the data dictionary to work this out).
tcname - The name of the test case.
message - The message to be used in the assertion code.
arglist - The list of arguments to be passed to the subprogram.
return_value - The return value to be checked against.
assertion_type - The type of assertion to be used. Currently this is ignored unless it contains 'EQ' or 'ISNULL'

In testpkg_from_file and testpkg_from_string, exactly the same fields need to be passed (and in the same order). These fields are separated by the character given by the field_delim_in parameter which defaults to '|', the pipe symbol. In the case of testpkg_from_string, we can also specify the line delimiter in the line_delim_in parameter, which defaults to an ASCII linefeed character.

In all cases, the arguments specified in the arglist field are separated by yet another delimiter, which is passed in the arg_delim_in parameter ( or just delim_in in the case of testpkg). This defaults to a semicolon.

The remaining arguments passed to these routines are date_format_in and only_if_in_grid_in. The former gives the date format used in dates passed through the arglist and return_values fields. The latter specifies if tests should only be generated for subprograms listed in the grid or not.

An Example

All of this is probably best explained with an example. Suppose I have a package defined as:

CREATE OR REPLACE PACKAGE lottery AS
  FUNCTION Draw (seed_in NUMBER := NULL, when_in DATE := NULL) RETURN VARCHAR2;
END;

This returns a string describing a lottery draw, given a seed and a date. I want to test the following conditions: (It doesn't make much sense, but hey, it's only an example)

Passing both parameters as NULL, we should get back '01 02 03 04 05 06'.
Passing in 7 for the seed and 1 January 2001 for the date, we should get back '23 24 27 37 39 48'.
Passing in 0 for the seed and today's date, we should get back NULL

So to generate the skeleton I require I could run the following through SQL*Plus:

set serveroutput on size 1000000
declare
  a_grid utgen.grid_tt;
begin
  
  a_grid(0).progname := 'Draw';
  a_grid(0).tcname := 'Test Case 1';
  a_grid(0).message := 'The First Test';
  a_grid(0).return_value := '01 02 03 04 05 06';
  a_grid(0).assertion_type := 'EQ';
  a_grid(1).progname := 'Draw';
  a_grid(1).tcname := 'Test Case 2';
  a_grid(1).message := 'The Second Test';
  a_grid(1).arglist := '7;2001-01-01';
  a_grid(1).return_value := '23 24 27 37 39 48';
  a_grid(1).assertion_type := 'EQ';
  a_grid(2).progname := 'Draw';
  a_grid(2).tcname := 'Test Case 3';
  a_grid(2).message := 'The Third Test';
  a_grid(2).return_value := NULL;
  a_grid(2).arglist := '0;!SYSDATE';
  a_grid(2).assertion_type := 'ISNULL';
  
  utgen.testpkg(
    package_in => 'LOTTERY', 
    grid_in => a_grid, 
    date_format_in => 'YYYY-MM-DD');
end;
/

or the equivalent:

set serveroutput on size 1000000
begin
  utgen.testpkg_from_string (
      package_in => 'LOTTERY',
      grid_in =>
'Draw||Test Case 1|The First Test||01 02 03 04 05 06|EQ
Draw||Test Case 2|The Second Test|7;2001-01-01|23 24 27 37 39 48|EQ
Draw||Test Case 3|The Third Test|0;!SYSDATE||ISNULL',
      date_format_in => 'YYYY-MM-DD'
   );
end;
/

which generate the following for the body of ut_draw (tidied up a little for compactness):

PROCEDURE ut_DRAW
IS
   -- Verify and complete data types.
   against_this VARCHAR2(2000);
   check_this VARCHAR2(2000);
BEGIN
   
   -- Define "control" operation for "Test Case 1"
   against_this := '01 02 03 04 05 06';
    
   -- Execute test code for "Test Case 1"
   check_this := 
   LOTTERY.DRAW (SEED_IN => '', WHEN_IN => '');
    
   -- Assert success for "Test Case 1"
   -- Compare the two values.
   utAssert.eq ( 'The First Test', check_this, against_this);

   -- End of test for "Test Case 1"
   
   -- Define "control" operation for "Test Case 2"
   against_this := '23 24 27 37 39 48';
    
   -- Execute test code for "Test Case 2"
   check_this := LOTTERY.DRAW (SEED_IN => 7, WHEN_IN => TO_DATE ('2001-01-01', 'YYYY-MM-DD'));
    
   -- Assert success for "Test Case 2"
   -- Compare the two values.
   utAssert.eq ( 'The Second Test', check_this, against_this);

   -- End of test for "Test Case 2"
   
   -- Define "control" operation for "Test Case 3"
   against_this := NULL;
    
   -- Execute test code for "Test Case 3"
   check_this := 
   LOTTERY.DRAW (SEED_IN => 0, WHEN_IN => SYSDATE);
    
   -- Assert success for "Test Case 3"
   -- Check for NULL return value.
   utAssert.isNULL ( 'The Third Test', check_this);

   -- End of test for "Test Case 3"
   
END ut_DRAW;

Note that the different data types are handled automatically. So '2001-01-01' is converted to a date using TO_DATE and the specified date format. However, we wanted to enter SYSDATE for our argument in one of these cases. How do we stop this being converted into a date? The answer is that we need to prefix the value with a '!' (an exclamation mark). This causes utGen to pass this along 'as is' without attempting any conversion. Note that this cannot currently be overridden, so if your data starts with an exclamation mark, you'll have to work around this problem.

< Previous Section: utAssert Package | Next Section: utOutput Package >