PL/SQL keywords Best Practices Standards Oracle Tips by Jonathan Ingram

PL/SQL Capitalization Best Practices Standards

The Table below contains a list of keywords that should always be fully capitalized when referenced in code. Some of these keywords are commonly used reserved words; reserved words that do not appear on this list should be capitalized as well.

Capitalize these Oracle keywords.   ALL FALSE MINUS ROWTYPE AND FETCH NOT SELECT AS FOR NOTFOUND SET BEGIN FOUND NULL SQLCODE BETWEEN FROM OPEN SQLERRM BODY FUNCTION OR TABLE CLOSE GOTO ORDER BY THEN COMMIT GROUP BY OUT TYPE CONSTANT HAVING PACKAGE UNION CREATE IF PROCEDURE UNION ALL DECLARE IN RAISE UPDATE DELETE INSERT REPLACE VALUES ELSE INTERSECT RETURN VIEW ELSIF INTO ROLLBACK WHEN END IS ROWCOUNT WHERE EXCEPTION LIKE ROWID WHILE EXIT LOOP ROWNUM

The keyword REPLACE is to be used in uppercase only when used as part of the CREATE OR REPLACE clause that is used to create a stored PL/SQL object. Calls to the SQL function replace() should not be presented in uppercase.

In addition to the keywords presented in Table D.2, fully capitalize all of the following:

?  The names of all standard exceptions (NO_DATA_FOUND, OTHERS, TOO_MANY_ROWS), and all user-defined exceptions.

?  The names of all constants and all user-defined datatypes.

?  All acronyms (ANSI, ASCII, HUD, NASA, NOAA, YMCA, and so forth).

?  The names of all tables, snapshots, and views, as well as the aliases given to these objects in queries.

?  The names of all database triggers.

Use mixed case to refer to the names of user-defined procedures and functions (functions provided by SQL*Plus and PL/SQL are still referenced in lower case). For example:

Calculate_GPA

DBMS_Output.Put_Line

Optionally, use mixed case for user-defined identifiers. If you choose this method, use capital letters to help make the identifier names more meaningful by visually breaking variable names into words; here are some examples:

vString

nBaseSalary

nGPA

iTardyDays

iClassNumber

lComments

rStudentPhoto

All text not handled by these rules should use lowercase. Consider the following:

CREATE OR REPLACE PACKAGE My_Sample_Package AS

PROCEDURE My_Sample_Procedure (nParameter1 IN     number,

                               nParameter2    OUT number)

IS

   YES   CONSTANT   char (1) := 'Y';

BEGIN

   IF (some expression) THEN

      replace (vString, chr (9), '   ');

   END IF;

END My_Sample_Procedure;

END My_Sample_Package;

---

This is an excerpt from "High Performance Oracle Database Automation", by Jonathan Ingram and Donald K. Burleson, Series Editor.
 

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.