Blogger List

Steven Feuerstein Indicates Oracle ACE director status
PL/SQL Obsession

Guy Harrison Indicates Oracle ACE status
Database topics

Bert Scalzo Indicates Oracle ACE status
Toad for Oracle, Data Modeling, Benchmarking
Dan Hotka Indicates Oracle ACE director status
SQL Tuning & PL/SQL Tips

Valentin Baev
It's all about Toad

Ben Boise
Toad SC Discussions

Dan Clamage

Kevin Dalton
Benchmark Factory

Peter Evans 
Business Intelligence, Data Integration, Cloud and Big Data

Vaclav Frolik  
Toad Data Modeler, Toad Extension for Eclipse

Devin Gallagher
Toad SC discussions

Stuart Hodgins
JProbe Discussions

Julie Hyman
Toad for Data Analysts

  Henrik "Mauritz" Johnson
Toad Tips & Tricks on the "other" Toads
  Mark Kurtz
Toad SC discussions
Daniel Norwood
Tips & Tricks on Toad Solutions
Amit Parikh
Toad for Oracle, Benchmark Factory,Quest Backup Reporter
Debbie Peabody
Toad Data Point
Gary Piper
Toad Reports Manager
John Pocknell
Toad Solutions
Jeff Podlasek
Toad for DB2
Kuljit Sangha
Toad SC discussions
Michael Sass 
Toad for DB2
Brad Wulf
Toad SC discussions
Richard To
SQL Optimization
  Toad Data Modeler Opens in a new window
Data Modeling
  Toad Higher Education
How Hi-Ed Uses Toad
  Real Automated Code Testing for Oracle
Quest Code Tester blog


Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies.  Have some views of your own to share?  Post your comments!  Note:  Comments are restricted to registered Toad World users.

Do you have a topic that you'd like discussed?  We'd love to hear from you.  Send us your idea for a blog topic.

Jul 28

Written by: QCTO Blog
Tuesday, July 28, 2009  RssIcon

Written by Finn Ellebaek Nielsen

In my previous blog post I covered test policy and strategy. In this blog post I'll dig a little deeper and elaborate on best practices for the test approach.

Test Approach

The test approach is the implementation details of the test policy and strategy for a particular project.

My suggestions for a test approach are the following:
  • Quest Code Tester for Oracle is used for testing the Oracle code. 
  • Change handling:
    • No changes should be applied to the code without a set of automated test cases covering the area to be changed, serving as a safety net. The tests can be placed at various levels (eg unit and integration) and these need to be in place in order to ensure that no unintended and unwanted behavioral change is introduced. They should be run before and after any change and it should be verified that they are all successful (status "green").
    • For various reasons you may find that the code you need to change is not testable so you cannot put test cases around it without modifying it. This is also referred to as the "legacy code dilemma". A solution to this dilemma would be to introduce tests at a higher level (eg integration) before changing the code to be testable, verify the new tests, and then introduce unit tests and verify the integration tests.
    • If the change is necessary in order to fix a defect, then a test case that reproduces the defect should be added to the unit/integration test and executed/verified before the change is done. This ensures that the same defect isn't reintroduced in the future. 
  • Realistic standards for the following should be established (I will cover this in further detail in a future blog post):
    • Code Coverage (CC). The percentage of number of statements executed through the test, out of the total number of statements in the program/subprogram. It doesn't make sense to opt for 100% CC in the units that will be tested automatically as that is often not realistic due to the sheer volume of permutations of input parameter values and test data.
    • Boundary tests based on parameter datatype. Some of these can be generated automatically by Quest Code Tester for Oracle. 
  • A library of reusable components that handle test data setup should be established and shared as widely as possible on the development team. Data setup is one of the biggest challenges in database program testing. 
  • Informal code review should be carried out on a regular basis, eg just before committing a change to the source code version control system. This will help identify defects and also help ensure that the corporate style guide and code conventions are followed (in case this cannot be automated though development tools). 
  • Database environments:
    • Development and unit testing: One for each developer. This can either be server-based or running on the developer's own PC. Cheap Oracle desktop license options are:
      • Oracle Database 10g Express Edition (XE). Free. Running Windows and Linux only, doesn't support Java stored procedures (including some of the UTL% and DBMS% Oracle packages) and is "only" 10g. You can only have one instance per machine and there are limitations on the amount of memory that can be accessed (1GB) and also on the size of your database instance (4GB). Furthermore, it can use one CPU only.
      • Oracle Database x.y Personal Edition (PE). Cheap (about USD 460). Running Windows only. Includes all the Enterprise Edition options apart from Real Application Cluster.
      • Oracle Database x.y Standard Edition One (SE1). Cheap (about USD 900). Running on all the supported Oracle platforms. Includes no Enterprise Edition options and is only supported on machines with at most two CPU sockets.
    • Test (integration test): Continuous Integration, which is described in a separate section later in this post.
    • System test (user acceptance test).
    • Production: Are you deploying some of your tests to the production environment? Why not? The future at Microsoft seems to be heading that way as they have realized that modern software is very complicated and it can be difficult to reproduce environment-specific defects. This subject is "religious" and people seem to have strong opinions about the right and wrong. In my view there's nothing wrong with having some kinds of tests in production, such as smoke tests to verify that a production deployment was successful.  Obviously, it should be ensured that they cannot modify production data. 
  • The automated unit tests should run in just a few minutes per unit. If they take too long time to run they will not be run frequently enough. Some test experts in other programming languages such as Java and C# claim that all the tests (at every level) must execute in as little as 10 minutes or even 10 seconds. In my view the latter is not realistic, especially with an Oracle database. Integration and regression tests may take longer to run, which is not a problem as they're likely to be run over night. 
  • The test cases are not static and will need continuous changes/enhancements.

