Best Practices PL/SQL (1)

There are several documents and also a book by Steven (with 2 editions that have some differences) about Best and Bad practices on PL/SQL.

The major contributions are from that book and recently from Gojko

I will in this topic show my best practices.
For now I will present then with no specific order (for instance importance), but maybe later I will compile then in some order.



Use an IDE
Avoid: Notepad or similar use
Use: there are several commercial products (TOAD and SQL Navaigator from Quest and PL/SQL Developer from Allround Automations, Dreamcoder for oracle, etc.) and freeware versions (JDeveloper and SQL Developer from Oracle,TOra, etc.)


Named conventions
A named convention should be used and enforced to the all team.
Importance: Improve readability
There is no standard!!! :-(
There some completely different visions (Bryn's, Steven's, for instance).
But, pick a coding style you like and that the IDE supports and stick to it!
Here are some links to Database and PL/SQL naming conventions:
http://www.oracle-base.com/articles/misc/NamingConventions.php
http://alexle.net/archives/177
http://www.csse.monash.edu.au/~zsamar/db/sqlnaming.html
http://www.dba-oracle.com/t_plsql_capitalization_standards.htm
http://www.dba-oracle.com/t_plsql_indentation_standards.htm
http://nal-ir.nal.res.in/1323/01/tr_pd_im_0402.pdf
http://apex.oracle.com/pls/otn/f?p=2853:4:8299594980553275::NO::P4_QA_ID:3262
http://www.williamrobertson.net/documents/plsqlcodingstandards.html
http://weblogs.asp.net/jamauss/articles/DatabaseNamingConventions.aspx
From OReview
Bryn Llewellyn, PL/SQL Product Manager Thoughts on Standards
From Oracle PL/SQL Programming, 2nd Edition
From Oracle PL/SQL Language Pocket Reference, Fourth Edition
From High Performance Oracle Database Automation
From Red Hat Web Application Framework Developer Guide
Red Hat Web Application Framework 6.0: WAF Developer's Guide
Actualização (june 2009):Steven's Naming Conventions and Coding Standards

Later I will present my coding standard.

With 11g you be able to have an automatic tool to help you to enforce (at least to some point) the naming convention.


Format the Code
A format style should be used and enforced to the all team.
Importance: Improve readability
Most IDEs has some feature to automatically format the code.
SQL Navigator and TOAD (from Quest Software) has an option to format the code and you can save the configuration style and share with your team. The formatter tool used by quest is third party and has some problems: cannot format code that has for instance MEMBER OF. Quest is developing a new tool.
SQL developer has in the new version also a formatter.

Named Notation
Procedures and functions should always be called using named notation for their parameters.
Importance: Avoid unexpected results, improve readability
Avoid:

htp.tabledata('Some data',
'left',
null,
null,
2,
null,
'class="claro"');

Use:

htp.tabledata(cvalue=>'Some data',
calign=>'left',
crowspan=>2,
cattributes=>'class="claro"');

This prevents that a change in the parameters order that was not notified to the group to invalidate its dependencies.
If a parameters changed its meaning but not its datatype no invalidation occurs and that could led to very unexpected results and its hard to debug.
In 11g those calls to functions (using named notation) are fully supported :-) (even mixed notation).

Calling Procedures with no parameters
Calling Procedures with no parameters should always have () after the procedure name.
Importance: Improve readability
Avoid: htp.para;
Use: htp.para();


I will continue this topic...