PL/SQL Coding conventions for the Castor databases

Purpose

This page explains the PL/SQL coding conventions agreed amongst the developers. The naming conventions detailed here are required to ease the operation of creating upgrade scripts, while other conventions are recommendations and hints to improve readability. Quoting Dennis:

If you do not name these constraints, then Oracle will do so automatically with a system generated name (SELECT * FROM user_cons_constraints). These SYS_ generated names make it extremely difficult to create future upgrade scripts. As the process for doing so requires diff'ing a reference database to an upgraded database and comparing the difference. As the SYS_ generated names are different across database the diff tool always finds inconsistencies.

Different tools exist on the web to pretty print SQL statements. See e.g. http://www.peterbe.com/plog/blogitem-040806-1/test-printsql.

Conventions

1. Name all [PRIMARY|FOREIGN] KEYS and NOT NULL constraints as follows:

 [PK|FK|NN]_<table_name>_<column_name>

If you get an Oracle error about the identifier being too long, then you need to either abbreviate the table_name (e.g. s/internalDiskCopy/InternalDC/) or truncate it so that the identifier name does not exceed 30 characters in length.

2. Name INDEXES according to their type. For indexes on a primary keys, the format is:

  PK_<table_name>_<column_name>

  UN_<table_name>_<column_name>

  I_<table_name>_<column_name>

3. All MATERIALIZED VIEWS should be post fixed with _MV.

4. All ORACLE keywords should be in upper-case. Note: for aggregation keywords, count, max, avg etc. upper-case or lower-case is accepted. For whichever is chosen it should be consistent throughout the schema.

5. Code indentation is done with 2 spaces. Please do avoid tabs.

6. When referencing:

• user defined tables that do not have an '_' in the name the convention is to have mixed cased names with the first character in uppercase e.g. ConfigSchema not configSchema or configschema.
• user defined fields follow the same convention, with the first character in lowercase.

7. Keywords should be stacked and aligned to the right if the line exceeds 80 characters. For example:

   SELECT * FROM DLF_Messages WHERE id = :1 AND severity = 8 AND msg_no = 57 AND facility = 22;

   SELECT *
     FROM dlf_messages
    WHERE id = :1
      AND severity = 8  -- System
      AND msg_no = 57  -- Triggering Tape Recall
      AND facility = 22;  -- Stager

8. In comma separated lists, there is no space before the comma but there is one after. For example, blah1,blah2 ,blah3 becomes blah1, blah2, blah3.

9. Procedure's parameters and local variables should be indicated as such as varMyData, inMyData outMyData, inoutMyData so that the use of the variable is made obvious upon reading.

10. When doing a SELECT FOR UPDATE INTO a variable which will never be used again, think of calling it varUnused.

11. All column names within a DML statement should be fully qualified, either with the name of the table or with an alias of that table. For example the following:

 SELECT id
   INTO varCastorFile
   FROM CastorFile
  WHERE fileId = 12345;

Should be written as:

SELECT CastorFile.id
  INTO varCastorFile
  FROM CastorFile
 WHERE CastorFile.fileId = 12345;

Or with a table alias as:

SELECT C.id
  INTO varCastorFile
  FROM CastorFile C
 WHERE C.fileId = 12345;

-- GiuseppeLoPresti - 11 Mar 2009 -- EricCano - 17-Aug-2010