Automated code review takes the tedious and time-consuming aspects of code review out of the task, but it still quite dependent on user input. In this article, we’ll discuss some of the best practices to follow when performing automated code review. The following is not a fixed procedure, but follows a logical sequence to use for best practices during automated code review.

  1. Establish objectives
  2. Do a preliminary code review
  3. Load one object at a time for code analysis
  4. Create a new rule set
  5. Test the rules
  6. Run code analysis
  7. Review code analysis result
  8. Use code analysis result


 As a preliminary setup, install Toad for Oracle, create an instance of Oracle Autonomous Database 19c (or other), and connect to the database instance, all of which is discussed in How to download Toad for Oracle.

Establish objectives


First, it is important to understand what automated code review is, and what it is not. Automated code review does not perform the following tasks:


  • Does not test or fix the code algorithm. For example, if a PL/SQL procedure is supposed to add two numbers, automated code review does not verify or fix that the procedure actually adds two numbers.
  • Does not verify that results are as expected. If a SQL statement is supposed to select data for all columns from a table, automated code review does not validate that the SQL code actually fetches the result for all columns.
  • Does not review or apply comments that code reviewer/s may have added.
  • Does not do maintenance tasks such upgrade code to a recent version of a feature or use a more suitable feature such as a more appropriate SQL function.
  • Does not debug or fix the code.


Automated code review runs a code analysis using predefined rule sets to ensure specific objectives for code’s program structure, naming conventions, maintainability, efficiency, code correctness and readability. Automated code review only displays the result of the code analysis and does not actually change the code to apply the rule sets. Example code analysis rules for different objectives are as follows:

Program structure

  • Avoid nesting comment blocks.
  • Use the CONSTANTkeyword wherever possible.
  • Avoid using CONTINUEin a loop.
  • Avoid using GOTO

Naming conventions

  • The name of each schema object should have a prefix equal to the given prefix.
  • The length of the name of tables should not exceed a given maximum.


  • The table you create should include a primary key.
  • Specify column names instead of numbers in ORDER BY
  • Avoid direct calls to PUT_LINE.
  • Avoid DDLs other than Truncate, Exchange/Split partition.
  • Encapsulate all procedures and functions in packages.
  • Limit use of sub-queries in SELECT

Code correctness

  • Avoid SELECTstatements with too many UNION
  • Use ANSI standard JOIN syntax whenever possible.
  • Avoid using DISTINCTin queries where it is already implied.
  • Limit use of FULL OUTER JOIN.
  • Limit use of CHARand VARCHAR.
  • LONGcolumn support will be discontinued in a future Oracle release. You are advised to migrate LONG data into LOB


  • Avoid large item lists for IN
  • For a given parenthetical level, avoid too many ORoperations in WHERE
  • Avoid deprecated data types such as RAWand LONG. Consider using BLOBs and CLOBs which have way less usage restrictions than LONGand LONG RAWS.


  • Explicitly specify ASC/DESCin ORDER BY
  • List columns explicitly in your INSERT
  • Never handle unnamed exceptions using the error number.

Do a preliminary code review


We’ll use the following sample code for automated code review. It’s a PL/SQL procedure based on a built-in table SH.PRODUCTSin Oracle Autonomous Database:




   cursor products_cur is

       select PROD_NAME

       from SH.PRODUCTS;

   loop_counter   NUMBER := 0;

   PROD_NAME   varchar2(50);


   open products_cur;

   fetch products_cur into PROD_NAME;

   while products_cur%FOUND


     loop_counter := loop_counter + 1;


         ('Record ' || loop_counter || ' is Product ' || PROD_NAME );

     fetch products_cur into PROD_NAME;


   DBMS_OUTPUT.put_line ('Procedure is done');

   close products_cur;



Automated Code review is not supposed to fix code errors. Therefore,

run the sample code in an SQL worksheet, or Editor, to create the PL/SQL procedure as shown in Figure 1.

automated code review 1

Figure 1. Create a Sample Code PL/SQL Procedure


In Schema Browser, click on Compile invalid objects to ensure no invalid objects are present. Click on Compile to ensure the sample code compiles. Click on Execute Procedure as shown in Figure 2 to run the procedure.