Database Environments

You may wonder why I suggest that each developer has their own database. An Oracle database is all about collaboration, isn't it? That's true, but this is not necessarily a good idea. Consider this situation: Your application has a central package that most other PL/SQL and SQL code depend on. Now you need to make a change to it, which presents you with the following challenges:

  • You cannot compile it if some code using the package is currently running in another session as it will be locked. 
  • You need to make sure that you implement the change correctly:
    • If you get the syntax wrong you have just severely affected all the dependent code such that it no longer compiles (if the package specification has compilation errors) or runs (if the package body has compilation errors).
    • If you get the semantics wrong it will take you a while to run your unit tests against it and find and fix the error. In the meantime, all your fellow team members are ripping their hair out in frustration because they think that they have a defect in the code they're currently working on. 
  • Two developers need to make changes to different subprograms of the package. This cannot be achieved in the same schema of the same instance and if they need to share the Code Tester repository in the same schema of the same instance Test Builder/Test Editor locks the entire test definition when the first developer enters it, blocking out the other developer. 
  • It can be difficult to control and cater for synonyms, sequences, PL/SQL and views in automated testing as these are shared database objects (instead of session-based). When you have your own instance you can control them 100%. Future blog post(s) will describe how this can benefit you.

DBA Privileges

The following limitations may also slow your progress if you share the database with others and DBAs are controlling the environment, not giving you any DBA privileges. In fact, each developer should be the DBA of their own development database because:

  • You should be able to kill defunct/infinite loop processes.
  • You should be able to watch progress of long running transactions and sessions (access to V$SESSION_LONGOPS and V$SESSION).
  • You should be able to create schemas for test (eg for Code Tester's repository and test code) and prototype (trying out new ideas) purposes.
  • Have easy access to alert log and trace files.
  • The DBAs don't have time to maintain your database.

Oracle Database Edition

My suggestion is that each developer gets a PE license of the same Oracle version as the "central" instances. A PE license is very cheap, XE is severely limited and may be older than your "central" instances (it is rumored that 11g XE will be released some time after Oracle Database 11g Release 2). Furthermore, SE1 doesn't come with any EE options, which PE does. If you don't run Windows on your desktop you would have to consider XE or SE1 instead.

Another solution would be to provide a set of application schemas to each developer such that all the developers use the same instance but have their own sandboxes. This can be cumbersome to control as synonyms would have to control which schemas are to be used in cross-schema object dependencies. Perhaps the other schema names are hard-coded in the application logic and perhaps the application doesn't work correctly when not running in a schema with a specific name.

Quest Code Tester for Oracle Repository

So how do you share the Code Tester unit test definitions? All the test definitions should be exported and committed to the source code version control system anyway, from which they can be imported into the other developer's databases.

Synchronizing Data and Code

Doesn't each developer's own database instance get out of synch with the "central" databases and what about access to up-to-date production data?

  • The purpose of the development environment is to develop and run unit tests and the unit tests must ensure that test data is set up and torn down when they run so no production data is necessary.
  • A mechanism that deploys other developers' committed changes since a given time should be in place (can be tricky to implement).
  • The development environment can be cloned from another database instance provided that they run the same Oracle version and character/national character set, even across platforms. It's possible to do this in a way that ensures that the instance-specific privileges and data of the development environment are kept. I've successfully co-developed such a facility for a large hedge fund in London.

Continuous Integration

Continuous Integration (CI) is the practice of integrating early and often. In our case it means that the test environment should automatically be updated each night with changes committed to your source code version control system and after this the entire test suite is run. This can be tricky and is the subject of a future blog post.

CI is a crucial element in the test approach:
  • If code is developed requiring privileges or access to objects that are not granted in any of the "central" environments (test, UAT, pre prod, prod etc) this will soon be discovered when the code is committed to the version control system as that night the CI will expose that the code doesn't compile or run. 
  • The integration tests that are run by CI will check whether the integration works correctly. This is necessary even though the component changed is fully covered by unit tests as the component could still be called or used in an incorrect way. The changes should only be committed to the version control system after the unit tests have been successfully executed.

Future Blog Posts

Future blog post will cover related issues like:

  • Testing effort/depth required -- how much testing do we need?
  • Test design tips & tricks.
  • How to break dependencies through creation/usage of mock objects in automated Oracle code testing.
  • How to apply Test-Driven Development to software projects using Oracle code.
  • Continuous Integration.
  • Code Coverage.


  • Working Effectively with Legacy Code by Michael C. Feathers, Prentice Hall, 2004. ISBN 978-0131177055.
  • Implementing Automated Software Testing by Elfriede Dustin, Thom Garrett and Bernie Gauf, Addison Wesley, 2009. ISBN 978-0321580511.
  • Foundations of Software Testing: ISTQB Certification by Dorothy Graham, Isabel Evans, Erik Van Veenendaal and Rex Black, Cengage, 2008. ISBN 978-1844809899.

Test Approach
del.icio.usFacebookDiggGoogleLive BookmarksNewsvineStumbleUponTechnoratiYahooDotNetKicks

3 comment(s) so far...

Re: Test Approach

Hi Finn, Thanks for your posts, the blog is very interesting and useful. You wrote that "data setup is one of the biggest challenges in database program testing." Indeed it looks as a big problem for me. We are doing custom development for Oracle E-Business Suite (OEBS) and I'd like to introduce testing in my company. Okay, to test SQL queries one needs to create rather small data set, data integrity kept in mind. But most of the tables are standard ones of OEBS, and all OEBS integrity rules are on application level (not in database). It's extremely difficult to correctly fill dozens of tables; it's also difficult and time-consuming via APIs. So I'm in doubt if the testing makes sense. I'll greatly appreciate If you can point me to any information on data setup techniques. Thanks, Egor.

By Egor on   Wednesday, September 30, 2009

Re: Test Approach

Hi Egor. Thanks a lot for your comments. I'll come back to this in more detail in a future blog post but for now I can give you this information: 1. I've successfully implemented test data setup using various XML techniques: DBMS_XMLGEN to generate XML based on a SELECT statement, DBMS_XMLSTORE to INSERT, UPDATE and DELETE data. The XML data is then the master data of the data setup and it is easy to save and edit. 2. I've also successfully implemented automated unit testing against OEBS some years ago. This was in order to test custom code that used the Receipt API. Invoices were created on the fly by the unit tests using AR interface tables and imported by submitting an Autoinvoice Master Program. Test receipts against the test invoices were then created using Receipt API. This was all automated. However, we didn't bother automating setting up exchange rates and customers. Which OEBS modules are you using? Perhaps I can talk to some people in my network for specific pointers. Hope this helps for now. Best regards Finn

By QCTOblog on   Sunday, October 04, 2009

Re: Test Approach

Hi Finn, Thanks for your reply! I'll take note of XML, perhaps it can help. As for OEBS, I'm interested in testing Fixed Assets. It has necessary APIs and interface tables, so it's not difficult to, say, create an asset or make an adjustment. Troubles begin when you need to setup several assets, then close financial period, then make some adjustments, then close period again... Surely it can be done, but it requires a lot of developer's time - which a developer doesn't usually have. I can think of another approach - searching for a suitable testcase in existing customer's data. Pro: you haven't to create your own setup, con: test can be unrepeatable 'cause data changes. Thanks and regards, Egor.

By Egor on   Sunday, October 04, 2009
Search Blog Entries
Blog Archives
<May 2013>
April, 2013 (13)
March, 2013 (10)
February, 2013 (5)
January, 2013 (7)
December, 2012 (6)
November, 2012 (10)
October, 2012 (8)
September, 2012 (6)
August, 2012 (8)
July, 2012 (8)
June, 2012 (12)
May, 2012 (21)
April, 2012 (10)
March, 2012 (16)
February, 2012 (19)
January, 2012 (20)
December, 2011 (19)
November, 2011 (14)
October, 2011 (12)
September, 2011 (17)
August, 2011 (15)
July, 2011 (16)
June, 2011 (13)
May, 2011 (15)
April, 2011 (8)
March, 2011 (21)
February, 2011 (17)
January, 2011 (16)
December, 2010 (13)
November, 2010 (13)
October, 2010 (7)
September, 2010 (15)
August, 2010 (11)
July, 2010 (13)
June, 2010 (12)
May, 2010 (14)
April, 2010 (12)
March, 2010 (13)
February, 2010 (12)
January, 2010 (7)
December, 2009 (10)
November, 2009 (12)
October, 2009 (15)
September, 2009 (18)
August, 2009 (13)
July, 2009 (23)
June, 2009 (14)
May, 2009 (17)
April, 2009 (7)
March, 2009 (14)
February, 2009 (7)
January, 2009 (12)
December, 2008 (7)
November, 2008 (11)
October, 2008 (19)
September, 2008 (14)
August, 2008 (11)
July, 2008 (14)
June, 2008 (19)
May, 2008 (12)
April, 2008 (18)
March, 2008 (13)
February, 2008 (8)
January, 2008 (7)
December, 2007 (5)
November, 2007 (8)
October, 2007 (13)
September, 2007 (13)
August, 2007 (16)
July, 2007 (11)
June, 2007 (6)
May, 2007 (5)
April, 2007 (5)
March, 2007 (8)
February, 2007 (6)
January, 2007 (6)
December, 2006 (5)
November, 2006 (8)
October, 2006 (4)
August, 2006 (3)