Oct 19, 2017 2:05:00 PM by Tim Fritz
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:
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:
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:
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.
Written by Tim Fritz
Quest Software solution architect - database performance management and administration solutions. Former DB2 developer and DBA. IBM Certified Database Administrator – DB2 LUW; Microsoft MCP, Certified Technical Specialist – SQL Server.