5 fitness tips for PL/SQL code

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:

• Will reduce your blood pressure
• Won’t break down as much
• Will accomplish tasks better: more consistently and dependably
• Will avoid time-consuming and agonizing recovery periods
• Will help you sleep better

 This post will present five ways to achieve more PL/SQL code fitness.

Why is PL/SQL fitness important?  

Risks abound when code isn’t tested before it goes to production—when the code is “unfit”: 

• Problems in your production application
• Unhappy users
• Developers worried about code malfunctioning once it hits production
• Having to ask the DBAs to recover data from before some bad/incorrect data appears on the database

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.

Five tips for improving the fitness of your PL/SQL code

Here are five ideas to help get your code to a fitness level that you’ll be happy with:

1. Write “good” code. Write code to your standards, using best practices for performance and for elegance, and keep it maintainable.

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.

2. Debug the flow of PL/SQL (procedural code). See the code react and flow as it executes.

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.

3. Write unit tests as you code. If the unit tests you need do not already exist for a piece of PL/SQL code, design and develop new unit tests on the fly, as the code is being written.

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.

4. Automate your unit testing and code reviews. Instead of forcing people to remember to run tests on their code—whether regression tests after a change, or tests on brand new code—automate the execution of unit tests. Automated tests will run the same way every time, and won’t be skipped or overlooked.   If accuracy and quality of your code are important to you, this is the way to make them reality.

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. 

5. Make production moves dependent upon passing all functional unit tests and pertinent code analysis rules. If a unit test fails, should PL/SQL code move up to production? Assuming you have designed your tests well, why would you move code to production that has proven itself to be inaccurate?  If code does not pass code review standards (rules), don’t you want to hold it back so it can be improved (instead of passing technical debt on to future database developers)? 

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.

Which Toad edition is right for you?

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?

Useful resources

E-book: Enabling Agile Database Development with Toad

Case study: Major financial firm advances DevOps with trusted database tools

Data sheet: Toad for Oracle: Find the edition that's right for you

Additional Toad World blog posts

How to: Debugging PL/SQL Code with Toad for Oracle

Questions?

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.