Skip to main content

Recommendations for unit testing PL/SQL programs

I have recently received a couple of requests for recommendations regarding unit testing of PL/SQL programs. I thought I would share with you what I told them.

First, some background: unit testing refers to the process of testing individual subprograms for correctness, as opposed to overall application testing (which, these days, almost always means visiting a website).

The basic idea behind unit testing is that if you verify that each individual subprogram works correctly, then you are much less likely to have bugs in higher-level programs that call those tested subprograms. And when you do, you know you can focus on the way the tested subprograms are used, and not the subprograms themselves.

The most important application of a unit test is to participate in a regression test, which can be run to verify one's code works today as well as it did yesterday. That will greatly reduce the chance of you upgrading the application and users complaining that a bunch of features that worked in V1.2 no longer work in the fancy, new and improved V1.3.

That's always embarrassing, right?

When developers talk about unit testing, they are usually not just talking about writing and running a test. They are talking about finding and using a framework that automates as much of the process as possible. Automation is key because testing always takes a lot of time, and manual testing takes the most time of all.

And most dev managers are not going to give you lots of time for testing. That's just the way it is.

Here are the main steps that could be automated:
  • Define the test cases: what are the various scenarios that need to be verified? These are driven by user requirements.
  • Build the code that implements the test cases.
  • Set up the data so that the test code will run properly.
  • Run the tests.
  • Record and examine the results.
There are a number of different tools available for automated or semi-automated unit testing of PL/SQL code. They include:
  1. utPLSQL: "Junit for PL/SQL", an open source framework that I originally built in 1999 and is now managed by Paul Walker.
  2. SQL Developer Unit Testing: Oracle's free PL/SQL IDE offers integrated unit testing.
  3. Dell Code Tester for Oracle: a commercial unit testing tool that I originally designed and helped to build, sold as part of the Toad Development Suite for Oracle.
and then a bunch of others, including DBFit, PL/unit, PLUTO and ruby-plsql, to name a few. I am not going to pretend that I have any great familiarity with this list of "others." I encourage you to check them out; I am going to focus on the Big Three. [Note: PL/SQL Developer, another popular PL/SQL IDE has some very basic unit testing features, but not enough to warrant review in this post.]

And even there, I am not going to offer a detailed comparison of features. You will need to do that yourself, based on your requirements. Instead, I will take a step back and offer some high level guidance to get you started on your journey.

utPLSQL

First, it is important to correct a misunderstanding that some people have, and the creator of ruby-plsql repeats in his blog: "Steven stopped developing further utPLSQL and currently there are no other active maintainers of this project." Raimonds is absolutely correct that I stopped working on utPLSQL years ago, and for a while it did languish. 

Last year, however, Paul Walker stepped up to the plate and took over managing the project. He has been applying bug fixes, improving documentation, and encouraging discussion and collaboration.

Since 2006, utPLSQL has been downloaded over 76,000 times. Of course, there's no way of knowing how many dev teams use it, but I think it is safe to say there are hundreds of groups around the world actively relying on utPLSQL.

utPLSQL is free and gives you total control over your test code. But that's mostly because you have to write all the test code (SQL Developer and Code Tester generate test code for you). And then you have to maintain all that test code. But once you've written your tests, built around the utPLSQL API, you can execute your tests with a single command, and utPLSQL will tell you whether or not your code passed (with unit testing, passing comes with 100% correctness).

utPLSQL test scripts can be integrated into continuous integration scripting.

Code Tester for Oracle

I think that it is safe to say, even given my perhaps doubly-subjective perspective of (a) being an original designer of Code Tester and (b) now working for "the competition" - Oracle, that Code Tester is the most sophisticated automated testing tool for PL/SQL. It's packed full of features, handles lots of complex scenarios (for example, if your parameter is a collection of records and the record contains a collection of objects, you can right inside the UI provide values for that parameter), generates test code from your specifications, and offers limited integration with Toad.

That sophistication comes, however, at a steep price, namely $2854 for the Toad Development Suite for Oracle. You can't buy Code Tester by itself.

So here's my recommendation: if you are licensed to use the Toad Development Suite for Oracle (even if you only use Toad, you may have a license to TDSO via a site license, and not even know it), you owe it to yourself and your team to check out Code Tester. 

You will likely run into some frustrations and limitations, but it does an awful lot of the heavy lifting for you.

Code Tester tests can be integrated into continuous integration scripting via a PL/SQL API.

SQL Developer Unit Testing

Last and definitely not least, is the unit testing feature integrated directly into SQL Developer. Let's stop right there to make sure you appreciate what I just said:

