Oracle Coding Best Practices: Good Coding Practices

Hi, This article will focus on a series of good coding tips and coding styles that I have used through the years. These tips will help your code be more consistent and allow others to make changes more easily.

Tip 1

A more general tip, but I use the KISS style of coding: Keep It Simple Stupid! If you are anything like me, with our busy world, in two weeks or so, you won’t even remember working on the code let alone what you did to it. The more simple and straightforward you can make any code, the less you will be bothered while on vacation or when promoted to a different job within your organization.

Tip 2

Use modular programming techniques. In the Oracle world, this generally entails the use of Packages. Packages are a convenient way of organizing related modules together. I am a big fan of ‘code it once’…don’t have the same routines copied here and there so when there is a change to it, you have to change it all over the place.

Modular coding in packages allows for short routines to be coded and remain hidden from the general applications. To do this, you simply don’t list them in the specification line. Any program can make reference to it from within the same package. So, code your error modules, file read/write routines, repeating calculations, etc., in separate modules within the package and have them be private routines for that package.

Tip 3

When working with lots of SQL from PL/SQL that will be re-executed, try to keep those cursors open. PL/SQL apps that submit a lot of SQL can cause a lot of CPU utilization. This stat doesn’t show up in the Oracle monitors but it does show up in the Unix/Linux side of things (utilities such as vmstat) as CPU time. When you submit a SQL, your Oracle connection requests memory for the cursor, processes the SQL, grabs the result (data returned), and closes the cursor. When closing the cursor, the host OS is signaled to deallocate this memory. The allocation and deallocation of memory takes some CPU time. Lots of SQL, lots of CPU time. If you are frequently running a lot of SQL, code in such a way that you can leave these cursors open and simply reuse them, using the DBMS_SQL package perhaps. I have a nice blog on some of these techniques in Part 5 of this series. Click here to review using the DBMS_SQL package to give fine control over opening/parsing/closing of cursors.

Tip 4

Indent your code. Code neat and tidy. This kinda refers back to Tip 1 but also makes code, especially IF/THEN/ELSE and CASE statements easier to see which steps are related to which conditions. Toad has two nice features for larger modules: code condensing and code formatting.

Code Condensing

Notice the ‘-’ in the gutter at line 7. Below, I clicked on it and Toad condensed the code. This is extremely handy when working on larger blocks of code and you are not interested in the code in this related section.

Condensed IF statement

Toad Formatter

Right click on your PL/SQL module, select Formatting Tools -> Format. This will nicely indent all related code, putting a lot of items on their own line as well. I generally start with this when working with someone else’s code.

Tip 5

Don’t nest any code more than two or three levels deep. IF/THEN/ELSE, Loops, and CASE statements fall into this category. You can go like 250 levels deep on these items (IF statement under an IF statement under an IF statement…250 times or so…). Wow, would this make for confusing code. Again, relating back to Tip 1, keep the depth level to two or three levels. Anything more than that and the code gets difficult to follow and modify.

Tip 6

Use Code Templates to keep your code the same, formatted, and easy to follow. PL/SQL really doesn’t have the concept of copy libraries, but Toad does offer code templates. I created a video on using and creating Toad Templates. Click here to review the video.

Tip 7

Use standardized coding practices. Use code review as well. Some shops (I haven’t been in one that did this, but what a great idea) do sitdowns and code reviews. When I was a programming manager (seems like a lifetime ago), I not only encouraged making the code look like the others in the same series, I insisted upon it, and more than once I made the programmers recode it till it looked like the others in the series. My goal here was code maintainability. Toad has a Code Review that takes code and applies PL/SQL code to several industry standard coding practices. It is a scoring method to show ease or difficulty of the code, maintainability, and level of expertise needed to maintain the code. I have produced a video blog on Code Review and you can review the details of using Toad’s Code Review by clicking here.

Tip 8

This tip is related to Tip 6. I find the PL/SQL exceptions for a series of routines to be similar, perhaps. Again, the lack of copy libraries in PL/SQL inhibits a standard set of code to be easily included in all routines, but you can make a Code Template out of a series of standard exception clauses for your code in Toad, and include them as you maintain your routines.

Tip 9

Don’t use the GO TO statement. Even the looping constraints now have a ‘continue’ clause to skip code and go to the next iteration of the same loop. I see no reason to use a GO TO statement in PL/SQL. I’m sure there are some good arguments out there but if you get your code execution to flow outside of a loop (without hitting the loops normal exit), you can get interesting results. Using GO TO statements is just not a good or acceptable coding practice.

Tip 10

When doing DML (especially updating and deleting) and your app wants to display how many rows are affected, use the cursor variable %ROWCOUNT. I know this goes back a ways, but I still see some of this old code on occasion. Back in the day, we used to run a SELECT COUNT into a variable with the same DML statements where clause. In Oracle8i (I believe), this cursor variable became available. Simply comment out or delete the SELECT INTO statement and just ‘:=’ the into variable with %ROWCOUNT.

Summary

These 10 tips are something I push in both my PL/SQL and SQL Performance tuning courses. I feel they make for good maintainable code and code that is consistent with industry standards.

I hope you find these tips useful in your day to day use of the Oracle RDBMS.