Oracle PL/SQL Best Practices

Name

STYL-10: Adopt meaningful naming conventions for source files.

Synopsis

This is a “meta-code” style issue. You should define a standard for the way you name the operating system files that contain your source code (some organizations now store and edit source code entirely in the database, but they are still in the minority). These files can contain many different kinds of “code”:

• DDL definitions of data structures (tables, indexes, GRANT statements, etc.)

• SQL*Plus scripts that contain a variety of anonymous blocks and standalone SQL statements, as well as SQL*Plus formatting /control commands

• PL/SQL program definitions

You need to be careful of how you organize the code in your files. Otherwise, you will end up with a plethora of files in a mish-mash of subdirectories. Your development team will have a hard time figuring out where anything is, and what all those files are supposed to do.

You should also be deliberate in how you name those files, including their extensions. There is a strong tendency in the Oracle world to use the .sql extension for all files. Why? Because .sql is the default extension of SQL*Plus: if you don’t specify an extension, that tool automatically looks for a .sql file. Laziness, however, is a poor excuse for a naming convention; by relying on a single extension, you forego valuable “real estate” in that filename.

Here are some recommendations for file-usage conventions:

• Use separate files for each distinct program or package. Don’t jumble a bunch of stuff ...