Real Automated Code Testing for Oracle

Testing Object Types with Code Tester 1.9

QCTO Blog — Tue, 09 Mar 2010 08:00:00 GMT

by Steven Feuerstein

An object type is Oracle's version of an object-oriented class. It shares some of the features of a class, but falls a bit short. In Code Tester 1.9 and higher you can define and run tests for methods of an object type. You can also directly test the contents of an instance of an object type.

Click here to watch a video demonstrating the features described below.

Download Code Tester 1.9 here.

Set Value for Object Type IN Argument

Prior to Code Tester 1.9, the only way you could specify the value of an object type instance was to write initialization code to assign values to the attributes of that type.

Now, you can enter values for these attributes in the Attribute Grid of the Properties Window.

Here, for example, is the Properties Window of the "compare to" IN parameter for the ORDER function of the Quick Start object type, qctod#test_object_type:

If the attribute has a scalar datatype, you can enter the value directly in the grid. You can only specify single values (as literals, expressions or queries). Multiple values (random generation, from lists, etc.) are not allowed.

If an attribute of your object type is a complex type, then you will not be able to type a value in the grid. Instead, you will need to open the Properties Window for that type and then write code in the initialization section to populate this attribute.

How to Test Object Type Methods

To select an object type for testing, simply click on "New Test" on the toolbar or use the Dashboard menu. You will then see the "Choose Program to Test" list, which now includes types. Simply choose the type of interest and you will be taken to the Test Builder, at which time you then select the method that you want to test.

You can define several different types of methods in an object type: constructor, static and member methods. You can also test the contents of instances of an object type. You will find links below to details on how to test each of these types.

You can tell the type of your method in the combo list at the top of Test Builder:

Test Constructors

A constructor is a function whose name matches the type itself and is used to construct a new instance of that type. Oracle provides a default constructor for each type. You must, however, provide a value for each attribute when you call the constructor. As there are no private attributes in an Oracle object type, this means that a programmer will have to be aware of and provide sensible values for all attributes.

Often, this is very much not how an instance should be initialized, so you can define your own constructor in which you specify precisely the input values desired. A constructor must be of the form:

