Article Info


If you have not yet read Part 1 of this series, please do so first, as this was written as a means to prepare you for Part 2.


I am a developer that has been writing, studying and reviewing PL/SQL for 7 years. In this article I am going to relay my opinion on additional starting points to writing effective PL/SQL.

Let's Talk About Coding Guidelines

50 Lines Or Less
First, I'll start with one of the most basic guidelines that I follow as best as I can. Each module you write should contain 50 lines or less of code. That is not a typo! Are you screaming at this article wondering how can anyone follow that, or maybe you're saying that rule cannot be applied to real world development? Well, maybe you're right. But, I will say this; the next time you go to write a procedure or function, try and fit your code into 50 lines, keeping it readable and maintainable. I bet you'll come back and say but my requirements are to write this huge program that does these 100 things and so on. I understand. I've had those requirements myself. We'll come back to this a little later in the article, but first my intent is to get the hamsters in your mind running.

CRUD Statements
I have several strict guidelines I set for myself when coding. You may not agree with all of them, but I believe if you follow some of these guidelines, you're closer to achieving the rule that enforces 50 lines of code per module. When it comes to writing CRUD statements, I have seen situations where these operations are thrown right into a main code block. I have seen situations where the operations are kind of organized but exist in multiple packages throughout a system. What's my solution to this? Organization. When I am responsible for adding features that require new database tables, the first thing I do is get the table structure figured out. This may not be your role depending on where you work, but the rule of thumb here is you need to understand the data model before you can proceed. Next, for each table, I create a package (ie. TABLE_PKG). Within the new packages I write code for the CRUD statements that are expected to be done against that new table. There's a lot of code there, right?

CRUD Statements Program Naming
Now on to naming objects. For the CRUD operations, I have seen developers try and give names to them. To be honest, do names really matter in this case? A delete is a delete is a delete. What I like to do for consistency sake is name insert operations "INS", update operations "UPD" and delete operations "DEL". What this does is creates a uniform way to manipulate data in the database with no guessing involved. In my situation, b/c I did this so often for so many years, I ended up writing a PL/SQL script to generate a good majority of the code required to perform those operations. This way, I provide a table name to my script and the result is DBMS_OUTPUTthat I can copy and paste into a package.

Other Object Naming
When it comes to naming, I have had the habit of suffixing core objects with an abbreviation of what type they are. For example:

  1. Package = _PKG
  2. Trigger = _TRG
  3. View = _VW

Closing Arguments Regarding Object Naming
Take some time to look over what guidelines I have put in this article and try them out yourself. I guarantee that the code you write, with just the suggestions above, will start to look cleaner and DMLoperations will now be encapsulated, promoting re-usability if needed.

50 Lines Or Less - Part 2
To continue from where I left off above, maybe it's dawned on you why this type of guideline make sense. If not, I'll elaborate a bit. First thing, minimizing code in each module will promote encapsulation. That causes one to have to start organizing their code info smaller pieces. A benefit here is that it may come to light that you are writing a block of code that can be re-used by other programmers. Because you encapsulated a specific chunk of code, you may have prevented another one of your work buddies from having to recreate the wheel and/or have to go through the tedious thought process to write the code! Also, just because someone else doesn't need that functionality right now, doesn't mean someone won't need it next month or maybe even next year.

Another benefit to breaking this code down is realized when subsequent changes to your code need to be made. Well organized code is easy to maintain. If you're looking at a program with 1000 lines of code or one with 50 lines, which would you rather make changes to? Let me provide a quick example with some pseudocode:

// routine to install OS and software on new computer
   // install windows
   splash screen comes up
   click next
   choose options
   click next
   enter product code
   click next
   windows installs
   create user account
   install adobe reader
   accept EULA
   click next
   click finish
   install other software...

Joking aside, you may have seen similar programs in the past and may have seen them in the present. So think about how you would re-organize this code to make it more readable, easy to maintain and follow the 50-line rule.

// routine to install OS and software on new computer

Amazing! So 14 lines of code in our block above went down to 3 lines b/c the implementation of each installation was encapsulated. And in the future, if someone has to make a change to how to install windows because Microsoft modified their process, it is very clear which routine needs to be modified. A final important note about encapsulating code is that it also makes it easier for testers to focus specifically on what features have changed. In the case above where someone goes back to this code to update the windows installation process, the testers need only test the windows installation process and not everything else outside of that. If you are familiar with unit testing (manual or automated), breaking down code this way also helps with that as well.

An Approach To Breaking Down Code

If you're the type of developer who likes to crank out code with minimal time spent up front planning the painful details for every program you must write, I completely understand. So here's an approach to try. As you start writing code, start thinking about how it can be organized. Maybe after you write 20-30 lines, it might make sense to take some of that code and move it into a function or procedure. The thought behind this approach is that you don't have to try and break everything down up front before you can write a line of code, but you refactor your code as you go. This has been an approach that even I have used over the years, and it is especially useful if you're in a bind and time is limited.


I hope after reading this article, I have made a positive impact in your life and have brought to light the importance of well written code. I wrote this article because I am THAT passionate about writing good code and I hope I have driven you to be just as passionate! So go ahead and pick up some of the books I have recommended, get reading, and may your coding future be bright.


You must be logged in to submit a review.