Code Analysis

Code Analysis for Oracle

Below is the set of rules that can be customized for your organization.  For information about downloading and customizing Code Analysis for Oracle, see here.

Best practice rules

AvoidCharDataType

Avoid using the Char datatype.

Unlike for Varchar2, unused bytes are padded with blanks. There are no reasons to use Char over Varchar2. See Ask Tom thread for more discussion.

AvoidNaturalJoin

Avoid using Natural Joins.

It is better practice to be explicit about the join condition where possible. See this StackOverflow answer for a more detailed explanation.

AvoidVarCharDataType

Varchar2 should be used in preference to the Varchar datatype.

For all intents and purposes Varchar and Varchar2 are equivalent, but Varchar should not be used at it is reserved by Oracle for future usage. See Orafaq reference for more details.

BewareDataValueLists

Use temporary table joins instead of hard-coded lists of values using the IN keyword.

Although functionally equivalent, hard-coding a long list of values in a query makes it much harder to change if business rules change. Additionally, IN keyword value lists are limited to 1000 values.

ColumnNameIsReservedWord

Column name has been used as a reserved word.

This may not have technical implications but is generally regarded as bad practice and can result in confusing-looking code.

DefaultOnNull

Always use Default On Null.

This avoids Nulls slipping into columns that have an explicit Default set.

DeleteWithoutWhere

Delete statement found without a Where clause.

Omitting a Where clause is a common way of accidentally impacting more data than intended.

DoNotIgnoreExceptions

Do not ignore exceptions.

The code should not "swallow up" exceptions as this may conceal what could be a real issue.

DoNotUseGoto

Using GOTO makes for hard-to-understand code.

Use of GOTO increases code complexity and impacts readability and reliability.

EncapsulateInPackage

Procedures and functions should be encapsulated into Packages.

Other than for demos, it is recommended to avoid creating standalone procedures. See Ask Tom discussion for more detail.

InitVarWithNull

Do not initialize variables as NULL.

This would be redundant, as null is the default if no value is specified.

InsertColumnList

Insert statement should explicitly name target columns.

This makes the insert statement more resilient to future changes to the table's column make-up and column order. For more information, refer to Ben Brumm's Simple Talk article (no. 9).

NeverUseQuotedIdentifier

Never use quoted identifiers.

Quoted identifiers can make code hard to read, and encourages the use of reserved words, spaces and other special characters which is not considered a good practice.

NumberWithoutPrecision

A Number data type has been specified without a precision.

If a precision has not been specified, the default will be used, which may not be appropriate in your circumstances.

SchemaRequired

The schema is required when referencing a table.

Two part names are less ambiguous and therefore considered a good practice.

StarInSelect

There is an asterisk instead of a column list in a SELECT statement.

SELECT *  assumes certain columns in a particular order, which may not last. Also, results should always consist of just the columns you need.

UpdateWithoutWhere

Update statement found without a Where clause.

Omitting a Where clause is a common way of accidentally impacting more data than intended.

UseCaseInsteadDecode

Use Case rather than Decode.

Case is functionally near-identical, better understood, and easier to read.

UseCoalesceInsteadNvl

Use Coalesce instead of NVL.

COALESCE is more recent and part of the ANSI-92 standard, whereas NVL is Oracle-specific. More detail on the issue contained this article.

UseAnsiJoins

ANSI Join syntax should be used.

This is both for clarity and to benefit from the flexibility that JOINs provide. See Ben Brumm's Simple Talk article (no 1) for a more detailed explanation.

UseCharSemantic

Char semantic should be set explicitly.

See Oracle documentation for more information

UseExitWhenInsteadOfIfExit

Using Exit When instead of wrapping Exit in an If statement

This makes for tidier looking code.

UseNameInOrderBy

Use column names instead of numeric positions in ORDER BY clause.

This helps readability and also avoids exposing the query to changes in the column make-up and ordering of the select statement.

UseTableAliases

Always use table aliases when referencing columns if the query selects from more than one table.

Using explicit aliases helps the readability of the query. For more information see Ben Brumm's Simple Talk article (no. 7).

BlockLabelMatch

If both a start and end label for a block is specified, then they must match.

Labeling blocks is useful to make code more readable, especially if the code is badly indented.

CrossJoinUsed

A CROSS JOIN query may return a huge amount of data and should only be used when really needed.

