Coding standards for PLSQL

Question:
I would like to follow standards as I write my PL/SQL programs. Can you suggest coding standards for the Oracle PL/SQL language? Does Oracle have a set of recommended standards?
Answer:
The term "coding standards" usually refers to any/all of the following:
• Physical layout of code: How many spaces do you indent? Do you put each parameter on a new line or put as many as possible on the same line? Do you upper-case all keywords? Having a standard style makes your code more readable, especially when that format is common across an entire development group.
• Guidelines for best practices: The most important aspect of standards, these guidelines help people write higher quality code that is easier to maintain and runs more efficiently.
• Naming conventions: What standards do you follow in the way you name your programs, variables, etc.? Should you put a prefix in front of local variables, include information about the mode of a parameter in a parameter's name, etc.?
Oracle does not itself publish a set of coding standards and, in fact, no single style of coding has been adopted within the world of PL/SQL developers. Oracle does, however, offer many recommendations for the best way to write PL/SQL applications. These recommendations may be found in the Oracle PL/SQL User Guide and Reference.
I offer some comments on each aspect of coding standards below:
Physical layout of code. I strongly urge you to not seek out nor write a document that prescribes a standard format, to which you then expect developers to conform. Given the current state of IDEs like Toad, SQL*Navigator, and PL/SQL Developer (to name just the three most popular of these tools), such a document is entirely unnecessary—and a big waste of time.
These tools offer code formatters, also known as "pretty-printers" and "beautifiers." Just define your standard format through the tool, and then reformat your code whenever needed. Some of the tools will automatically reformat code when read in from file or before it is compiled into the database. Automatic formatters allow each of us individually to work with our own, favorite format, and then transform the code into the group standard before it moves to production.
Guidelines for best practices. We have devoted this entire project to, more or less, offering such guidelines, so I hope you will not be too surprised that I do not answer that question in detail here. Instead, I encourage you to spend some time looking through answers to various questions on this column. Beyond that, however, there does just happen to be a whole book titled Oracle PL/SQL Best Practices, published by O'Reilly Media, that you may find helpful in this regard.
I wrote it several years ago, and although it has not been updated since Oracle8i Database, most of the recommendations are still completely valid. It is a concise book that offers best practices in each feature area of the language. It also contains a quick reference card of all the best practices.
Naming conventions. Setting up and following consistent naming conventions will make a big difference in the readability and maintainability of your code. As to which set of conventions you should follow, well, I think that I would be on safer ground by saying you should simply choose one that makes sense to you. Developers often get very dogmatic about naming conventions, and I don't care to take sides. Actually, I don't think it really matters very much which prefixes and suffixes you use, as long as you are consistent in your application of your choices.
And I remain hopeful that someday we will be able to choose inside our IDE from among "canned" naming conventions, and then have the tool automatically name our elements to conform to the convention. (Probably just a fantasy...)
By the way, the download that accompanies the Oracle PL/SQL Best Practices book contains a document that offers a set of naming conventions, which you are welcome to examine, modify to your preferences, and use. When you arrive at that page, click on "Examples", then click on the examples.zip link. The standards.zip file inside the main zip for the book contains the HTML documents describing a coding standard.