The automated unit testing feature of SQL Developer is not a separate tool from the editor (which is the case with Code Tester - it is external to Toad, with only minimal integration points). It is a part of SQL Developer. This is, I believe a crucial point, because developers do not like to leave their editors.  Instead, they want all necessary functionality brought to them inside their editors.

Here is one screenshot from the doc to give you a sense of the integration:



In many ways, SQL Developer Unit Testing and Code Tester for Oracle are similar. They both aim to take the burden off of you for writing test code. Instead, you describe the test case: What are the input values? What data needs to be set up? Most importantly, what are the expected outcomes?

SQL Developer then generates code that implements your test case. If you need to change the test, you don't have to modify test code (as you would do with utPLSQL), you go into the UI, make a change, and run your test again.

SQL Dev unit tests can also be integrated into continuous integration scripting.

So here's my recommendation: 

If you are already using SQL Developer, the decision should be clear. Try the integrated unit testing features. See if it meets your needs (at least enough to get you going, building some tests, experiencing the positive feedback loop). 

If you are not a SQL Developer user, let's say you use Toad or PL/SQL Developer, and you are not likely to get access to Code Tester for Oracle, then I also suggest you install SQL Developer and try its unit testing features. Sure, it would be better to just use one IDE, but if the automated testing of SQL Dev works for you, given its infinitely low cost (as in, free), I would think you could justify having that second IDE around solely for testing.

Overall Conclusions

If you have access to an IDE that offers automated unit testing, including the generation of test code from test case specifications, try that first. The productivity gains of test code generation are so massive, it can easily be worth accepting some limitations.

If you are allergic to UI-based testing and/or worry about being constrained by an IDE's feature set (in other words, you are a command line fanatic), utPLSQL offers a powerful alternative. It's proven to be useful to hundreds and maybe thousands of dev teams over the years. It is an actively maintained and enhanced open source project.

Your Experience?

Do you use an automated testing framework for PL/SQL? Or have you built your own? Please tell us about your own experience.