automated code review 2

Figure 2. Execute Procedure


In the Set Parameters dialog, set Output Options as needed. Click on Execute in Toad Script Runner to run PL/SQL procedure in Script Runner. The output should be PL/SQL Procedure successfully completed.


Next, do the code analysis, which involves loading object/s for code analysis, optionally adding rule sets and rules, and running the code analysis itself.

Load one object at a time for code analysis


To best identify issues, load one object at a time for code analysis. Select Database>Diagnose>Code Analysis as shown in Figure 3.

 automated code review 3

Figure 3. Database>Diagnose>Code Analysis


In the Code Analysis wizard select Load Objects from a drop down as shown in Figure 4.

automated code review 4


Figure 4. Load Objects


In the Load Database Object wizard, select the example PL/SQL procedure created earlier and click on OK as shown in Figure 5.

automated code review 5

Figure 5. Load Database Object


The PL/SQL procedure gets loaded in the Code Analysis wizard as shown in Figure 6.

automated code review 6

Figure 6. PL/SQL Procedure loaded in Code Analysis wizard

Create new rule set


A new rule set could be useful to select and include only relevant rules. Select Edit Rule Sets in the Code Analysis wizard as shown in Figure 7.

automated code review 7

Figure 7. Edit Rule Sets


The Edit Code Analysis Rule Sets wizard gets started. The different rule sets are categorized. To create a new rule set, click on Create new rule set as shown in Figure 8.

automated code review 8

Figure 8. Create new rule set


In the New Rule Set wizard, specify a title for the new rule set, and select all the PL/SQL related rules because the sample code is a PL/SQL procedure. Click on OK (Figure 9).

automated code review 9

Figure 9. New Rule Set wizard


A new rule set gets added as shown in Figure 10. A user created rule set is editable by default as indicated by the Read only checkbox deselected.

automated code review 10

Figure . 10. New rule set created


Test rules


It’s useful to test at least a few of the relevant rules. Select Edit Rules in the Code Analysis wizard as shown in Figure 11.


automated code review 11

Figure 11. Edit Rules


To test a rule, select the rule and click on Test selected rule as shown in Figure 12. The selected rule gets tested using the sample code for the rule listed in the Test Code box.

automated code review 12

Figure 12. Test selected rule


A new rule may be created with the Create new rule button.  

Run code analysis


To run the code analysis, click on Analyze code for all selected items as shown in Figure 13.

automated code review 13

Figure 13. Analyze code for selected items


The code analysis results get displayed as shown in Figure 14.

automated code review 14

Figure 14. Code Analysis Results


Review code analysis result


The most important thing in code analysis is to review the result. As shown in Figure 15, all 17 of the rules used passed.


automated code review 15

Figure 15. Result Review


The Report tab (Figure 16) shows a more detailed report of the code analysis result.

automated code review 16

Figure 16. Report of Code Analysis Result

Try Toad free for 30 days. 

Free 30-day Trial

Already in a trial? Talk to sales or buy now online.

Already a loyal fan of Toad for Oracle? Renew now.


Use code analysis result


The project team may use the code analysis result to improve on the code if needed. The complexity of the code may be used to allocate appropriate project resources. The programmer/s on the project team should use the result of the SQL Scan to find and fix invalid SQL statements, if any. The Halstead Volume of about 58 for the sample code indicates that an average programmer should be able to understand the code. The McCabe's Cyclomatic complexity of 3 indicates that the sample code is a simple code. The Maintainability Index of 100+ indicates that sample code is easy to maintain.  


Related Links

Blog: Toad code review – useful to the programmer

Blog: What is a code review and why PL/SQL code quality is important 

Blog: Code analysis: Why PL/SQL code quality matters 

Blog: How a Code Review Tool Can Help You Write Team-friendly PL/SQL [Webcast] 

Blog: Advanced code review using Code Analysis 

Blog: Analyzing Code with the Toad for Oracle Code Analysis Tool 

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.


About the Author

Deepak Vohra

Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.

Start the discussion at