Toad World Blog

Using McCabe's Cyclomatic Complexity to design unit tests in Toad for Oracle

Feb 25, 2020 9:00:00 AM by Mathew Phan

In my previous blog post, I talked about what McCabe's Cyclomatic Complexity is, how to calculate it, and how to use Toad® for Oracle to calculate it for you.

Now that you understand that McCabe's Cyclomatic Complexity is a representation of how complex your code is, and you understand that you should work to reduce the value, what do you do next—after you've done your best to reduce it as much as possible? You can use McCabe’s Cyclomatic Complexity to design unit tests in Toad for Oracle.

[testing.jpg] Testing code using utPLSQL

How to use McCabe’s Cyclomatic Complexity to design unit tests in Toad for Oracle

It is best practice to have a test case for each pathway in you code. In other words, since I have a calculated complexity of 3, I should have at least 3 test cases to ensure proper code coverage.

Let's review my previous code example. I've commented inline the three possible pathways. [I've also modified my code a bit from my previous blog post to cover positive and negative decimal values just in case]:

mccabeutplsql01

mccabeutplsql02

 

 

So let's take a look at the 3 possible scenarios.

  1. If the input value is a positive number, then the left path is taken and the return value is 1
  2. If the input value is a negative number, then the middle path is taken, and the return value is 0
  3. If the input value is a neither positive or negative, it's zero, and the far right path is taken and the return value is 0

 

Create a test case for each scenario

In order to ensure proper code coverage, we should create a test case for each scenario. Fortunately Toad 13.2 comes with an easy way to do this. Toad 13.2 integrates with utPLSQL framework. If you haven't heard about this, you can learn more here.

  1. Now let's create our first test case for my code. I have the newest utPLSQL frame work installed and I have my function open in the Editor. Click on menu Unit Testing and click on Create Unit Test:

mccabeutplsql03

 

  1. A short video will appear. Read through the text and watch the video to get a sense of what we're going to do next. We're going to first create a test suite, then we're going to add tests to the suite, and then we're going to finish off building the test in the Editor. Click Continue when done.

mccabeutplsql04

 

  1. On the next screen, click on "Create new test suite" and give the test suite a name. I'm using MCCABE_TEST_SUITE in my screenshots. You can leave Suite path blank for now and it'll put it into my current schema. Add a description next.

mccabeutplsql05

 

  1. Then click on "Add another Unit Test." This will create my first test in the suite. I'll test the first scenario where a positive number is being passed in. So I'll put in the name for the test and a description. Next I'll assign this test to myself as owner, followed by selecting the object I want to test, which in this case is my function TEST_MCCABE. Lastly, what I want to check is that the return value is equal to 1, because that's what should happen if a positive value is passed in as a parameter, the function is supposed to return 1.

mccabeutplsql06

 

  1. Click on add another test case again, this time fill out the properties of a negative input test:

mccabeutplsql07

 

  1. Lastly, add a third test to this suite which checks when a 0 value is passed in, a 0 value should be returned:

mccabeutplsql08

 

  1. Now I'm done, I can see that Toad has helped construct the package that will run my test code. It's about 90 lines of code. This saves me a ton of time of having to build this out myself, while still giving me the ability to customize it if I need to. Click on "Send to Editor" to continue. You'll get another explanation page stating you'll need to put in the final inputs for tests, compile the package, then you can run the tests. Click on Ok when ready to move on.

 

  1. The test package will open in the Editor window. Scroll down to the procedure within the package. The first procedure should have the same name as your first test case. In my example it's POSITIVE_INPUT and it starts at line 24. But line 27 is where I provide the input value for my function. Since I want to test a positive input here, I can put any positive number, so let's put 10.

I modify the code to set l_INVAL to 10:

mccabeutplsql09

 

  1. Now, for the second test, NEGATIVE_INPUT, which will have l_INVAL, put in a negative value. Any negative number will do. I put -5:

mccabeutplsql10

 

  1. And lastly, for the procedure ZERO_VALUE_TEST, modify and set the l_INVAL to zero:

mccabeutplsql11

 

  1. Now I'm ready to compile this procedure. If it compiles successfully, I'll have access to this new toolbar and from there I can say "Run unit tests":

mccabeutplsql12

 

  1. The results will be displayed in the Unit Test Manager on the far left. You can see green check marks meaning my code ran three times, and each time, I got the output I expected for the corresponding inputs. It also lets me see how long each test took to complete.

mccabeutplsql13

 

Work is saved to database: re-run tests, regression testing, package can be called from other tools

Best of all, the work I did in creating these tests, is saved to my database. I can always re-run these tests as needed. If the code ever changes and I need to do regression testing, all my inputs and expected outputs are saved and I can just run them all with a single click. Other people who have access can run this package and run my tests as well, and this package can be called from other tools that can run PLSQL too.

I hoped you enjoyed this two-part blog series on McCabe's Cyclomatic Complexity and unit testing. And, I hope you test your code!

Have you heard about the new Toad utPLSQL unit test creation module?

Most developers and DBAs use Toad for Oracle to reduce time and effort to develop and manage Oracle databases. But did you know that the newly released Toad for Oracle 13.2 includes a new utPLSQL unit test creation module—and it’s available in all Toad editions Nov 18.

With this new utPLSQL feature, developers can create unit tests easily using an industry standard approach without leaving their database development tool of choice.

What else can Toad do that you didn’t know about? 

 

Try Toad free for 30 days.

 

Free 30-day Trial

 

Already in a trial? Talk to sales or buy now online.

Already a loyal fan of Toad for Oracle? Renew now.

 

Related information

Video: Introducing utPLSQL Unit Test Integration in Toad® for Oracle

Press release: New Quest Toad for Oracle Addresses DevOps Challenges with Seamless Integration of Database Developer and DevOps Processes

Blog: 13 new features in Toad® for Oracle 13.2

Blog: Why can’t database ops align with DevOps processes?

Blog: More blogs from Mat.

 

Questions?

Have questions, comments, or an idea for a new feature? Interact with experts on the Toad for Oracle forum on Toad World®.

 

Share on social media

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!

 

Tags: Development toad Toad for Oracle Developer utplsql

Mathew Phan

Written by Mathew Phan

Mathew Phan began his career as a technical support engineer for Toad for Oracle soon after graduating from the University of California, Irvine, where he obtained a bachelor's degree in computer and information science. Mat has devoted his entire 14-year career with the Toad family of database solutions and held various positions helping to shape the direction and growth of Toad. Early in his tenure, his focus was on Oracle databases from a developer point of view. Now as a systems consultant, he continues to work with customers to build effective solutions that meet their needs.