Skip to content
PL/SQL & SQL Coding Guidelines - Version 1.0
G-7250: Always use NOCOPY when appropriate
Type to start searching
insum-labs/plsql-and-sql-coding-guidelines
PL/SQL & SQL Coding Guidelines
insum-labs/plsql-and-sql-coding-guidelines
Introduction to the Insum PL/SQL and SQL Coding Guidelines
Document Conventions
Naming Conventions
Coding Style
Language Usage
Language Usage
General
General
G-1010: Try to label your sub blocks.
G-1020: Have a matching loop or block label.
G-1030: Avoid defining variables that are not used.
G-1040: Always 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.
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-2140: Never initialize variables with NULL.
G-2150: Never use comparisons with NULL values, use 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-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: Avoid using 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.
DML & SQL
DML & SQL
General
General
G-3110: Always specify the target columns when coding an insert statement.
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-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-3190: Avoid using NATURAL JOIN.
G-3200: Never use an ON clause when USING will work.
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.
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.
Flow Control
Flow Control
G-4310: Never use GOTO statements in your code.
G-4320: Always label your loops.
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-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: Always use an 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.
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-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.
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-7240: Avoid using an IN OUT parameter as IN or OUT only.
G-7250: Always use NOCOPY when appropriate
Procedures
Procedures
G-7310: Avoid standalone procedures – put your procedures in packages.
G-7320: Avoid using RETURN statements in a PROCEDURE.
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: Avoid triggers for business logic
G-7730: If using triggers, use compound triggers
Sequences
Sequences
G-7810: Never use SQL inside PL/SQL to read sequence numbers (or SYSDATE).
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.
Code Reviews
G-7250: Always use NOCOPY when appropriate