Trivadis PL/SQL & SQL coding guideline rules
dbLinter includes 165 rules based on the Trivadis PL/SQL & SQL Coding Guidelines for Oracle SQL and PL/SQL. Each rule links to the relevant guideline and provides an explanation, examples and a recommended fix.
124 rules use the same G-number as the corresponding guideline, making them easy to find by guideline number.
Building on experience maintaining the Trivadis guidelines, dbLinter carries many familiar recommendations into an independently developed rule set.
Most severe Trivadis guideline rules
Violations of these rules carry the highest severities, so they are the ones to fix first.
- G-1060 Avoid storing ROWIDs or UROWIDs in database tables. Blocker
- G-1080 Avoid using the same expression on both sides of a relational comparison operator or a logical operator. Blocker
- G-1920 Avoid syntax errors. Blocker
- G-2145 Never self-assign a variable. Blocker
- G-2150 Avoid comparisons with NULL value, consider using IS [NOT] NULL. Blocker
- G-2190 Avoid using ROWID or UROWID. Blocker
- G-2310 Avoid using CHAR data type. Blocker
- G-2320 Never use VARCHAR data type. Blocker
Trivadis rules by quality area
- Maintainability rules (106 from the guidelines)
- Reliability rules (56 from the guidelines)
- Security rules (3 from the guidelines)
All 165 Trivadis guideline rules
-
G-0010 New Always format database code consistently.Minor Maintainability
-
G-1010 Try to label your sub blocks.Minor Maintainability
-
G-1020 Always have a matching loop or block label.Minor Maintainability
-
G-1030 Avoid defining variables that are not used.Minor Maintainability
-
G-1040 Avoid dead code.Minor Maintainability
-
G-1050 Avoid using literals in your code.Minor Maintainability
-
G-1060 Avoid storing ROWIDs or UROWIDs in database tables.Blocker Reliability
-
G-1070 Avoid nesting comment blocks.Minor Maintainability
-
G-1080 Avoid using the same expression on both sides of a relational comparison operator or a logical operator.Blocker Maintainability
-
G-1260 Try to define a comment for each table.Info Maintainability
-
G-1270 Try to define a comment for each column.Info Maintainability
-
G-1910 Avoid using the NOSONAR marker.Info Maintainability
-
G-1920 Avoid syntax errors.Blocker Maintainability
-
G-2110 Try to use anchored declarations for variables, constants and types.Major Reliability
-
G-2120 Try to have a single location to define your types.Minor Maintainability
-
G-2130 Try to use subtypes for constructs used often in your code.Minor Maintainability
-
G-2135 Avoid assigning values to local variables that are not used by a subsequent statement.Major Reliability
-
G-2140 Never initialize variables with NULL.Minor Maintainability
-
G-2145 Never self-assign a variable.Blocker Maintainability
-
G-2150 Avoid comparisons with NULL value, consider using IS [NOT] NULL.Blocker Reliability
-
G-2160 Avoid initializing variables using functions in the declaration section.Critical Reliability
-
G-2170 Never overload variables.Major Maintainability
-
G-2180 Never use quoted identifiers.Major Maintainability
-
G-2185 Avoid using overly short names for explicitly or implicitly declared identifiers.Major Maintainability
-
G-2190 Avoid using ROWID or UROWID.Blocker Reliability
-
G-2210 Avoid declaring NUMBER variables, constants or subtypes with no precision.Critical Reliability
-
G-2220 Try to use PLS_INTEGER instead of NUMBER for arithmetic operations with integer values.Critical Reliability
-
G-2230 Try to use SIMPLE_INTEGER datatype when appropriate.Critical Reliability
-
G-2310 Avoid using CHAR data type.Blocker Reliability
-
G-2320 Never use VARCHAR data type.Blocker Maintainability
-
G-2330 Never use zero-length strings to substitute NULL.Blocker Maintainability
-
G-2340 Always define your VARCHAR2 variables using CHAR SEMANTIC (if not defined anchored).Blocker Reliability
-
G-2410 Try to use boolean data type for values with dual meaning.Minor Maintainability
-
G-2510 Avoid using the LONG and LONG RAW data types.Major Maintainability
-
G-2610 Never use self-defined weak ref cursor types.Minor Maintainability
-
G-3110 Always specify the target columns when coding an insert statement.Blocker Reliability
-
G-3115 Avoid self-assigning a column.Blocker Maintainability
-
G-3120 Always use table aliases when your SQL statement involves more than one source.Blocker Maintainability
-
G-3130 Try to use ANSI SQL-92 join syntax.Major Maintainability
-
G-3140 Try to use anchored records as targets for your cursors.Major Reliability
-
G-3145 Avoid using SELECT * directly from a table or view.Blocker Reliability
-
G-3150 Try to use identity columns for surrogate keys.Critical Reliability
-
G-3160 Avoid visible virtual columns.Blocker Reliability
-
G-3170 Always use DEFAULT ON NULL declarations to assign default values to table columns if you refuse to store NULL values.Blocker Reliability
-
G-3180 Always specify column names instead of positional references in ORDER BY clauses.Major Reliability
-
G-3182 Always specify column names/aliases instead of positional references in GROUP BY clauses.Blocker Reliability
-
G-3183 Always specify column aliases instead of expressions in GROUP BY clauses.Minor Maintainability
-
G-3185 Never use ROWNUM at the same query level as ORDER BY.Blocker Reliability
-
G-3190 Avoid using NATURAL JOIN.Blocker Reliability
-
G-3195 Always use wildcards in a LIKE clause.Blocker Maintainability
-
G-3210 Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement for more than 4 times.Critical Reliability
-
G-3220 Always process saved exceptions from a FORALL statement.Critical Reliability
-
G-3310 Never commit within a cursor loop.Blocker Reliability
-
G-3320 Try to move transactions within a non-cursor loop into procedures.Major Maintainability
-
G-3330 Avoid autonomous transactions.Blocker Reliability
-
G-4110 Always use %NOTFOUND instead of NOT %FOUND to check whether a cursor returned data.Minor Maintainability
-
G-4120 Avoid using %NOTFOUND directly after the FETCH when working with BULK OPERATIONS and LIMIT clause.Blocker Reliability
-
G-4130 Always close locally opened cursors.Blocker Reliability
-
G-4140 Avoid executing any statements between a SQL operation and the usage of an implicit cursor attribute.Blocker Reliability
-
G-4210 Try to use CASE rather than an IF statement with multiple ELSIF paths.Minor Maintainability
-
G-4220 Try to use CASE rather than DECODE.Major Maintainability
-
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.Critical Reliability
-
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.Critical Reliability
-
G-4250 Avoid using identical conditions in different branches of the same IF or CASE statement.Blocker Maintainability
-
G-4260 Avoid inverting boolean conditions with NOT.Minor Maintainability
-
G-4270 Avoid comparing boolean values to boolean literals.Minor Maintainability
-
G-4310 Never use GOTO statements in your code.Major Maintainability
-
G-4320 Always label your loops.Minor Maintainability
-
G-4325 Never reuse labels in inner scope.Major Maintainability
-
G-4330 Always use a CURSOR FOR loop to process the complete cursor results unless you are using bulk operations.Major Maintainability
-
G-4340 Always use a NUMERIC FOR loop to process a dense array.Major Maintainability
-
G-4350 Always use 1 as lower and COUNT() as upper bound when looping through a dense array.Blocker Reliability
-
G-4360 Always use a WHILE loop to process a loose array.Critical Reliability
-
G-4365 Never use unconditional CONTINUE or EXIT in a loop.Major Maintainability
-
G-4370 Avoid using EXIT to stop loop processing unless you are in a basic loop.Major Maintainability
-
G-4375 Always use EXIT WHEN instead of an IF statement to exit from a loop.Minor Maintainability
-
G-4380 Try to label your EXIT WHEN statements.Minor Maintainability
-
G-4385 Never use a cursor for loop to check whether a cursor returns data.Critical Maintainability
-
G-4387 Never use a FOR LOOP for a query that should return not more than one row.Blocker Reliability
-
G-4390 Avoid use of unreferenced FOR loop indexes.Major Reliability
-
G-4395 Avoid hard-coded upper or lower bound values with FOR loops.Minor Maintainability
-
G-5010 Try to use a error/logging framework for your application.Critical Reliability
-
G-5020 Never handle unnamed exceptions using the error number.Critical Maintainability
-
G-5030 Never assign predefined exception names to user defined exceptions.Blocker Reliability
-
G-5040 Avoid use of WHEN OTHERS clause in an exception section without any other specific handlers.Critical Reliability
-
G-5050 Avoid use of the RAISE_APPLICATION_ERROR built-in procedure with a hard-coded 20nnn error number or hard-coded message.Major Maintainability
-
G-5060 Avoid unhandled exceptions.Major Reliability
-
G-5070 Avoid using Oracle predefined exceptions.Blocker Reliability
-
G-5080 Always use FORMAT_ERROR_BACKTRACE when using FORMAT_ERROR_STACK or SQLERRM.Critical Maintainability
-
G-6010 Always use a character variable to execute dynamic SQL.Major Maintainability
-
G-6020 Try to use output bind arguments in the RETURNING INTO clause of dynamic DML statements rather than the USING clause.Minor Maintainability
-
G-7110 Try to use named notation when calling program units.Major Maintainability
-
G-7120 Always add the name of the program unit to its end keyword.Minor Maintainability
-
G-7125 Always use CREATE OR REPLACE instead of CREATE alone.Major Maintainability
-
G-7127 Always use the FORCE option when creating views.Major Maintainability
-
G-7130 Always use parameters or pull in definitions rather than referencing external variables in a local program unit.Major Reliability
-
G-7140 Always ensure that locally defined procedures or functions are referenced.Major Maintainability
-
G-7150 Try to remove unused parameters.Major Maintainability
-
G-7160 Always explicitly state parameter mode.Major Reliability
-
G-7170 Avoid using an IN OUT parameter as IN or OUT only.Major Reliability
-
G-7210 Try to keep your packages small. Include only few procedures and functions that are used in the same context.Minor Reliability
-
G-7220 Always use forward declaration for private functions and procedures.Minor Maintainability
-
G-7230 Avoid declaring global variables public.Major Reliability
-
G-7250 Never use RETURN in package initialization block.Major Reliability
-
G-7310 Avoid standalone procedures – put your procedures in packages.Minor Maintainability
-
G-7320 Avoid using RETURN statements in a PROCEDURE.Major Maintainability
-
G-7330 Always assign values to OUT parameters.Blocker Maintainability
-
G-7410 Avoid standalone functions – put your functions in packages.Minor Maintainability
-
G-7420 Always make the RETURN statement the last statement of your function.Major Maintainability
-
G-7430 Try to use no more than one RETURN statement within a function.Major Maintainability
-
G-7440 Never use OUT parameters to return values from a function.Major Maintainability
-
G-7450 Never return a NULL value from a BOOLEAN function.Blocker Maintainability
-
G-7460 Try to define your packaged/standalone function deterministic if appropriate.Major Reliability
-
G-7510 Always prefix Oracle supplied packages with owner schema name.Blocker Security
-
G-7515 Always prefix Oracle supplied object types with owner schema name.Blocker Security
-
G-7710 Avoid cascading triggers.Major Maintainability
-
G-7720 Never use multiple UPDATE OF in trigger event clause.Blocker Maintainability
-
G-7730 Avoid multiple DML events per trigger.Minor Reliability
-
G-7740 Never handle multiple DML events per trigger if primary key is assigned in trigger.Critical Reliability
-
G-7810 Never use SQL inside PL/SQL to read sequence numbers (or SYSDATE).Critical Reliability
-
G-7910 Never use DML within a SQL macro.Blocker Reliability
-
G-8110 Never use SELECT COUNT(*) if you are only interested in the existence of a row.Critical Reliability
-
G-8120 Never check existence of a row to decide whether to create it or not.Critical Reliability
-
G-8210 Always use synonyms when accessing objects of another application schema.Minor Maintainability
-
G-8310 Always validate input parameter size by assigning the parameter to a size limited variable in the declaration section of program unit.Minor Maintainability
-
G-8410 Always use application locks to ensure a program unit is only running once at a given time.Blocker Reliability
-
G-8510 Always use dbms_application_info to track program process transiently.Critical Reliability
-
G-9010 Always use a format model in string to date/time conversion functions.Blocker Security
-
G-9020 Try to use a format model and NLS_NUMERIC_CHARACTERS in string to number conversion functions.Critical Reliability
-
G-9030 Try to define a default value on conversion errors.Major Maintainability
-
G-9040 Try using FX in string to date/time conversion format model to avoid fuzzy conversion.Blocker Reliability
-
G-9101 Always follow naming conventions for global variables.Minor Maintainability
-
G-9102 Always follow naming conventions for local variables.Minor Maintainability
-
G-9103 Always follow naming conventions for cursor variables.Minor Maintainability
-
G-9104 Always follow naming conventions for record variables.Minor Maintainability
-
G-9105 Always follow naming conventions for collection variables (arrays/tables).Minor Maintainability
-
G-9106 Always follow naming conventions for object variables.Minor Maintainability
-
G-9107 Always follow naming conventions for cursor parameters.Minor Maintainability
-
G-9108 Always follow naming conventions for IN parameters of functions and procedures.Minor Maintainability
-
G-9109 Always follow naming conventions for OUT parameters of functions and procedures.Minor Maintainability
-
G-9110 Always follow naming conventions for IN/OUT parameters of functions and procedures.Minor Maintainability
-
G-9111 Always follow naming conventions for record type definitions.Minor Maintainability
-
G-9112 Always follow naming conventions for collection type definitions (arrays/tables).Minor Maintainability
-
G-9113 Always follow naming conventions for exceptions.Minor Maintainability
-
G-9114 Always follow naming conventions for constants.Minor Maintainability
-
G-9115 Always follow naming conventions for subtypes.Minor Maintainability
-
G-9201 Always follow naming conventions for tables.Minor Maintainability
-
G-9202 Always follow naming conventions for table/view columns.Minor Maintainability
-
G-9203 Always follow naming conventions for indexes.Minor Maintainability
-
G-9204 Always follow naming conventions for primary key constraints.Minor Maintainability
-
G-9205 Always follow naming conventions for unique constraints.Minor Maintainability
-
G-9206 Always follow naming conventions for foreign key constraints.Minor Maintainability
-
G-9207 Always follow naming conventions for check constraints.Minor Maintainability
-
G-9208 Always follow naming conventions for assertions.Minor Maintainability
-
G-9209 Always follow naming conventions for global temporary tables.Minor Maintainability
-
G-9210 Always follow naming conventions for views.Minor Maintainability
-
G-9211 Always follow naming conventions for sequences.Minor Maintainability
-
G-9212 Always follow naming conventions for synonyms.Minor Maintainability
-
G-9213 Always follow naming conventions for triggers.Minor Maintainability
-
G-9214 Always follow naming conventions for PL/SQL packages.Minor Maintainability
-
G-9215 Always follow naming conventions for functions.Minor Maintainability
-
G-9216 Always follow naming conventions for procedures.Minor Maintainability
-
G-9217 Always follow naming conventions for object types.Minor Maintainability
-
G-9218 Always follow naming conventions for object type attributes.Minor Maintainability
-
G-9219 Always follow naming conventions for collection types.Minor Maintainability
165 total
Related rule collections
- OWASP-related security rules - Rules that name an OWASP Top 10 risk category.
- Oracle APEX rules - Checks for APEX applications, pages, items and regions.
- PostgreSQL rules - Rules that also apply to PostgreSQL and PL/pgSQL.
- Security rules - All rules in the Security quality area.
- Reliability rules - Rules that prevent bugs and failures at run time.
- Maintainability rules - Rules for code that is easy to read and change.
- Rules by quality area - Security, reliability and maintainability.
- Rules by severity - From Blocker to Info.
- Rule profiles - Essential to Extreme: which rules are checked.
- Rules by tag - Topics such as SQL, performance and naming.
- All rules - Search and filter the complete rule set.