Oct 10, 2019 9:52:46 AM by Tim Fritz
How much time per day, week, or month do you spend on physical fitness training? I’m talking about strength training, functional training, cardio training, sports training, walking, taking the stairs instead of the elevator, or any combination of those and maybe others. Think about any type of fitness training you’ve ever been a part of, and then consider this: your database code that interacts with your Oracle database needs to be fit, too.
Ok, you might ask, what does it mean for database code to be “fit”? Well, like your body when it is more fit, when your code is more fit it:
This post will present five ways to achieve more PL/SQL code fitness.
Risks abound when code isn’t tested before it goes to production—when the code is “unfit”:
We hear from many of our Toad® customers that testing is not being done as much as it should be, even in organizations that have adopted agile development practices. Agile development, it turns out, causes some development teams to actually reduce the unit testing they do when they write and change code. Why? Partly because of smaller amounts of new/revised code in each build; instead of taking the time to run a regimen of unit tests, why not just have the developer quickly desk check their code and run some basic checks, and then get the code to production? After all, if any problems are found by production users, they’re fixed in the next sprint. Easy, right?
Well, another approach is to have some ready unit tests that you can simply run and find out—before production—whether the expected results are actually produced by the PL/SQL code.
Toad for Oracle Developer Edition, voted #1 in 2019 DBTA Reader’s Choice Awards, and its code testing features can make the seemingly daunting task of writing, then running, functional unit tests of PL/SQL much easier. So simple, in fact, that it doesn’t make sense to skip it, given the risks.
So, how do we get your PL/SQL code to that sort of fitness level, the same you’d want for yourself? Let’s discuss five ideas to help you do just that.
Here are five ideas to help get your code to a fitness level that you’ll be happy with:
Toad for Oracle Professional Edition+ includes a feature called Code Analysis that can run static code reviews on PL/SQL that resides in your database objects like functions and stored procedures. A calculated “quality score” tells you how well written the code is, to your own criteria. Reasons for low scores are shared by the tool, so you know exactly what needs fixing.
Toad for Oracle Base Edition+ includes a debugger feature that allows you to step through procedural code (like PL/SQL) to view the logic branches the code takes as different input is provided. Does the code act as it should? You will see for yourself where it does and where it does not.
Oracle database functions, for example, produce results based on input parameter values. Given an input parameter value, the output from the function is (must be) predictable. If the function does not produce the expected result, there is a code accuracy problem. It needs to be fixed—especially before the code reaches production.
Toad for Oracle Code Tester, part of the Toad for Oracle Developer Edition, can help you thoroughly test every piece of PL/SQL code.
Toad Code Tester unit tests and Toad Code Analysis code reviews can automatically run when the object containing the PL/SQL code is checked into your version control software (VCS). The execution of these tests and code reviews can also be part of your continuous integration (CI) processes if you are implementing, or have implemented, DevOps for your database development. More and more, we see that is the case.
Toad DevOps Toolkit is the framework for handling those CI processes that involve Toad functionality like unit testing and code reviews, or schema or data compares, or running of scripts.
Again, for Oracle code, Toad DevOps Toolkit is the way to automate the requirement that tests and review rules all pass before code is deployed to production.
PL/SQL code needs to be fit to do what is expected of it: to keep application users happy and returning, to make money for your company or to keep business processes running smoothly. There are at least five ways to help ensure healthy and fit PL/SQL code, and they are all achievable with the right tools. Accuracy of results, debugged logical flows, effective and complete unit tests, conditional production implementations, and automation are keys.
Toad Developer Edition for Oracle is an integrated solution for Oracle database development. Toad Code Tester is just one of several tools that comprise this solution. Toad Professional Edition for Oracle is also part of the Developer Edition. Learn how you can work smarter and become more proactive, please read the Toad for Oracle: Find the edition that's right for you datasheet, and browse Toad World at www.toadworld.com.
Better yet, why don’t you try Toad for Oracle Developer Edition, free for 30 days?
Have questions? Start a discussion about this blog in the Toad World forum, Toad for Oracle.
+ Denotes feature is available in Toad for Oracle edition plus every next-level edition. Edition levels are as follows: Base, Professional, Xpert, Developer, DBA.
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.