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.

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).

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)

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

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/

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.

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

TODO

Todo.

Misc rules

UseExistsInsteadOfIn

TODO

Todo.

BewareDataValueLists

TODO

Todo.

UseHavingWithAgg

Only Use HAVING on Aggregate Functions

Todo.

INModeExplicit

Specify IN explicitly for parameters

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

UseASWithAlias

TODO

Todo.

OrderByInSubquery

TODO

Todo.

CloseOpenedCursor

Explicitly opened cursors should be closed

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

DoNotGoBack

TODO

Todo.

LoopLabelMatch

TODO

Todo.

DoNotUseWeakRefCursor

TODO

Todo.

CRLFInStringLiteral

TODO

Todo.

CreateOrReplace

Use CREATE OR REPLACE instead of CREATE

This avoids an error if the object already exists.

AvoidLoop

TODO

Todo.

FuncOutParam

Avoid OUT and IN OUT parameters in functions

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

ToNumberFormat

TODO

Todo.

VarNull

Declare variables as NULL, not as NOT NULL

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

CaseWithoutElse

Ensure that CASE expression includes an ELSE

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

DbmsSqlUsed

TODO

Todo.

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).

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.

CompoundTrigger

A compound trigger must comprise two or more triggers

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

LargeInList

TODO

Todo.




Didn't find what you were looking for?