CROSS JOIN query returns all rows from the first table combined with all rows from the second table. This is also known as a Cartesian product.  For more information, see the CROSS JOIN operation on the Oracle Docs.

DTFormatModel

Specify the format of the input string when using the TO_DATE or TO_TIMESTAMP functions. 

If you do not specify the format, then the default date format will be used and this is not always reliable.  You can learn more about the TO_DATE function on the Oracle Docs.

FullJoin

Full outer joins should be used with caution. 

You can learn more about Joins on the Oracle Docs.

LoopLabelMatch

The start and end labels for loops should match.

Labeling loops is useful to make code more readable, especially if the code is badly indented.

NestedSubQuery

Avoid nested subqueries if possible to improve performance.

If a subquery is in the WHERE clause of a SELECT statement, then it is called a nested subqueryLearn more about nested subqueries in the Oracle docs.

PutLine

Use a logging mechanism instead. 

There are cases when the DBMS_OUTPUT.PUT_LINE message may not be seen.  Learn more on the Obsessed with Oracle PL/SQL blog.

RedundantBoolean

A Boolean variable is either true or false. So, comparisons with the Boolean values TRUE and FALSE are redundant.

For more information, see the Guidelines for PL/SQL Boolean Expressions on the Oracle docs.

TooBigJoin

Consider using views if you have more than 3 joined tables.

UseEquijoins

The recommendation with writing joins is to use the ANSI style (the JOIN and ON keywords) rather than the Oracle style (the WHERE clause with (+) symbols). 

You can learn more on SimpleTalk.

UseExitWhenInsteadOfIfExit

The EXIT WHEN syntax can exit a loop depending on a condition and is simpler than IF condition THEN EXIT; END IF;.

Learn more about EXIT-WHEN in the Oracle docs.

UseWhenOthers

WHEN OTHERS should be used to catch every possible exception is caught.

Learn more about PL/SQL Exception Handling on the Oracle docs.


DoNotUseWeakRefCursor

A weak REF CURSOR can not be verified until runtime, so use it with care and consider using the built-in SYS_REFCURSOR.

Learn more about weak vs strong reference cursors on StackOverflow.


Deprecation rules

AvoidLongDataType

The Long datatype has been deprecated since v8.0.

"Do not create tables with LONG columns. Use LOB columns (CLOBNCLOBBLOB) instead. LONG columns are supported only for backward compatibility" (source: Oracle Docs)

OldStyleJoin

( + ) Join operator syntax has restrictions

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator (see Oracle documentation).


Miscellaneous rules

CGUNP

Unable to parse SQL code. The SQL may be incorrect or contain syntax that can't be understood.

CreateTableStatement

Create Table found in SQL script.

Some DBAs like to keep a watchful eye on when and how new tables are created.

RaiseApplicationError20k

Allowable custom error codes for raise_application_error are -20000 to -20999.

All other numbers are reserved for Oracle errors.


Naming convention rules

TableNameConvention

Table name does not adhere to the specified convention.

Specify allowed and disallowed patterns for constant names.

ConstantNameConvention

Constant name does not adhere to the specified convention.

Specify allowed and disallowed patterns for constant names.

CursorNameConvention

Cursor name does not adhere to the specified convention.

Specify allowed and disallowed patterns for cursor names.

ExceptionNameConvention

Exception name does not adhere to the specified convention.

Specify allowed and disallowed patterns for exception names.

FunctionNameConvention

Function name does not adhere to the specified convention.

Specify allowed and disallowed patterns for function names.

PackageNameConvention

Package name does not adhere to the specified convention.

Specify allowed and disallowed patterns for package names.

ProcedureNameConvention

Procedure name does not adhere to the specified convention.

Specify allowed and disallowed patterns for procedure names.

VariableNameConvention

Variable name does not adhere to the specified convention.

Specify allowed and disallowed patterns for variable names.

TypeNameConvention

Type name does not adhere to the specified convention.

Specify allowed and disallowed patterns for type names.

Performance rules

AvoidDistinct

Avoid using the Distinct keyword.

The DISTINCT keyword allows you to remove duplicates. It is handy in some situations, but in other situations, it's masking an issue with your query that's resulting in duplicate rows. Consider looking into your query if you're using DISTINCT to resolve an issue in the results.

CreateIndexStatement

Create Index found in script.

UseHavingWithGroupBy

Only using Having with Group By.

