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. | 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 | ✘ | |||||||
n/a | 1080 | Avoid using the same expression on both sides of a relational comparison operator or a logical operator. | 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 | ✘ | |||||||
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. | 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 using ROWID or UROWID. | Major | ✘ | ✘ | ||||||
18 | 2210 | Avoid declaring NUMBER variables, constants 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 | Never use 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 | ✘ | |||||||
n/a | 2610 | Never use self-defined weak ref cursor types. | Minor | ✘ | ✘ | ✘ | ✘ | ||||
26 | 3110 | Always specify the target columns when coding an insert statement. | Major | ✘ | ✘ | ||||||
n/a | 3115 | Avoid self-assigning a column. | Minor | ✘ | |||||||
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 | 3145 | Avoid using SELECT * directly from a table or view. | Major | ✘ | ✘ | ✘ | ✘ | ||||
n/a | 3150 | Try to use identity columns for surrogate keys. | Minor | ✘ | ✘ | ||||||
n/a | 3160 | Avoid visible virtual columns. | 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 | 3185 | Never use ROWNUM at the same query level as ORDER BY. | Major | ✘ | ✘ | ||||||
n/a | 3190 | Avoid using NATURAL JOIN. | Major | ✘ | ✘ | ||||||
n/a | 3195 | Always use wildcards in a LIKE clause. | Minor | ✘ | |||||||
30 | 3210 | Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement for more than 4 times. | Major | ✘ | |||||||
n/a | 3220 | Always process saved exceptions from a FORALL statement. | Major | ✘ | ✘ | ||||||
n/a | 3310 | Never commit within a cursor loop. | Critical | ✘ | ✘ | ||||||
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. | 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 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. | Major | ✘ | ✘ | ✘ | |||||
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. | 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 | ✘ | |||||||
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. | 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 | ✘ | |||||||
n/a | 5080 | Always use FORMAT_ERROR_BACKTRACE when using FORMAT_ERROR_STACK or SQLERRM. | Minor | ✘ | ✘ | ||||||
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. | 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 | ✘ | ✘ | ||||||
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. | Minor | ✘ | ✘ | ||||||
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. | Minor | ✘ | |||||||
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. | 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 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. | Major | ✘ | ✘ | ||||||
n/a | 7810 | Never use SQL inside PL/SQL to read sequence numbers (or SYSDATE). | Major | ✘ | ✘ | ||||||
n/a | 7910 | Never use DML within a SQL macro. | Critical | ✘ | ✘ | ||||||
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 is only running once at a given time. | Minor | ✘ | ✘ | ||||||
n/a | 8510 | Always use dbms_application_info to track program process transiently. | Minor | ✘ | ✘ | ||||||
n/a | 9010 | Always use a format model in string to date/time conversion functions. | Major | ✘ | ✘ | ✘ | ✘ | ✘ | |||
n/a | 9020 | Try to use a format model and NLS_NUMERIC_CHARACTERS in string to number conversion functions. | Major | ✘ | ✘ | ✘ | ✘ | ✘ | |||
n/a | 9030 | Try to define a default value on conversion errors. | Minor | ✘ | ✘ | ✘ | |||||
n/a | 9040 | Try using FX in string to date/time conversion format model to avoid fuzzy conversion. | Minor | ✘ | ✘ |