This post will be quite short and simply describe the conventions that I follow in my code. I fully realise that many different views abound and that coding standards often create intense discussion. The main reason for this post is not to promote my conventions but to simply note them for anyone reading code from this site.
I have yet to find a standard that I consider totally acceptable, which is why you may find me not abiding by my self-imposed standards.
Schema Naming Conventions
<table_name>_pk denotes both the primary key index and primary key constraint on table <table_name>
<table_name>_i<n> denotes an index on <table_name>, where <n> is a simple integer
<table_name>_uk<n> denotes a unique index (other than the primary key) as well as unique constraint on <table_name>, where <n> is a simple integer
<table_name>_fk<n> denotes a check constraint on <table_name>, where <n> is a simple integer
<table_name>_ch<n> denotes a check constraint on <table_name>, where <n> is a simple integer
I don’t have any specific naming convention for database tables, views and materialised views other than to use a name that reflects the data content.
PL/SQL
Naming Conventions
I tend to keep things fairly simple here:
t_ prefix denotes a type definition and I don’t differentiate between PL/SQL types and schema types
c_ prefix denotes a constant, whether locally declared or package globally
g_ prefix denotes a package level, global variable
l_ prefix denotes a local routine variable
p_ prefix denotes a routine parameter and I don’t differentiate between IN, OUT and IN OUT parameters
_cur suffix denotes a cursor, used for both type declarations and variables
_rec suffix denotes a record, used for both type declarations and variables
_tab suffix denotes a collection, used for both type declarations and variables (associative arrays, nested tables & varrays)
Upper & Lower Case
I use upper case for reserved words, built-in function names and other Oracle defined identifiers. Lower case for everything else, including Oracle’s own package names.
Indentation
I use 3 spaces for indentation. Tab characters are avoided due to the formatting problems with different editors.
Other
For lists that comma separated, such as the columns in a CREATE TABLE statement, the SELECT list of a query and the parameters of a routine, I place the comma at the start of the line rather than at the end, only one entry per line.
Mark Hoxey 