Appendix

A - PL/SQL & SQL Coding Guidelines as PDF

These guidelines are primarily produced in HTML using Material for MkDocs.

However, we provide these guidelines also as PDF produced by wkhtmltopdf.

The formatting is not perfect, but it should be adequate for those who want to work with offline documents.

B - Mapping new guidelines to prior versions

Old Id	New Id	Text	Severity	Change-ability	Efficiency	Maintain-ability	Portability	Reliability	Reusability	Security	Testability
1	1010	Try to label your sub blocks.	Minor			✘
2	1020	Always have a matching loop or block label.	Minor			✘
3	1030	Avoid defining variables that are not used.	Minor		✘	✘
4	1040	Avoid dead code.	Minor			✘
5	1050	Avoid using literals in your code.	Minor	✘
6	1060	Avoid storing ROWIDs or UROWIDs in database tables.	Major					✘
7	1070	Avoid nesting comment blocks.	Minor			✘
8	2110	Try to use anchored declarations for variables, constants and types.	Major			✘		✘
9	2120	Try to have a single location to define your types.	Minor	✘
10	2130	Try to use subtypes for constructs used often in your code.	Minor	✘
11	2140	Never initialize variables with NULL.	Minor			✘
12	2150	Avoid comparisons with NULL value, consider using IS [NOT] NULL.	Blocker				✘	✘
13	2160	Avoid initializing variables using functions in the declaration section.	Critical					✘
14	2170	Never overload variables.	Major					✘
15	2180	Never use quoted identifiers.	Major			✘
16	2185	Avoid using overly short names for explicitly or implicitly declared identifiers.	Minor			✘
17	2190	Avoid the use of ROWID or UROWID.	Major				✘	✘
18	2210	Avoid declaring NUMBER variables or subtypes with no precision.	Minor		✘
19	2220	Try to use PLS_INTEGER instead of NUMBER for arithmetic operations with integer values.	Minor		✘
n/a	2230	Try to use SIMPLE_INTEGER datatype when appropriate.	Minor		✘
20	2310	Avoid using CHAR data type.	Major					✘
21	2320	Avoid using VARCHAR data type.	Major				✘
22	2330	Never use zero-length strings to substitute NULL.	Major				✘
23	2340	Always define your VARCHAR2 variables using CHAR SEMANTIC (if not defined anchored).	Minor					✘
24	2410	Try to use boolean data type for values with dual meaning.	Minor			✘
25	2510	Avoid using the LONG and LONG RAW data types.	Major				✘
26	3110	Always specify the target columns when coding an insert statement.	Major			✘		✘
27	3120	Always use table aliases when your SQL statement involves more than one source.	Major			✘
28	3130	Try to use ANSI SQL-92 join syntax.	Minor			✘	✘
29	3140	Try to use anchored records as targets for your cursors.	Major			✘		✘
n/a	3150	Try to use identity columns for surrogate keys.	Minor			✘		✘
n/a	3160	Avoid virtual columns to be visible.	Major			✘		✘
n/a	3170	Always use DEFAULT ON NULL declarations to assign default values to table columns if you refuse to store NULL values.	Major					✘
n/a	3180	Always specify column names instead of positional references in ORDER BY clauses.	Major	✘				✘
n/a	3190	Avoid using NATURAL JOIN.	Major	✘				✘
30	3210	Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement more than 4 times.	Major		✘
31	4110	Always use %NOTFOUND instead of NOT %FOUND to check whether a cursor returned data.	Minor			✘
32	4120	Avoid using %NOTFOUND directly after the FETCH when working with BULK OPERATIONS and LIMIT clause.	Critical					✘
33	4130	Always close locally opened cursors.	Major		✘			✘
34	4140	Avoid executing any statements between a SQL operation and the usage of an implicit cursor attribute.	Major					✘
35	4210	Try to use CASE rather than an IF statement with multiple ELSIF paths.	Major			✘					✘
36	4220	Try to use CASE rather than DECODE.	Minor			✘	✘
37	4230	Always use COALESCE instead of NVL, if parameter 2 of the NVL function is a function call or a SELECT statement.	Critical		✘			✘
38	4240	Always use CASE instead of NVL2 if parameter 2 or 3 of NVL2 is either a function call or a SELECT statement.	Critical		✘			✘
39	4310	Never use GOTO statements in your code.	Major			✘					✘
40	4320	Always label your loops.	Minor			✘
41	4330	Always use a CURSOR FOR loop to process the complete cursor results unless you are using bulk operations.	Minor			✘
42	4340	Always use a NUMERIC FOR loop to process a dense array.	Minor			✘
43	4350	Always use 1 as lower and COUNT() as upper bound when looping through a dense array.	Major					✘
44	4360	Always use a WHILE loop to process a loose array.	Minor		✘
45	4370	Avoid using EXIT to stop loop processing unless you are in a basic loop.	Major			✘
46	4375	Always use EXIT WHEN instead of an IF statement to exit from a loop.	Minor			✘
47	4380	Try to label your EXIT WHEN statements.	Minor			✘
48	4385	Never use a cursor for loop to check whether a cursor returns data.	Major		✘
49	4390	Avoid use of unreferenced FOR loop indexes.	Major		✘
50	4395	Avoid hard-coded upper or lower bound values with FOR loops.	Minor	✘		✘
n/a	5010	Try to use a error/logging framework for your application.	Critical					✘	✘		✘
51	5020	Never handle unnamed exceptions using the error number.	Critical			✘
52	5030	Never assign predefined exception names to user defined exceptions.	Blocker					✘			✘
53	5040	Avoid use of WHEN OTHERS clause in an exception section without any other specific handlers.	Major					✘
54	n/a	Avoid use of EXCEPTION_INIT pragma for a 20nnn error.	Major					✘
55	5050	Avoid use of the RAISE_APPLICATION_ERROR built-in procedure with a hard-coded 20nnn error number or hard-coded message.	Major	✘		✘
56	5060	Avoid unhandled exceptions	Major					✘
57	5070	Avoid using Oracle predefined exceptions	Critical					✘
58	6010	Always use a character variable to execute dynamic SQL.	Major			✘					✘
59	6020	Try to use output bind arguments in the RETURNING INTO clause of dynamic DML statements rather than the USING clause.	Minor			✘
60	7110	Try to use named notation when calling program units.	Major	✘		✘
61	7120	Always add the name of the program unit to its end keyword.	Minor			✘
62	7130	Always use parameters or pull in definitions rather than referencing external variables in a local program unit.	Major			✘		✘			✘
63	7140	Always ensure that locally defined procedures or functions are referenced.	Major			✘		✘
64	7150	Try to remove unused parameters.	Minor		✘	✘
65	7210	Try to keep your packages small. Include only few procedures and functions that are used in the same context.	Minor		✘	✘
66	7220	Always use forward declaration for private functions and procedures.	Minor	✘
67	7230	Avoid declaring global variables public.	Major					✘
68	7240	Avoid using an IN OUT parameter as IN or OUT only.	Major		✘	✘
69	7310	Avoid standalone procedures – put your procedures in packages.	Minor			✘
70	7320	Avoid using RETURN statements in a PROCEDURE.	Major			✘					✘
71	7410	Avoid standalone functions – put your functions in packages.	Minor			✘
73	7420	Always make the RETURN statement the last statement of your function.	Major			✘
72	7430	Try to use no more than one RETURN statement within a function.	Major			✘					✘
74	7440	Never use OUT parameters to return values from a function.	Major						✘
75	7450	Never return a NULL value from a BOOLEAN function.	Major					✘			✘
n/a	7460	Try to define your packaged/standalone function to be deterministic if appropriate.	Major		✘
76	7510	Always prefix ORACLE supplied packages with owner schema name.	Major							✘
77	7710	Avoid cascading triggers.	Major			✘					✘
n/a	7810	Do not use SQL inside PL/SQL to read sequence numbers (or SYSDATE)	Major		✘	✘
78	8110	Never use SELECT COUNT(*) if you are only interested in the existence of a row.	Major		✘
n/a	8120	Never check existence of a row to decide whether to create it or not.	Major		✘			✘
79	8210	Always use synonyms when accessing objects of another application schema.	Major	✘		✘
n/a	8310	Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.	Minor			✘		✘	✘		✘
n/a	8410	Always use application locks to ensure a program unit only running once at a given time.	Minor		✘			✘
n/a	8510	Always use dbms_application_info to track program process transiently	Minor		✘			✘