Skip to content
PL/SQL & SQL Coding Guidelines
Object Types
Initializing search
trivadis/plsql-and-sql-coding-guidelines
PL/SQL & SQL Coding Guidelines
trivadis/plsql-and-sql-coding-guidelines
About
Introduction
Naming Conventions
Coding Style
Language Usage
Language Usage
General
General
G-1010: Try to label your sub blocks.
G-1020: Always have a matching loop or block label.
G-1030: Avoid defining variables that are not used.
G-1040: Avoid dead code.
G-1050: Avoid using literals in your code.
G-1060: Avoid storing ROWIDs or UROWIDs in database tables.
G-1070: Avoid nesting comment blocks.
G-1080: Avoid using the same expression on both sides of a relational comparison operator or a logical operator.
Variables & Types
Variables & Types
General
General
G-2110: Try to use anchored declarations for variables, constants and types.
G-2120: Try to have a single location to define your types.
G-2130: Try to use subtypes for constructs used often in your code.
G-2135: Avoid assigning values to local variables that are not used by a subsequent statement.
G-2140: Never initialize variables with NULL.
G-2145: Never self-assign a variable.
G-2150: Avoid comparisons with NULL value, consider using IS [NOT] NULL.
G-2160: Avoid initializing variables using functions in the declaration section.
G-2170: Never overload variables.
G-2180: Never use quoted identifiers.
G-2185: Avoid using overly short names for explicitly or implicitly declared identifiers.
G-2190: Avoid using ROWID or UROWID.
Numeric Data Types
Numeric Data Types
G-2210: Avoid declaring NUMBER variables, constants or subtypes with no precision.
G-2220: Try to use PLS_INTEGER instead of NUMBER for arithmetic operations with integer values.
G-2230: Try to use SIMPLE_INTEGER datatype when appropriate.
Character Data Types
Character Data Types
G-2310: Avoid using CHAR data type.
G-2320: Never use VARCHAR data type.
G-2330: Never use zero-length strings to substitute NULL.
G-2340: Always define your VARCHAR2 variables using CHAR SEMANTIC (if not defined anchored).
Boolean Data Types
Boolean Data Types
G-2410: Try to use boolean data type for values with dual meaning.
Large Objects
Large Objects
G-2510: Avoid using the LONG and LONG RAW data types.
Cursor Variables
Cursor Variables
G-2610: Never use self-defined weak ref cursor types.
DML & SQL
DML & SQL
General
General
G-3110: Always specify the target columns when coding an insert statement.
G-3115: Avoid self-assigning a column.
G-3120: Always use table aliases when your SQL statement involves more than one source.
G-3130: Try to use ANSI SQL-92 join syntax.
G-3140: Try to use anchored records as targets for your cursors.
G-3145: Avoid using SELECT * directly from a table or view.
G-3150: Try to use identity columns for surrogate keys.
G-3160: Avoid visible virtual columns.
G-3170: Always use DEFAULT ON NULL declarations to assign default values to table columns if you refuse to store NULL values.
G-3180: Always specify column names instead of positional references in ORDER BY clauses.
G-3185: Never use ROWNUM at the same query level as ORDER BY.
G-3190: Avoid using NATURAL JOIN.
G-3195: Always use wildcards in a LIKE clause.
BULK Operations
BULK Operations
G-3210: Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement for more than 4 times.
G-3220: Always process saved exceptions from a FORALL statement.
Transaction Control
Transaction Control
G-3310: Never commit within a cursor loop.
G-3320: Try to move transactions within a non-cursor loop into procedures.
Control Structures
Control Structures
CURSOR
CURSOR
G-4110: Always use %NOTFOUND instead of NOT %FOUND to check whether a cursor returned data.
G-4120: Avoid using %NOTFOUND directly after the FETCH when working with BULK OPERATIONS and LIMIT clause.
G-4130: Always close locally opened cursors.
G-4140: Avoid executing any statements between a SQL operation and the usage of an implicit cursor attribute.
CASE / IF / DECODE / NVL / NVL2 / COALESCE
CASE / IF / DECODE / NVL / NVL2 / COALESCE
G-4210: Try to use CASE rather than an IF statement with multiple ELSIF paths.
G-4220: Try to use CASE rather than DECODE.
G-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.
G-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.
G-4250: Avoid using identical conditions in different branches of the same IF or CASE statement.
G-4260: Avoid inverting boolean conditions with NOT.
G-4270: Avoid comparing boolean values to boolean literals.
Flow Control
Flow Control
G-4310: Never use GOTO statements in your code.
G-4320: Always label your loops.
G-4325: Never reuse labels in inner scopes.
G-4330: Always use a CURSOR FOR loop to process the complete cursor results unless you are using bulk operations.
G-4340: Always use a NUMERIC FOR loop to process a dense array.
G-4350: Always use 1 as lower and COUNT() as upper bound when looping through a dense array.
G-4360: Always use a WHILE loop to process a loose array.
G-4365: Never use unconditional CONTINUE or EXIT in a loop.
G-4370: Avoid using EXIT to stop loop processing unless you are in a basic loop.
G-4375: Always use EXIT WHEN instead of an IF statement to exit from a loop.
G-4380: Try to label your EXIT WHEN statements.
G-4385: Never use a cursor for loop to check whether a cursor returns data.
G-4390: Avoid use of unreferenced FOR loop indexes.
G-4395: Avoid hard-coded upper or lower bound values with FOR loops.
Exception Handling
Exception Handling
G-5010: Try to use a error/logging framework for your application.
G-5020: Never handle unnamed exceptions using the error number.
G-5030: Never assign predefined exception names to user defined exceptions.
G-5040: Avoid use of WHEN OTHERS clause in an exception section without any other specific handlers.
G-5050: Avoid use of the RAISE_APPLICATION_ERROR built-in procedure with a hard-coded 20nnn error number or hard-coded message.
G-5060: Avoid unhandled exceptions.
G-5070: Avoid using Oracle predefined exceptions.
G-5080: Always use FORMAT_ERROR_BACKTRACE when using FORMAT_ERROR_STACK or SQLERRM.
Dynamic SQL
Dynamic SQL
G-6010: Always use a character variable to execute dynamic SQL.
G-6020: Try to use output bind arguments in the RETURNING INTO clause of dynamic DML statements rather than the USING clause.
Stored Objects
Stored Objects
General
General
G-7110: Try to use named notation when calling program units.
G-7120: Always add the name of the program unit to its end keyword.
G-7125: Always use CREATE OR REPLACE instead of CREATE alone.
G-7130: Always use parameters or pull in definitions rather than referencing external variables in a local program unit.
G-7140: Always ensure that locally defined procedures or functions are referenced.
G-7150: Try to remove unused parameters.
G-7160: Always explicitly state parameter mode.
G-7170: Avoid using an IN OUT parameter as IN or OUT only.
Packages
Packages
G-7210: Try to keep your packages small. Include only few procedures and functions that are used in the same context.
G-7220: Always use forward declaration for private functions and procedures.
G-7230: Avoid declaring global variables public.
G-7250: Never use RETURN in package initialization block.
Procedures
Procedures
G-7310: Avoid standalone procedures – put your procedures in packages.
G-7320: Avoid using RETURN statements in a PROCEDURE.
G-7330: Always assign values to OUT parameters.
Functions
Functions
G-7410: Avoid standalone functions – put your functions in packages.
G-7420: Always make the RETURN statement the last statement of your function.
G-7430: Try to use no more than one RETURN statement within a function.
G-7440: Never use OUT parameters to return values from a function.
G-7450: Never return a NULL value from a BOOLEAN function.
G-7460: Try to define your packaged/standalone function deterministic if appropriate.
Oracle Supplied Packages
Oracle Supplied Packages
G-7510: Always prefix Oracle supplied packages with owner schema name.
Object Types
Triggers
Triggers
G-7710: Avoid cascading triggers.
G-7720: Never use multiple UPDATE OF in trigger event clause.
G-7730: Avoid multiple DML events per trigger.
G-7740: Never handle multiple DML events per trigger if primary key is assigned in trigger.
Sequences
Sequences
G-7810: Never use SQL inside PL/SQL to read sequence numbers (or SYSDATE).
SQL Macros
SQL Macros
G-7910: Never use DML within a SQL macro.
Patterns
Patterns
Checking the Number of Rows
Checking the Number of Rows
G-8110: Never use SELECT COUNT(*) if you are only interested in the existence of a row.
G-8120: Never check existence of a row to decide whether to create it or not.
Access objects of foreign application schemas
Access objects of foreign application schemas
G-8210: Always use synonyms when accessing objects of another application schema.
Validating input parameter size
Validating input parameter size
G-8310: Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.
Ensure single execution at a time of a program unit
Ensure single execution at a time of a program unit
G-8410: Always use application locks to ensure a program unit is only running once at a given time.
Use dbms_application_info package to follow progress of a process
Use dbms_application_info package to follow progress of a process
G-8510: Always use dbms_application_info to track program process transiently.
Function Usage
Function Usage
G-9010: Always use a format model in string to date/time conversion functions.
G-9020: Try to use a format model and NLS_NUMERIC_CHARACTERS in string to number conversion functions.
G-9030: Try to define a default value on conversion errors.
G-9040: Try using FX in string to date/time conversion format model to avoid fuzzy conversion.
Complexity Analysis
Code Reviews
Tool Support
Tool Support
db* CODECOP for SQL Developer
db* CODECOP for SonarQube
db* CODECOP Command Line
db* CODECOP Validators
plscope-utils
Appendix
Object Types
There are no object type-specific recommendations to be defined at the time of writing.