PL/SQL Standards

WELCOME, GUEST

Oracle PL/SQL Naming Conventions and Coding Standards

As an author and trainer on the PL/SQL language, I (Steven Feuerstein) am often asked about my naming conventions and coding standards. My answer has generally been a combination of muttering and vague statements and hand-waving. That's because I hadn't taken the time to write down the standards I do follow. No more!

Below you will find a link to a document that offers my ideas regarding coding standards. This is a work in progress. If you have disagreements with my approach, have a suggestion for something to add to this document, or would like to offer your own set of naming conventions and coding standards to the world of PL/SQL, send me a note at steven.feuerstein@quest.com. If I agree with it, I will put it into the document (and give you credit!) or make it available on this web page.

Steven's Naming Conventions and Coding Standards

PDF Document, 103KB
Last Updated: 5/28/2009

Naming Conventions and Coding Standards Offered by Others

PL/SQL Standards Developed for the PL/SQL Starter Framework

These standards are offered up by Bill Coulam, a fellow PL/SQL enthusiast and author of the open-source PL/SQL Starter Framework. Bill can be reached at bill.coulam@dbartisans.com.

Trivadis PL/SQL & SQL Coding Guidelines

PDF Document, 696KB
Last Updated: 4/26/2010

This comprehensive and very nicely organized guide comes courtesy of Roger Troller, Senior Consultant of Trivadis (www.trivadis.com), a consulting and training firm based in Switzerland. Many thanks, Roger!

PDF Document, 605KB
Last Updated: 11/3/2009


		Comments (Read Below)







Message	*
Enter code!

Date: 10/30/2009

Hello Steven,

I had to write a standards document for our company. It became quite lengthy to include all requirements. Yet the most important rule is, as Terry Pratchett says in Thief Of Time, "That's why there are rules - so you think before you break them"

Regards
Marcus

Date: 7/20/2009

Hi Steven

As you already noticed I'm writing in my blog (http://blueeyesfeup.blogspot.com/search/label/Oracle%20Blues) my comment on the document you wrote (its a work in progress).
I think that finally people got to talk about that in a one "public place" (many ideas are spread over several forums, etc..)

I just wanted to answer here to our "Things I still wonder about" questions.
--- How to distinguish between all the levels of scope between global (package level)
and local (in the current block). What if the current block is a local subprogram
within another subprogram?
I use g for global l for local and ll, lll (2 l's, 3l's, etc.) for depth procedures/functions ... anonymous blocks uses the same convention as the non-anonymous block there are.

--- Should functions and procedures without any arguments be invoked with "()"
after the subprogram name?
I use that but I do not enforce it on my team. I think the code gets more readable that way.

Regards
Filipe Silva

Date: 7/2/2009

I can see that some things are unclear in the naming convention - so it should be probably corrected in some way.
Additionally different people have different preferences (what should be the prefix/suffix, wherther you should user upper/lower case).

I can see that we are trying to put too manyinformation into the name:
- access type: in, in out, out [i|io|o]|[in|inout|out]
- definition scope: local, global [l|g|]
- type of variable (note that we should avoid that): varchar, varchar2, integer, etc.
- variable character: cursor, parameter, iterator (indirectly defined cursors), etc. [c|cv|p|par|i|iter|...]

If we get rid of the type of variable (since we should use %TYPE whenever possible and the type can often change) then we have 3 information to put into the name.
Prefix and suffix allows 2 pieces of information. Where to put the third one?

What Steven did was to partially combine 2 pieces of the information into 1: in|inout|out had "parameter" hidden in it. I think that similar thing should be made to the rest. Probably he made a mistake to have in/out at the end of name (I did the same mistake by the way).

If you have your own naming convention, then please, verify how you would call the following variables:
"var" local constant varchar2(1024);
"var" local variable varchar2(1024);
"var" global constant integer;
"var" global variable pls_integer;
"var" global cursor;
"var" local ref_cursor;
"var" parameter in out varchar2;
"var" table of integer indexed by integer;
"var" parameter in out ref_cursor for update;

IMHO "p_name_cur_inout" is a little overloaded.

Date: 6/25/2009

Hi Stephen,
I recently changed employers and now have a different set of standards to live by.
However, I just wanted to make some notes on your document:

- Case: I hate the uppercase for Oracle stuff and lowercase for anything else.
I find it difficult to read. More important, if you use an IDE like TOAD or SQL Developer, all of the Oracle stuff is color coded...so there is no risk to misunderstand things.
I have only found two places that are uniform with lowercase, my previous employer and RedHat.
My new employer uses the upper/lower scenario + Camel case...I'm not too fond of that.

-Naming: For your table on page 4...I think that using cur_employees is better that using employees_cur. First you can quickly identify the type by the 3 letters. Second it is a "cursor based on the employee" not an employee based on a cursor.
So in general, I prefer the type as a prefix not as a suffix.
I know for example that Retek used parameters into functions/procedures like: P_IN_... P_OUT_...
I was not convinced but sill liked it better because of the prefix.

- Functions and procedures without arguments: I usually call them like this function_name;.
This tells me that the function or procedures does not take any argument.
Previous employer had this rule: procedure_name (); means that you can call the procedure with no arguments...but the procedure might use default arguments, so eventually you could call it with some arguments.

I find that standards can be extremely good for an organization, but I also believe that they need to be flexible.

Nelson

Date: 6/21/2009

Thanks Steven
I have been using (as most people I have seen) p_ as a prefix for all parameters. In fact it was the standard in Oracle Apps development team when I was a part of it.
I see your point in using _i, _io, _o though.
I'll probably try to go with a hybrid - p_[name]_io from now on, so that old programmers like me are still able to identlify parameters using _p :-)
Thanks, Srini