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.

Trivadis PL/SQL & SQl Coding Guidelines in PDF format

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