Naming and Coding Conventions for SQL and PL/SQL

I have had a look through the document and it is mainly concerned with making code neat and maintainable and NOT with writing efficient code. Although the content is commendable and well intended, I have never actually encountered a site where such an approach succeeds in practice.

Why have standards?



Reference :

• a discussion on the OTN forums

Anonymous PL/SQL Blocks versus Stored Procedures

A stored procedure:

Is created and stored in the database as a schema object. Once created and compiled, it is a named object that can be executed without recompiling. Additionally, dependency information is stored in the data dictionary to guarantee the validity of each stored procedure.

Anonymous PL/SQL block:

As an alternative to a stored procedure, you can create an anonymous PL/SQL block by sending an unnamed PL/SQL block to the Oracle server from an Oracle tool or an application. Oracle compiles the PL/SQL block and places the compiled version in the shared pool of the SGA, but does not store the source code or compiled version in the database for reuse beyond the current instance. Shared SQL allows anonymous PL/SQL blocks in the shared pool to be reused and shared until they are flushed out of the shared pool.

In either case, moving PL/SQL blocks out of a database application and into database procedures stored either in the database or in memory, you avoid unnecessary procedure recompilation by Oracle at run-time  improving the overall performance of the application and Oracle.