The HAVING clause allows you to restrict the data that is returned from a query. It's intended to be used along with GROUP BY clauses, but it will work without it. However, without a GROUP BY, it's better to use WHERE to restrict data. More information here: https://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/

UseUnionAll

Use Union All instead of Union.

The UNION keyword will combine two results and remove duplicates. UNION ALL will combine results but leave duplicates, meaning it generally performs better. If you don't need to check for duplicates, use UNION ALL instead. More information here: https://www.databasestar.com/sql-set-operators.

LikeStartWildcard

Review LIKE clauses that start with wildcard characters ('%' or '_') because an index can't be used.

When the value of a LIKE clause starts with '%' or '_', a full table scan is performed instead of using an index, which could cause a performance issue.  Learn more about this on the Pattern Matching on Indexed Columns on the Oracle docs.

UseExistsInsteadOfIn

Consider using EXISTS instead of IN for a subquery.

Learn more about Exists vs In.

VarNull

Declare variables as NULL, not as NOT NULL

Specifying a variable as NOT NULL comes at a small performance cost.


Style rules

AvoidROWID

Use of RowID may have performance benefits (in some cases), but is a non-standard Oracle-specific pseudocolumn.

RowID is non-standard SQL. More on RowID in the Oracle docs.

CompareWithNull

IS NULL is the correct syntax to use when comparing with null.

"NULL = NULL results in FALSE where NULL IS NULL results in TRUE" (source: Simple Talk)

FunctionMustEndWithReturn

Function must end with Return.

Ending a function with Return makes for more readable code.

LabelYourLoops

Loops should be labeled.

Naming loops makes SQL easier to understand.

INModeExplicit

Specify IN explicitly for parameters

Although a parameter without a specified mode defaults to IN, including this will improve code readability.

OrderByExplicit

ASC / DESC must be specified when using ORDER BY

ASC is the default if the direction isn't specified, but including it improves the code readability.

CloseOpenedCursor

Explicitly opened cursors should be closed

A cursor must be explicitly closed before it can be reopened (see Oracle documentation).

FuncOutParam

Avoid OUT and IN OUT parameters in functions

Oracle recommends against using OUT and IN OUT with functions (see Oracle documentation).

UseASWithAlias

To make your queries more readable, column aliases should be defined using "AS" so it's not misread as another column.  
SELECT COLUMN AS ALIAS_NAME FROM DUAL:


Security rules

AlterSystemStatement

Alter System statement found in script.

CreateUserStatement 

Create User found in script.

This may have security implications.

GrantStatement

Create Grant found in script.

This may have security implications.

Execution rules

RownumSameLevelOrderBy

If ROWNUM is used in a query with ORDER BY, the results may be different.  It may be best to use a subquery.

Learn more about ROWNUM in the Oracle docs.

LargeInList

Oracle cannot have any more than 1,000 items in an IN list.  Consider using a JOIN instead.

Learn more about lists in the Oracle docs.

UseHavingWithAgg

Only Use HAVING on Aggregate Functions.

Learn more about Aggregate Functions on the Oracle docs or this best practice on SimpleTalk.

CompoundTrigger

A compound trigger must comprise two or more triggers

By definition a compound trigger should contain more than one trigger.

DbmsSqlUsed

Review use of DBMS_SQL since this can dynamically construct SQL. Consider using EXECUTE IMMEDIATE.

Read more about Dynamically Dangerous Code in the Oracle Blog.

CaseWithoutElse

Ensure that CASE expression includes an ELSE

By definition an expression must return a value, which ELSE guarantees.

DoNotGoBack

Consider using a LOOP instead of jumping back to a previous statement with GOTO.

AvoidLoop

Consider using a condition in your LOOP.

ToNumberFormat

Specify a format when using TO_NUMBER.

Learn more about TO_NUMBER on the Oracle docs.

CRLFInStringLiteral

Use ASCII codes if you're trying to have a newline in PL/SQL.

Learn more at Ask Tom.

CreateOrReplace

Use CREATE OR REPLACE instead of CREATE

This avoids an error if the object already exists.

OrderByInSubquery

From the Oracle docs, "In subqueries, the ORDER BY clause is meaningless unless it is accompanied by one or both of the result offset and fetch first clauses or in conjunction with the ROW_NUMBER function, since there is no guarantee that the order is retained in the outer result set." 


Didn't find what you were looking for?