CONSTRUCTOR FUNCTION object_type_name (
   SELF IN OUT NOCOPY object_type_name (your_parameter_list)
   RETURN SELF AS RESULT

To test a constructor, you choose that method from the program list at the top of Test Builder. The Input Grid will contain a row for every parameter in your function's parameter list. The Outcome Grid will contain the following:

A row for the object type instance returned by the function. How to Test Object Type Instances explains how you can complete the definition of this outcome.
A row for each attribute of the object type. The outcome that you can defined for each attribute is determined by its datatype.

Generally, you will not want to set up an outcome for both the instance returned by the function and its attributes. Simply remove those outcomes that do not apply for your particular test.

Here, for example, is the default outcome grid for the constructor function of the Quick Start object type, qctod#test_object_type:

Test Static Methods

A static method is a function or method defined in an object type that is not connected to an instance of that object type. Every IN and IN OUT parameter will appear in the Input Grid. You can define one or more outcomes for the OUT and IN OUT parameters (and return value, if the static method is a function).

You will test static methods in exactly the same fashion as you would a procedure or function in a package.

Test Type Member Methods

A member method is a procedure or function that operates on a particular instance of an object type. It is executed in the form TYPE.METHOD, that is using dot notation.

A member method has associated with it a special parameter named SELF, which is the instance of the object type on which the method is invoked. For functions, SELF is by default IN only (the body of the function cannot change the values of an attribute of the instance). If a procedure, SELF is IN OUT by default, but that can be over-ridden as well (by explicitly specifying SELF as a parameter with the non-default parameter mode).

When you choose to test a member, each parameter of the member is displayed in the Input Grid, as occurs with a package-based subprogram.

If SELF is an IN or IN OUT parameter, each attribute of the object type is also displayed in the Input Grid, so that you can directly specify values for those attributes. Here, for example, is the Input Grid for the member procedure of the Quick Start object type, qctod#test_object_type:

Notice that each input's name is prefaced by "SELF" when it is the attribute of the instance.

If SELF is an OUT or IN OUT parameter, then Code Tester displays in the Outcome Grid a starting point of an outcome for each attribute of the object type. An outcome is also provided for the object type as a whole. You can remove any outcomes for which you do not need or want to provide a test. Here, for example, is the Input Grid for the member function, return_as_attr2_self_inout, of the Quick Start object type, qctod#test_object_type (it defines SELF as IN OUT):

Notice that each outcome's name is prefaced by "SELF" when it is the attribute of the instance.

Test MAP and ORDER Methods

MAP and ORDER methods are defined so that Oracle can perform native equality and inequality checks, as well as be able to compare two object types (as in, "Is object type 1 > object type 2?").

The MAP method returns a number. That number determines the relative "value" of the object type and is used in the comparisons.

The ORDER method returns one of three integer values: -1, 0 and 1. A 0 return value means that the two types are equal. Returning 1 means that the current (SELF) instance is greater than the instance passed as the ORDER method's only argument. Returning -1 indicates that the instance passed as the ORDER method's only argument is greater than the SELF instance.

To test a map method, you simply provide input values for each attribute of the type and then define the appropriate outcome on the number returned by the method.

To test an order method, Code Tester provides in the Input Grid one row for each of the attributes of the SELF instance, and one row for the ORDER's comparison object type.

Here, for example, is the default outcome grid for the ORDER member function of the Quick Start object type, qctod#test_object_type:

How to Test Object Type Instances

An instance of an object type is a variable (or, in the context of Code Tester, an expression) or a parameter whose datatype is the object type. You can test an object type instance by comparing one instance to another using an equality and inequality check. You can also provide values for each attribute and these will be compared individually.

Compare Object Type Instances

If the program you are testing returns an object type instance, or you define an Expression outcome and the type of the expression is an object type, then you will need to set up a test that compares the contents of an actual (changed by the program) object type instance and an expected instance.

Only two types of tests are defined for object type instances: equality and inequality, as you can see in this window from the Outcome Grid:

If the object type does not have a MAP or ORDER member defined in it, then Code Tester will test for equality by comparing each of the comparable attributes for equality. If the values of each of the attributes of the actual and expected instances are all the same (or both are NULL), then Code Tester records the test as a success. If the value of at least one attribute differs, the test has failed.

If the object type does have a MAP or ORDER member defined in it, then Code Tester will test for equality by calling that member function. In this case, success or failure is determined by the logic that the author of the instance has put into those functions.

Extending the Reach of Quest Code Tester for Oracle

The ultimate objective of Code Tester is to make it possible for PL/SQL developers to test their programs without having to write any code at all.

Version 1.9 takes Code Tester a big step closer to this objective, by making it possible to declaratively test the methods and attributes of an object type.

Code Coverage Analysis in Code Tester 1.9

QCTO Blog — Mon, 22 Feb 2010 07:00:00 GMT

Getting a happy green face after running your tests is, of course, very important. It is also important, however, to understand how much of the code in your program is being exercised by your tests. If, for example, you discovered that all your tests are successful, but only 10% of the lines of code in your program are actually run, you would have to conclude that either your tests are inadequate or you wrote a lot of code you don't need. Which of those two is more likely to be true?

The best way to gain a high level of confidence in your code is to complement your functional tests with code coverage analysis. Code Tester makes it easy for you to do that and even allows you to specify a target percentage of coverage against which your programs will be measured.

You can set your target or goal globally in the Preferences window:

You can also set different targets for specific program units. You might, for example, need a very high code coverage for certain key modules in your application.

Then in your test definition, you make sure that you have turned on the gathering of code coverage data:

Then when you run your test, Code Tester will automatically turn on the PL/SQL Profiler (DBMS_PROFILER) so that the raw data is generated by Oracle and then stored in Code Tester's repository.

After you have run your test, you can choose from a wide range of reports to view your code coverage information in detail:

It can't get much easier than this!

Watch a video demonstrating code coverage features in Code Tester 1.9.

Continuous Integration

QCTO Blog — Wed, 13 Jan 2010 08:00:00 GMT

Written by Finn Ellebaek Nielsen. A short introduction to Continuous Integration (CI) and a description of how to integrate testing Oracle code with QCTO in a CI environment.

How to reference IN argument values in outcomes

QCTO Blog — Mon, 21 Dec 2009 08:00:00 GMT

Sometimes you need to reference the value of an IN argument (an input, in Code Tester terminology) in your outcome. For example, under certain circumstances, the string returned by your function should be the same as the input value. Or perhaps the out value should be some part of the input value.

You could hard-code the same value you supplied in the Input Grid, but then if you change the input value, you have to change the outcome value as well. That's not a great solution.

Code Tester instead gives you two different ways to reference the input value indirectly:

Functions in the qu_result_xp package that accept the name of the argument and returns it value
Reference the name of the variable that Code Tester will declare and then assign the IN value to it before the program is run.

Let's take a look at both these techniques for a very simple example. Suppose I want to test this function:

FUNCTION first_three (string_in IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
   RETURN SUBSTR (string_in, 1, 3);
END;

I start up Test Builder and create a new test case. I enter "abcdefg" in the input grid for string. I will also open up the Properties Window for that input.

I see this:

Notice the variable name "i_STRING_IN." That is the name of the variable that will be used to store the input value and pass it to first_three. You can change this to anything you like, but the default is usually fine.

OK, now let's shift focus to the Outcome side of the test case. The function is supposed to return a string that is just a portion of the input value (first three characters). I could therefore create an outcome like this:

But this only proves that the correct value is returned for this one string ("abcdefg"). And, as noted above, if I ever want to change the input value, I must change the outcome value as well.

Instead of hard-coding the return value, I can open the Properties Window for the outcome's expected result:

and then in the "Expression of Query" field I can enter either of the following:

SUBSTR (i_String_in, 1, 3)

SUBSTR (qu_result_xp.argval_varchar2 ('STRING_IN'), 1, 3)

The first approach simply relies on the name of the input variable remaining "i_string_in." The second approach relies on the underlying API in the qu_result_xp package to look up the value of the IN argument with the specified name.

More generally, the API approach can be described as: when you want to get the value of an IN argument for a test case, you will call a program of this form:

qu_result_xp.argval_ < datatype > ('< argument_name >')

All of the following are currently supported:

FUNCTION argval_varchar2 (argument_name_in IN VARCHAR2) RETURN VARCHAR2;

FUNCTION argval_clob (argument_name_in IN VARCHAR2) RETURN CLOB;

FUNCTION argval_date (argument_name_in IN VARCHAR2) RETURN DATE;

FUNCTION argval_number (argument_name_in IN VARCHAR2) RETURN NUMBER;

FUNCTION argval_boolean (argument_name_in IN VARCHAR2) RETURN BOOLEAN;

FUNCTION argval_timestamp (argument_name_in IN VARCHAR2) RETURN TIMESTAMP;

FUNCTION argval_timestamp_tz (argument_name_in IN VARCHAR2)
   RETURN TIMESTAMP WITH TIME ZONE;

FUNCTION argval_timestamp_ltz (argument_name_in IN VARCHAR2)
   RETURN TIMESTAMP WITH LOCAL TIME ZONE;

FUNCTION argval_interval_ds (argument_name_in IN VARCHAR2)
   RETURN INTERVAL DAY TO SECOND;

When you pass the name of the argument, be sure to use a case that matches the actual argument name. In other words, unless the argument name is contained within double quotes, pass the argument name as UPPER CASE.

Another advantage of referencing the input value by name, rather than by the hard-coded value, is that you can use random values or lists of values to dynamically generate many different inputs (and therefore different test cases), all with the same/single outcome.

Consider the testing of first_three. So far I have built a single test case for a specific input value of "abcdefg". I am reassured that I get the right answer for that one value, but I'd feel lots more confident if I could "throw" lots of different strings at it and ensure that in all cases, the function returned the correct answer.

To do this, I can go back to the input for the test case and change the value from a single literal to a randomly generated list of, say, 100 strings with lengths varying from 10 to 32767:

And now every time I run my test, Code Tester will generate 100 new strings and run first_three for each. You see the partial results below:

I hope this gives you a clear understanding of how you can reference IN argument values by name, and make your test cases that much more flexible and useful.

Handling Complicated Tasks More Easily

QCTO Blog — Mon, 09 Nov 2009 08:00:00 GMT

Did you know that in Code Tester 1.8.5 you can now perform in-line validation of your customization code? For more complex tests, this will save you lots of time and make it easier to build your regression tests.

The ultimate objective of Code Tester is easy to state: allow developers to test arbitrarily complex PL/SQL programs without writing any test code. That turns out to be really hard to do, so as we get Code Tester closer and closer to that goal, we also offer the ability to fully customize every aspect of your tests.

One of the nicest new features in Code Tester 1.8.5 is the ability to perform "in-line" validation of customization code. Prior to 1.8.5, the only way to see if your custom code had compile errors was to generate the test package, which meant that you had to close the window in which you entered the code. If that test package had errors, you had to then circle back to that window - a cumbersome process that frustrated users.

There are two ways to determine if the custom code you entered has compilation errors:

Generate the test code (which will automatically happen the next time you try to run a test), which also attempts to compile the test package that will contain your test code.
Ask Code Tester to perform in-place validation (while the customization window is still open). The steps to request in-place validation vary depending on if you are in Test Builder or Test Editor.

Test Builder In-Place Validation

In Test Builder, when you have the customization window open for the test case, you can ask Code Tester to check for errors in three ways:

Press Ctl-Alt-V.
Select "Check for Errors" from the right click menu
Press the "Check for Errors" button.

All three options are shown below:

Test Editor In-Place Validation

When you press on the Customization tab at any level within the test definition, you can then edit custom code. Within this window, you can ask Code Tester to check for errors in three ways:

Press Ctl-Alt-V.
Select "Check for Errors" from the right click menu

Both these options are shown below:

When No Errors Are Found

If the custom code has no errors, then you will see this window:

When Errors Are Found

If your custom code contains one or more errors, Code Tester will open a window that contains all the customization code for the current element and also...

Custom code for elements above it in the test definition hierarchy.
If you ask to validate input or outcome custom code, custom code for all inputs.

If, for example, you request validation of test case custom code, you will also see in this window unit test custom code (if present). Code Tester does this because the test case custom code might reference custom code at that higher level.

All inputs are included because an outcome's customization code may also reference a local variable holding an input value.

You will see at the bottom of the window a grid of all errors reported from Oracle's USER_ERRORS data dictionary view. You can click on a line number in that grid and that line of code will be shown.

You cannot change any of the code in this window. Instead, you must close the window, change your code, and validate again.

Here is an example of the window:

Quest Code Tester 1.9 beta now available

QCTO Blog — Mon, 12 Oct 2009 07:00:00 GMT

In case you missed my announcement on the Code Tester community forum, we have now posted a beta version of 1.9.

The major focus for 1.9 to date has been to add support for automated testing of object type methods. This beta release offers the first glimpse of this functionality; you should now be able to define tests for constructors, static methods, and member procedures and functions.

You should also very much expect to encounter bugs, as this feature involves substantial changes to Test Builder, Test Editor and the test code generator.

So if you want to test object type methods, please install and try out this beta version.

DO NOT, HOWEVER, USE THIS VERSION IN PLACE OF YOUR PRODUCTION INSTALLATION OF CODE TESTER. IT IS NOT YET STABLE ENOUGH.

We have not yet updated the Help document for 1.9. You will find below some high very high level guidance on how to use these new features. We very much want and need your feedback on how we have implemented object type support. User feedback has already caused us to consider changes in the UI design.

Many thanks for your interest and support!

Steven Feuerstein

PRELIMINARY HELP FOR OBJECT TYPE SUPPORT

There are three main areas of enhancement:

Test methods of an object type
Specify values for attributes of object type parameters
Testing the contents of an object type

You will find brief descriptions of each of these areas below.

Test methods of an object type.

A method is a procedure or function defined in an object type specification. A method can be static, a constructor, or a member (which uses SELF either implicitly or explicitly in its parameter list). Then there are specialized members, such as MAP and ORDER.

When you ask to create a New Test, you will now see object types listed, in addition to packages, procedures and functions. When you pick an object type, all the methods of that type will appear in the drop down at the top of Test Builder. You then pick the method and start defining your tests.

In other words, it works just the same as testing subprograms of packages - but of course there are some differences.

If you are testing a member method, then all the attributes of the type are presented as inputs and possibly outcomes as well (if SELF is IN only, they will not be available as outcomes).

If you are testing a constructor, then we do not show you the attributes, only the parameters.

Specify values for attributes of object type parameters

Prior to 1.9, if you had a parameter or return of object type, then you could only provide initialization and cleanup code for the input, and then write your own custom code to test the contents of the type. No longer!

Now, if your input or outcome is an object type, you can open the Properties Window for that type and you will see a grid of attributes. You can then assign values to each attribute.

Here are the current limitations on this implementation (and they will remain in place unless we hear loud and widespread complaints):

You cannot specify multiple values (random value generation, lists, queries) for attributes.
If an attribute of the OT is itself a complex type (another OT, a nested table, a varray), then you can only provide initialization and cleanup code to populate that attribute. In other words, the grid of attributes is only supplied for the top level of attributes.

Testing the contents of an object type

You can now perform equality checks for an object type. That is, does the object type returned by the program match an expected object type?

If you have a MAP or ORDER method defined for the object type, this is *always* used to do the comparison.

If you do not have a MAP or ORDER method defined for the object type, Code Tester generates an equality comparison function that compares for equality each attribute whose type allows such a comparison. That is, nested object types, varrays, XMLtype attributes will all be ignored.

Two quick tips for getting more out of Code Tester

QCTO Blog — Fri, 09 Oct 2009 07:00:00 GMT

Written by Steven Feuerstein

Code Tester is a relatively young tool (first released for production use in February 2007), but it has matured quickly (hey, at least I think so!) and is packed full of handy features.

As with all other tools, however, it can sometimes be hard to find the "jewels," so I thought I would highlight a couple.

1. Dynamically generated test cases

Rather than provide single, literal values as inputs to a test case, you can specify sets of random values, lists of values, and values from queries. Why might you want to do this? Suppose you need to verify that if you pass a NULL to your first parameter, then no matter what you pass for the other argument values, the function should return NULL.

Well, proving anything based on statements like "no what matter you pass" is very difficult. You obviously can't test every single value, but you could tell Code Tester to randomly pass in 100 strings of varying lengths, 200 numbers randomly selected between 1 and 1,000,000 - and permutate all those values into 20,000 separate test cases.

Sure, it will take lots of time to run all these tests, so be careful when you start permutating test data groups. But your test coverage - and corresponding confidence in your software - does go way up.

To take advantage of these features, open the Properties Window in the input grid of Test Builder:

Then click over to the Random Values tab and have at it:

If you specify multiple input values for more than one parameter, be sure to choose the Permutate setting for the test case:

You will want to choose Correlation when you also specify multiple values for your outcomes and need to correlate those with specific combinations of input values. I will talk about that feature (which has to do with driving tests from tables) in a future blog post.

2. Generate Outcomes

Test Builder is designed primarily for a "top down" or "test driven" approach: you think about the kinds of scenarios you want to test and then define them through the UI, including the expected outcomes.

In many cases, however, we are going back and testing programs that are already in production and need some change. Or the value returned by the function is not easily typed in, such as a long string.

In such cases, it makes a lot more sense to run the program for the specific input values, take a look at the value returned by the program, and if it is right, save it as an outcome.

You can do precisely this (for scalar values like strings and dates) with the Generate Outcomes feature. This is available through the Outcome Grid in Test Builder:

The Generate Outcome wizard will then run your program, show you any values returned by the program (OUT and IN OUT arguments, as well as the RETURN value if a function). You then can decide if you like the results and save them as outcomes:

This wizard also includes some nice "bells and whistles". You can ask it to evaluate any expression you specify and then show you those results as well. Suppose, for example, your procedure modifies the value of a packaged variable (that's a hidden side effect you should avoid, but it happens a lot). Or you might want to apply a formula of some sort to your function return value (maybe you only want to verify the first three characters in the string returned).

No problem. Just add that expression in the wizard, and you can save that resulting value as an outcome as well:

And then you can save two outcomes for these inputs:

So many features, so little time

I hope you find these features of interest. There's a lot more packed into Code Tester. I will explore these goodies in the coming months.

In the meantime, if you have questions about Code Tester, feel free to contact me or, even better, since the Q&A can be shared, post a question at the Code Tester community discussion forum.

Happy testing!

Steven

Automated Testing at OPP2009

QCTO Blog — Tue, 15 Sep 2009 07:00:00 GMT

Written by Finn Ellebaek Nielsen. Learn about automated testing and much more at the OPP2009 conference November 10-11 in Atlanta, Georgia, USA.

Test Amount

QCTO Blog — Mon, 14 Sep 2009 07:00:00 GMT

Written by Finn Ellebaek Nielsen. Best practices for determining test amount required through analysis of product risk.

Test Approach

QCTO Blog — Tue, 28 Jul 2009 07:00:00 GMT

Written by Finn Ellebaek Nielsen. Best practices for a test approach that implements the strategy and test policies for Oracle code laid out in a previous blog post.

Test Strategy and Policies

QCTO Blog — Sun, 05 Jul 2009 01:18:35 GMT

Written by Finn Ellebaek Nielsen. Best practices for establishing a test strategy, as well as test policies for Oracle code.

Quest Code Tester for Oracle 1.8.3 Now Available

QCTO Blog — Wed, 10 Jun 2009 01:26:22 GMT

Written by Steven Feuerstein. Version 1.8.3 of Quest Code Tester is now available.

Reasons for Testing Oracle Code in the Database

QCTO Blog — Sun, 17 May 2009 21:15:51 GMT

Written by Finn Ellebaek Nielsen. Reasons for testing Oracle code in the database.

Code Tester Blog - introduction

QCTO Blog — Sun, 10 May 2009 15:00:04 GMT

Written by Steven Feuerstein. Introduction to the "Real Automated Code Testing for Oracle" blog.