Comments

  1. I like SQL Developer Unit Testing. With a few workarounds and a lot of patience, it's a very able tool for collating and running tests.

    SDUT (?) just needs a little bit of love from the development team. Maybe you could have a word ;-)

    ReplyDelete
  2. Thanks, Kevan. Can you give me any specifics regarding perhaps the top three bits of love you'd like to see?

    ReplyDelete
  3. I've used utPLSQL for a couple of years extensively and it really helped me to get to grips with unit testing. I loved not being tied into an IDE (we ran the tests from Eclipse). We even extended the framework with our own assertions.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. I would start with the documentation set available at utplsql.org

      Delete
  4. Last year we switched from Code Tester to utPLSQL. We tried Code Tester for several years but somehow it did not help us with getting a true TDD process in developing PLSQL logic. Changing the signature of a function or moving it to other packages caused real headaches. Since we have mostly java developers in our team, utPLSQL felt more natural. With utPLSQL we also lifted our automatic database tests to an higher level. It is really easy to write test cases and assertions on the integration of packages and other code units with utPLSQL. We tried this with Code Tester but we didn't succeed.

    ReplyDelete
  5. And a note received from Dennis V:

    we have integrated dell code tester with Jenkins to have a continuous integration test. it save a lot of bug fixing in released versions. and also helps us to have a application that is backward compatible. what is a big benefit for our customers. #adaption

    ReplyDelete
  6. Can I have 4?
    1. Sub-folders under Tests to help manage multiple tests.
    2. Move the Parameters and Process Validation windows to the top of the edit screen (or make Startup and Teardown windows collapsible).
    3. Add 'copy implementation'
    4. Use the SQL Developer-like code editor (with syntax highlighting, code completion, etc.) for editing

    ReplyDelete
  7. Kevan, I will make sure Jeff Smith sees this, but I suggest you also consider posting these as ideas on the SQL Dev Exchange - or maybe you already did that? https://apex.oracle.com/pls/apex/f?p=43135:1

    ReplyDelete
  8. I use utPLSQL the automatic testing of code. Start using were not easy, but today it is testing a fixed part of the development cycle.

    B. Vetrak

    ReplyDelete
  9. Hi Steve, Thanks for putting this up. SQL developer has scope for improvement...http://www.fuzzy.cz/en/articles/unit-testing-plsql-code-in-sql-developer-problems/

    ... until then utplsql it is.

    ReplyDelete
  10. Hi Steven,

    thanks for this good article, i got a couple of questions, and I really hope you got the answer:
    is there some best practise about how to design procedure and function in order to make it easy for testing ? in all the ex provided by utplsql, all the examples are testing basic algorithm that doesn't need any access to database, there's no dependency on table content. i'm working in a big project where almost every procedure, every function is based on tables content, ( we are dealing with more than one thousand table, to be more specific, it's a financial application ), my question is, what's the best approache to be able to test this kind of function and procedure where the body of the f/p depends on the database content and the user setup ? i was thinking about using another schema dedicated for testing, where the the setup function will setup anything on that schema that is needed for the testing and then rollback it once the testing finishes, please let me know if you you have any good url or book that would be helpful and that talks about the same subject, again many thanks for your valuable articles and support.

    ReplyDelete
  11. Hi Steven,

    I am unable to see the objects after connected to the code tester. My connection is showing successfully connected. Could you please help me on this.

    Thanks & Regards,
    Gangadhar

    ReplyDelete
  12. Gangadhar, I suggest you report the issue to Dell Support or go to ToadWorld.com and see if you can find a place there to ask a question about Code Tester.

    ReplyDelete
  13. Hi,
    Very informative blog i must say.
    I just have a quick question ;does sql developer unit testing support %rowtype data types as input or out put from a function/procedure?

    ReplyDelete
  14. @swapnali, thanks glad it was useful. Answer to your question: no, %rowtype and record arguments are not yet supported.

    ReplyDelete
  15. After trying many testing frameworks, all of those we tried were mentioned here, we picked and love Ruby plsql-spec. That was more than 5 years ago. It's integration with Oracle EBS makesit even better for our shop. plsql-spec allows us to perform small unit tests to larger integration tests like creating POs and invoices and then matching quickly.

    Keep pushing TDD!

    Thanks,
    Jason

    ReplyDelete
  16. I should have also mentioned how easy it is to automate the plsql-spec tests by adding them to a continuous integration server like Hudson.

    And the added benefit of using a language like Ruby that is a joy to program in.

    ReplyDelete
  17. Thanks, Jason. Very interesting! I imagine for many PL/SQL developers the idea of learning ruby is rather off-putting in terms of leveraging this framework. Any suggestions for a fast, easy path to having enough ruby knowledge to use plsql-spec?

    ReplyDelete
    Replies
    1. What you say is sad but true. Learning another language just to test your PL/SQL code can put some people off. But, in my opinion, the benefits far outweigh any costs. Also, you just need the Ruby basics which a developer should be able to pick up very quickly from any number of sites and books.

      One caveat (for my slow brain) is that the heavily used block functionality in Ruby may take a solely PL/SQL developer a bit of brain retraining but other than that Ruby is a very straight forward language that is designed around the concept of least astonishment. You won't get the hmmm, I recall there are 3 kinds of arrays in PL/SQL, which one should I use now or hmm, now I'm using PL/SQL object types and need to access SQL types where's that no-man's land between SQL types and object types where they havne't built the bridge and I can't get there from here types of questions.

      Learning a different language will exercise that brain and put it into a higher gear. Like playing those puzzle games you recommend.

      We PL/SQL developers must realize that we cannot write everything in PL/SQL. Even with Apex, which I really like BTW (Check out Selenium for testing Web UI's using RSpec). Maybe it's a bit easier when developing for Oracle EBS since the tech stack is so large. You already need to know shell scripting, some perl, PL/SQL, of course, Java, and even Pro*C was helpful ten years ago. Now we are in or entering the post Java era and Groovy is making it's way into the stack. So what's another syntax?

      Maybe someday Oracle will ask Raimonds to present on plsql-spec for the good of PL/SQL developers and not based upon whether it competes with OATS or some other pay for Oracle product. I've submitted to present on plsql-spec multiple times for OOW but never got the green light. So I'm not holding my breath.

      All I can say is that in my opinion PL/SQL developers need to employ TDD and be open about choosing the best options for doing so. Once you see how much better TDD is for the quality of the software you deliver you won't go back and adding methods to your PL/SQL package procedures and functions the way plsql-spec allows feels quite natural and is really cool in my opinion!

      Keep up the great work of spreading the word on testing.

      Delete
  18. Nice post. Your readers might also find real user reviews for Oracle SQL Developer on IT Central Station to be helpful. As an example, this user writes that one particularly valuable feature is, "Oracle SQL Developer Data Modeler. No PL/SQL IDE has such modelling capabilities, as far as I know. This is a killer feature for me." You can read the rest of his review here: https://goo.gl/MG3nQH.

    ReplyDelete
  19. Right now SDUT does not support REF Cursors and %ROWTYPE as parameters. Is there a workaround for this, other than writing some wrapper procedure?

    ReplyDelete
  20. Hi,

    We at Intrum Justitia SDC (Riga) have recently achieved a goal to implement automated testing for a big corporate system with PL/SQL back-end and Angular 2.0 front-end, which has been started to be developed recently. After investigating what's there for PL/SQL testing, we took the idea to test it with a dynamic language and developed our own tool.
    The new user interface is written in JavaScript and runs in a browser. To make it possible I have created an HTTP driver for Oracle, which allows to manipulate data and to call PL/SQL procedures with JSON requests, receiving the data also in JSON format. Virtually all Oracle data types are supported, including the complex ones at any nesting depth. Transactions (including the global multi-user ones) are also supported.
    So it was very natural for us to create a tool, which will allow to write automates tests for the PL/SQL backend in JavaScript.
    As a template we took JavaScript community established TDD standard syntax with suite() and test(), augmented it with the expect.js library and wrapped in a Java application (to secure the passwords).
    As a result we already have hundreds of tests which have been written by all PL/SQL developers AND testing engineers, which haven't had any programming experience before!
    The tool now slowly gets a WEB-GUI, I plan to integrate it with Jenkins, TestRails and possibly with our future Oracle CI tool :)
    It now counts as out proprietary product, however, if you are interested in the details and out experience, please leave a comment or contact me.

    P. S. Steve, I have once participated in your seminar "PL/SQL best practices" in Riga (I guess around 10 years ago or so :)).

    Cheers.

    ReplyDelete
    Replies
    1. Hi Gwindor, I am very interested to know more about your experience in developing your tests in JS for PL/SQL! Please let me know the best way to contact you in regards to that. Thanks!

      Delete
  21. And well, actually I have worked with Raimonds Simanovskis, the author of the original idea of testing PL/SQL in Ruby.

    ReplyDelete
  22. Amazing, thanks a lot my friend, I was also siting like a your banner image when I was thrown into Selenium.When I started learning then I understood it has got really cool stuff.
    I can vouch webdriver has proved the best feature in Selenium framework.
    Thanks a lot for taking a time to share a wonderful article.

    ReplyDelete
  23. Hi Steve - now that it is nearing the end o 2017 and version 3.02 of utPlsql just came out - any further comments about it? I have long wanted to implement database unit testing at my company. I am excited by utPLSQL's new version but, as you say, you have to do a lot of the work by hand.

    Thoughts?

    ReplyDelete
    Replies
    1. Alan, I have not had time to work with v3 so I cannot report to you on the changes. My impression is that the utility will now do more for you, but you still must write those test packages yourself.

      Delete
  24. Hi I was using sql developer for uni test case. Does
    integration Of this sql developer test suite to Jenkins requires any shell script or pom.xml change.how can I map it.

    ReplyDelete
    Replies
    1. Sorry have not done this myself. Check thatjeffsmith.com or ask @thatjeffsmith on Twitter.

      Delete
    2. Preethi, I am hosting an AskTOM Office Hours session on PL/SQL testing on November 5 9 AM Eastern time.

      I wonder if you are still using SQL Developer for unit testing and if so, would you be able to join the session and share some of your experience, offer tips for others?

      Delete
  25. Hi, "SQL Dev unit tests can also be integrated into continuous integration scripting" please elaborate on this.

    ReplyDelete
    Replies
    1. This integration is accomplished by using the unit testing API. You can find more information about it here: https://docs.oracle.com/en/database/oracle/sql-developer/19.4/rptug/sql-developer-unit-testing.html#GUID-1B5A170C-349C-4990-B695-650E652D7AA8

      Delete
  26. Hi Steven, I have just started doing the unit testing of my stored procedures using SQL developer. But while checking the code coverage report, it seems that it is not showing the number of executable/ relevant lines. It just displays the total number of lines and the number of lines covered. It appears to be quite difficult to find out the exact code coverage percentage. Is there any other way of displaying the executable lines or I need to move to utPLSQL for finding out the exact figures?

    ReplyDelete
    Replies
    1. Sorry, Bhumika, I have not used that report. If you have a Twitter account, I suggest asking about this of either/both @thatjeffsmith or @oraclesqldev.

      Also, if you are on 12.2 or higher, we now offer a utility that generates code coverage data. For more information:

      https://oracle-base.com/articles/12c/dbms_plsql_code_coverage-12cr2

      Delete
  27. Nice blog buddy,

    Well, Unit Testing is a level of software testing where individual units/ components of a software are tested. The purpose is to validate that each unit of the software performs as designed. A unit is the smallest testable part of any software. It usually has one or a few inputs and usually a single output.

    ReplyDelete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel