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.

Maintainability

• 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

Efficiency

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

Readability

• 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:

CREATE OR REPLACE PROCEDURE loop_sh_products

IS

   cursor products_cur is

       select PROD_NAME

       from SH.PRODUCTS;

   loop_counter   NUMBER := 0;

   PROD_NAME   varchar2(50);

BEGIN

   open products_cur;

   fetch products_cur into PROD_NAME;

   while products_cur%FOUND

   LOOP

     loop_counter := loop_counter + 1;

     DBMS_OUTPUT.put_line

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

     fetch products_cur into PROD_NAME;

   END LOOP;

   DBMS_OUTPUT.put_line ('Procedure is done');

   close products_cur;

END;

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.

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.

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.

Figure 3. Database>Diagnose>Code Analysis

In the Code Analysis wizard select Load Objects from a drop down as shown in Figure 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.

Figure 5. Load Database Object

The PL/SQL procedure gets loaded in the Code Analysis wizard as shown in Figure 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.

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.

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

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.

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.

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.

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.

Figure 13. Analyze code for selected items

The code analysis results get displayed as shown in Figure 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.

Figure 15. Result Review

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

Figure 16. Report of Code Analysis Result

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.