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	Effi- ciency	Maintain- ability	Port- ability	Reli- ability	Reus- ability	Secu- rity	Test- ability
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.	Major		✘	✘
4	1040	Avoid dead code.	Major			✘
5	1050	Avoid using literals in your code.	Minor	✘
6	1060	Avoid storing ROWIDs or UROWIDs in database tables.	Blocker					✘
7	1070	Avoid nesting comment blocks.	Minor			✘
n/a	1080	Avoid using the same expression on both sides of a relational comparison operator or a logical operator.	Blocker		✘	✘					✘
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	✘
n/a	2135	Avoid assigning values to local variables that are not used by a subsequent statement.	Major		✘	✘					✘
11	2140	Never initialize variables with NULL.	Minor			✘
n/a	2145	Never self-assign a variable.	Blocker			✘
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.	Major			✘
17	2190	Avoid using ROWID or UROWID.	Blocker				✘	✘
18	2210	Avoid declaring NUMBER variables, constants or subtypes with no precision.	Critical		✘
19	2220	Try to use PLS_INTEGER instead of NUMBER for arithmetic operations with integer values.	Critical		✘
n/a	2230	Try to use SIMPLE_INTEGER datatype when appropriate.	Critical		✘
20	2310	Avoid using CHAR data type.	Blocker					✘
21	2320	Never use VARCHAR data type.	Blocker				✘	✘
22	2330	Never use zero-length strings to substitute NULL.	Blocker				✘	✘
23	2340	Always define your VARCHAR2 variables using CHAR SEMANTIC (if not defined anchored).	Blocker					✘
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				✘
n/a	2610	Never use self-defined weak ref cursor types.	Minor	✘		✘	✘		✘
26	3110	Always specify the target columns when coding an insert statement.	Blocker			✘		✘
n/a	3115	Avoid self-assigning a column.	Blocker			✘
27	3120	Always use table aliases when your SQL statement involves more than one source.	Blocker			✘
28	3130	Try to use ANSI SQL-92 join syntax.	Major			✘	✘
29	3140	Try to use anchored records as targets for your cursors.	Major			✘		✘
n/a	3145	Avoid using SELECT * directly from a table or view.	Blocker		✘	✘		✘			✘
n/a	3150	Try to use identity columns for surrogate keys.	Critical			✘		✘
n/a	3160	Avoid visible virtual columns.	Blocker			✘		✘
n/a	3170	Always use DEFAULT ON NULL declarations to assign default values to table columns if you refuse to store NULL values.	Blocker					✘
n/a	3180	Always specify column names instead of positional references in ORDER BY clauses.	Major	✘				✘
n/a	3182	Always specify column names instead of positional references in GROUP BY clauses.	Blocker					✘
n/a	3183	Always specify column aliases instead of expressions in GROUP BY clauses.	Minor			✘
n/a	3185	Never use ROWNUM at the same query level as ORDER BY.	Blocker					✘			✘
n/a	3190	Avoid using NATURAL JOIN.	Blocker	✘				✘
n/a	3195	Always use wildcards in a LIKE clause.	Blocker			✘
30	3210	Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement for more than 4 times.	Critical		✘
n/a	3220	Always process saved exceptions from a FORALL statement.	Critical					✘			✘
n/a	3310	Never commit within a cursor loop.	Blocker		✘			✘
n/a	3320	Try to move transactions within a non-cursor loop into procedures.	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.	Blocker					✘
33	4130	Always close locally opened cursors.	Blocker		✘			✘
34	4140	Avoid executing any statements between a SQL operation and the usage of an implicit cursor attribute.	Blocker					✘
35	4210	Try to use CASE rather than an IF statement with multiple ELSIF paths.	Minor			✘					✘
36	4220	Try to use CASE rather than DECODE.	Major			✘	✘
37	4230	Always use a COALESCE instead of a NVL command, if parameter 2 of the NVL function is a function call or a SELECT statement.	Critical		✘			✘
38	4240	Always use a CASE instead of a NVL2 command if parameter 2 or 3 of NVL2 is either a function call or a SELECT statement.	Critical		✘			✘
n/a	4250	Avoid using identical conditions in different branches of the same IF or CASE statement.	Blocker			✘		✘			✘
n/a	4260	Avoid inverting boolean conditions with NOT.	Minor			✘					✘
n/a	4270	Avoid comparing boolean values to boolean literals.	Minor			✘					✘
39	4310	Never use GOTO statements in your code.	Major			✘					✘
40	4320	Always label your loops.	Minor			✘
n/a	4325	Never reuse labels in inner scopes.	Major			✘		✘			✘
41	4330	Always use a CURSOR FOR loop to process the complete cursor results unless you are using bulk operations.	Major			✘
42	4340	Always use a NUMERIC FOR loop to process a dense array.	Major			✘
43	4350	Always use 1 as lower and COUNT() as upper bound when looping through a dense array.	Blocker					✘
44	4360	Always use a WHILE loop to process a loose array.	Critical		✘
n/a	4365	Never use unconditional CONTINUE or EXIT in a loop.	Major			✘					✘
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.	Critical		✘
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.	Critical					✘
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.	Blocker					✘
n/a	5080	Always use FORMAT_ERROR_BACKTRACE when using FORMAT_ERROR_STACK or SQLERRM.	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			✘
n/a	7125	Always use CREATE OR REPLACE instead of CREATE alone.	Major			✘
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.	Major		✘	✘
68	7160	Always explicitly state parameter mode.	Major			✘
n/a	7170	Avoid using an IN OUT parameter as IN or OUT only.	Major		✘	✘
65	7210	Try to keep your packages small. Include only few procedures and functions that are used in the same context.	Major		✘	✘
66	7220	Always use forward declaration for private functions and procedures.	Minor	✘
67	7230	Avoid declaring global variables public.	Major					✘
n/a	7250	Never use RETURN in package initialization block.	Major			✘
69	7310	Avoid standalone procedures – put your procedures in packages.	Minor			✘
70	7320	Avoid using RETURN statements in a PROCEDURE.	Major			✘					✘
n/a	7330	Always assign values to OUT parameters.	Blocker			✘					✘
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.	Blocker					✘			✘
n/a	7460	Try to define your packaged/standalone function deterministic if appropriate.	Major		✘
76	7510	Always prefix Oracle supplied packages with owner schema name.	Major							✘
77	7710	Avoid cascading triggers.	Major			✘					✘
n/a	7720	Never use multiple UPDATE OF in trigger event clause.	Blocker			✘		✘			✘
n/a	7730	Avoid multiple DML events per trigger.	Minor			✘					✘
n/a	7740	Never handle multiple DML events per trigger if primary key is assigned in trigger.	Critical		✘			✘
n/a	7810	Never use SQL inside PL/SQL to read sequence numbers (or SYSDATE).	Critical		✘	✘
n/a	7910	Never use DML within a SQL macro.	Blocker					✘			✘
78	8110	Never use SELECT COUNT(*) if you are only interested in the existence of a row.	Critical		✘
n/a	8120	Never check existence of a row to decide whether to create it or not.	Critical		✘			✘
79	8210	Always use synonyms when accessing objects of another application schema.	Minor	✘		✘
n/a	8310	Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.	Major			✘		✘	✘		✘
n/a	8410	Always use application locks to ensure a program unit is only running once at a given time.	Blocker		✘			✘
n/a	8510	Always use dbms_application_info to track program process transiently.	Critical		✘			✘
n/a	9010	Always use a format model in string to date/time conversion functions.	Blocker	✘		✘		✘		✘	✘
n/a	9020	Try to use a format model and NLS_NUMERIC_CHARACTERS in string to number conversion functions.	Blocker	✘		✘		✘		✘	✘
n/a	9030	Try to define a default value on conversion errors.	Major			✘		✘			✘
n/a	9040	Try using FX in string to date/time conversion format model to avoid fuzzy conversion.	Blocker					✘			✘