Minimize Risk by Simplifying and Automating Oracle Code Testing

As I work with database management and application development pros at all types and sizes of organizations, I’ve been looking for trends in database code testing – unit testing of PL/SQL functions and procedures, for example.  It interests me, as a former DBA and developer.  A large majority of organizations seem to be focused on routinely testing their application (Java, whatever) code before it goes up the environment chain to production.  Database code isn’t being tested so much. 

Why should database developers worry about testing their code in some automated way?  Well, if Java code is being tested several times a day for new releases (true for many Agile organizations) – new features that could be going into customer-facing applications, for example – and they can’t be released because they have to wait on database code that’s only being approved for release once a week, that’s a problem.  It’s just not sustainable in a lot of organizations anymore.  Agile methodology and DevOps processes are about speed, automation, and accuracy.  

Functional unit tests help ensure accurate code, and automating those functional unit tests is important for test speed and repeatability.  Good quality code delivered quickly means better applications our customers will use – so maybe they’ll keep coming back.   

Quest has a solution for speeding up database code testing. It’s called Code Tester, it’s integrated nicely with Toad for Oracle Developer Edition, and it’s very powerful.  Code Tester is installed along with the rest of Toad for Oracle Developer Edition.   Check out this short video that describes the benefits.

Here’s a blog post by my colleague Denis O’Sullivan showing how to set up and run unit tests against PL/SQL code.

Ok, so we see that functional unit tests can be built in Toad Code Tester (part of the Toad Developer Edition) and saved for reuse whenever needed.  How about automation?  Say you have several PL/SQL functions that are under development.  You want to test the application functionality every time any of that code is changed even slightly.  

How do you automate testing of database code like that?  With Toad Code Tester and Toad Automation, follow these steps to make it happen:

1. Create the unit tests.  
   1. Open a function in the Toad editor and then click the button to execute it with debugger (reference figure 1 below).
   2. While executing the function with debugger, provide input parameter values.  When the resulting output values are correct, save the scenario as a “unit test” (figure 2).
   3. Build a Toad Automation Job.

One of the types of actions you can add to automation is Code Test, so you can automate running all the unit tests for regression testing our PL/SQL routines (figure 3).  But you’ll want to automate this, so make the Code Test step conditional – so it runs only if some condition is met. In our example here, the condition will be that something has changed in the structure of the functions I’m working on (figure 4).

Let’s take a tour through Toad screens that illustrate those steps:

Figure 1:  Execute the function with debugger

Figure 2: Use input values and resulting output value(s) from the execution’s PL/SQL Results tab to create a unit test

Figure 3: Building a Code Test automation step in a job. I clicked into Toad Automation and then the “Add a new job” button.  The first thing I’m adding is the Code Tester action; I can layer on other actions, even if-then-else logic, later.

Figure 4: Build out the Toad Automation job with steps to conditionally test our code. 

Our Automation job will do several things:

• Export the DDL of our functions on our source (Development) schema – the ones I’m working on during the day.
• Export the DDL of our functions on our target schema – let’s call it our QA environment – on its way to production.
• Perform logic that’s built in (Toad Automation IF-Then-Else) that compares the two DDL files from the first two steps.   The “file compare” action in the Automation wizard provides a setting to issue a “false” condition if there are differences.    That’s good, because if there are differences in our functions (meaning someone changed code in Development), we want to run our regression tests!
• Sure enough, the “Else” – the ‘false’ condition – fires off the Code Tester unit tests that have been designed in Toad as I worked on the PL/SQL in the first place.
• If the Code Tester unit tests fail – if any of them fail – I want the entire job to fail.  That’s what’s happened in the screen capture above (figure 4).   Red highlighted text makes it clear where things went wrong in the job.       

Figure 5:  Above, we had no differences this time between the functions (DDL) on the two schemas.

Nothing in the code changed, so skip the unit tests in the automation job. In Toad, keep in mind, you can get very specific about which objects you want to export DDL for.  I chose a group of functions that I know are under development during this particular Sprint.

To compare the functions across two schemas or databases, I first need to export their DDL.  One way to do that is with the “Export DDL” action in Toad Automation:

Figure 6:  Above, I’m exporting the functions from the first schema. Later, I’ll drag that “Export” action up to the beginning of the Action tree so that it executes first.  I’ll do the same for the second schema of the comparison – export the DDL for the selected functions, perform it at the start of our Action tree.

Watch for other blog posts soon that will cover other aspects of functional unit tests:

1. Checking them into source control along with the PL/SQL code the tests pertain to
2. Fitting them into DevOps processes (continuous integration)
3. Creating more advanced code tests

To find out more, explore the Toad World Toad for Oracle page here to discover more information about Toad for Oracle, and its features like Code Tester. Use the ‘Search’ field at the top of the screen to zero in on the information you are seeking.