Code Analysis

Code Analysis for Oracle

Our newest x-database code analysis solution is available as part of the Flyway check command.  Contact the development team if you are interested in code analysis checks.  

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


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.


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.


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.


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.


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.


Always use Default On Null.

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


Delete statement found without a Where clause.

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


Do not ignore exceptions.

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


Using GOTO makes for hard-to-understand code.

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


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.


Do not initialize variables as NULL.

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


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


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.


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.


The schema is required when referencing a table.

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


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.


Update statement found without a Where clause.

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


Use Case rather than Decode.

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


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.


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.


Char semantic should be set explicitly.

See Oracle documentation for more information


Using Exit When instead of wrapping Exit in an If statement

This makes for tidier looking code.


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.


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


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.


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.


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.


Full outer joins should be used with caution. 

You can learn more about Joins on the Oracle Docs.


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.


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.


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.


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.


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


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.


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.


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

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


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


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)


( + ) 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


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


Create Table found in SQL script.

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


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

All other numbers are reserved for Oracle errors.

Naming convention rules


Table name does not adhere to the specified convention.

Specify allowed and disallowed patterns for constant names.


Constant name does not adhere to the specified convention.

Specify allowed and disallowed patterns for constant names.


Cursor name does not adhere to the specified convention.

Specify allowed and disallowed patterns for cursor names.


Exception name does not adhere to the specified convention.

Specify allowed and disallowed patterns for exception names.


Function name does not adhere to the specified convention.

Specify allowed and disallowed patterns for function names.


Package name does not adhere to the specified convention.

Specify allowed and disallowed patterns for package names.


Procedure name does not adhere to the specified convention.

Specify allowed and disallowed patterns for procedure names.


Variable name does not adhere to the specified convention.

Specify allowed and disallowed patterns for variable names.


Type name does not adhere to the specified convention.

Specify allowed and disallowed patterns for type names.

Performance rules


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.


Create Index found in script.


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:


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:


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.


Consider using EXISTS instead of IN for a subquery.

Learn more about Exists vs In.


Declare variables as NULL, not as NOT NULL

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

Style rules


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.


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)


Function must end with Return.

Ending a function with Return makes for more readable code.


Loops should be labeled.

Naming loops makes SQL easier to understand.


Specify IN explicitly for parameters

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


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.


Explicitly opened cursors should be closed

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


Avoid OUT and IN OUT parameters in functions

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


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

Security rules


Alter System statement found in script.


Create User found in script.

This may have security implications.


Create Grant found in script.

This may have security implications.

Execution rules


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.


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.


Only Use HAVING on Aggregate Functions.

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


A compound trigger must comprise two or more triggers

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


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.


Ensure that CASE expression includes an ELSE

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


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


Consider using a condition in your LOOP.


Specify a format when using TO_NUMBER.

Learn more about TO_NUMBER on the Oracle docs.


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

Learn more at Ask Tom.



This avoids an error if the object already exists.


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?