Use case - CASTPUBS app

Introduction

The comparison has been done between PL-SQL Analyzer 8.3.8 and SQL Analyzer 3.4.0-beta3 (AIP 8.3.8 + datacolumnaccess 1.0.0-beta2), on the attached source code.

CASTPUBS cover almost all kind of Oracle objects, links and quality rules, that’s why it has been selected as example.

You can find here bellow differences.

/**/ 1 TQI 2 Number of data functions and transaction entry points per APPLICATION 3 Number of objects by type 4 Compare equivalent quality rules

TQI

<query>

analyzer tqi
SQL 2.59073431994084
Oracle 2.58794953216398

Number of data functions and transaction entry points per APPLICATION

<query>

analyzer cnt_datafunctions cntdfp_functionpoints cntdf_calibratedfunctionpoints cnt_transactions cnttfp_functionpoints cntt_calibratedfunctionpoints
SQL 16 100 100 0 0 0
Oracle 12 72 72 0 0 0

Number of objects by type

<query>

objecttype numberofobjects Compared with Oracle
Column 100 =
Foreign Key 16 =
Function 24 =
Indexes Primary Keys Unique Constraints 21

-11

We are betters on SQL Analyzer, on PL/SQL Analyzer a part of indexes are duplicated.

Package 14 =
Procedure 17 =
Schema 2 =
Synonym 2 =
Table 16 =
Trigger 4 =
View 5 =
Check Constraint 56 Object type not implemented on SQL
Instance 1 Object type not implemented on SQL
Package Cursor 2 Object type not implemented on SQL
Sequence 1 Object type not implemented on SQL

Compare links by type

<query>{linked-resource-id=“378514739” linked-resource-version=“2” linked-resource-type=“attachment” linked-resource-default-alias=“CompareSQLORALinks.sql” linked-resource-content-type=“application/octet-stream” linked-resource-container-id=“378514735” linked-resource-container-version=“4”}

kindoflink callertype calleetype Compared with Oracle
ACCESS Function Column

We discover more links than PL/SQL Analyzer

ACCESS Package Column

Detected only by PL/SQL Analyzer

This is the case of package headers

ACCESS Procedure Column

-16

PL/SQL discover 16 links more than SQL Analyzer but a part of them are false links, they don't really exists

ACCESS Trigger Column =
ACCESS View Column =
CALL Function Function =
CALL Procedure Function =
CALL Procedure Procedure =
CALL Procedure Trigger Detected only by SQL Analyzer
CALL Trigger Trigger Detected only by SQL Analyzer
USE Function Synonym Detected only by SQL Analyzer
USE Function Table =
USE Function View =
USE Package Table

Detected only by PL/SQL Analyze

Same comment as for columns, this is the case of package headers

USE Procedure Synonym Detected only by SQL Analyzer
USE Procedure Table =
USE Trigger Synonym Detected only by SQL Analyzer
USE Trigger Table =
USE View Table =

Compare equivalent quality rules

<query>{linked-resource-id=“378514737” linked-resource-version=“2” linked-resource-type=“attachment” linked-resource-default-alias=“CompareSQLORAQRs.sql” linked-resource-content-type=“application/octet-stream” linked-resource-container-id=“378514735” linked-resource-container-version=“4”}

qualityrulename objecttype Compared with Oracle
Always define column names when inserting values Function Exists only on SQL Analyzer
Always define column names when inserting values Procedure Exists only on SQL Analyzer
Average Reuse by Call Function =
Average Reuse by Call Procedure =
Average Reuse by Call Trigger =
Average Reuse by Call View =
Average Size Artifacts Function Detected only by PL/SQL Analyzer
Average Size Artifacts Procedure Detected only by PL/SQL Analyzer
Average Size Artifacts Trigger Detected only by PL/SQL Analyzer
Avoid "SELECT *" queries Function Detected only by PL/SQL Analyzer
Avoid "SELECT *" queries Procedure Detected only by PL/SQL Analyzer
Avoid Artifacts with High Essential Complexity Function Detected only by SQL Analyzer
Avoid Artifacts with High Essential Complexity Procedure Detected only by SQL Analyzer
Avoid Artifacts with High Essential Complexity Trigger Detected only by SQL Analyzer
Avoid Artifacts with SQL statement including subqueries Function -2
Avoid Artifacts with SQL statement including subqueries Trigger =
Avoid Artifacts with a Complex SELECT Clause Function The rationale describe a client server context there is no value to apply-it on SQL code
Avoid Artifacts with a Complex SELECT Clause Procedure The rationale describe a client server context there is no value to apply-it on SQL code
Avoid Artifacts with high Commented-out Code Lines/Code Lines ratio Procedure Detected only by PL/SQL Analyzer
Avoid Artifacts with lines longer than X characters Function =
Avoid Artifacts with lines longer than X characters Procedure =
Avoid Tables without Primary Key Table =
Avoid Too Many Copy Pasted Artifacts Function -2
Avoid cascading Triggers Trigger =
Avoid empty catch blocks Procedure Exists only on SQL Analyzer
Avoid exists and not exists independent clauses Function Detected only by SQL Analyzer
Avoid having multiple Artifacts inserting data on the same SQL Table Table =
Avoid having multiple artifacts deleting data on the same SQL table Table =
Avoid large Artifacts - too many Lines of Code Function =
Avoid large Artifacts - too many Lines of Code Procedure =
Avoid large Artifacts - too many Lines of Code Trigger =
Avoid long Table or View names View =
Avoid non-SARGable queries Function Exists only on SQL Analyzer
Avoid non-indexed SQL queries Function -1
Avoid non-indexed SQL queries Procedure -1
Avoid non-indexed SQL queries View -1
Avoid orphaned synonyms Synonym =
Avoid queries using old style join convention instead of ANSI-Standard joins Function =
Avoid queries using old style join convention instead of ANSI-Standard joins Procedure =
Avoid queries using old style join convention instead of ANSI-Standard joins View =
Avoid redundant indexes Table =
Avoid triggers functions and procedures with a very low comment/code ratio Function =
Avoid triggers functions and procedures with a very low comment/code ratio Procedure =
Avoid triggers functions and procedures with a very low comment/code ratio Trigger =
Avoid undocumented Triggers Functions and Procedures Function =
Avoid undocumented Triggers Functions and Procedures Procedure =
Avoid undocumented Triggers Functions and Procedures Trigger =
Avoid unreferenced Functions Function =
Avoid unreferenced Functions Procedure =
Avoid unreferenced Tables Table =
Avoid unreferenced views View =
Avoid using "nullable" Columns except in the last position in a Table Table Exists only on PL/SQL Analyzer
Avoid using GOTO statement Function =
Avoid using SQL queries inside a loop Function

Detected only by PL/SQL Analyzer

False violation, the loop is a cursor, that's why the violation is not raised on SQL Analyzer since 3.4.0-beta3. Since 3.4.0-beta3 the rule is calculated by extension and not by MA.

Column references should be qualified Procedure Exists only on SQL Analyzer
Column references should be qualified View Exists only on SQL Analyzer
Cyclomatic Complexity Distribution Function Detected only by PL/SQL Analyzer
Cyclomatic Complexity Distribution Procedure =
Cyclomatic Complexity Distribution Trigger =
Cyclomatic Complexity Distribution View Detected only by SQL Analyzer
DISTINCT should not be used in SQL SELECT statements View Exists only on SQL Analyzer
Low Complexity Artifacts Function 7
Low Complexity Artifacts Procedure =
Low Complexity Artifacts Trigger =
Low SQL Complexity Artifacts Function -2
Low SQL Complexity Artifacts Procedure Detected only by PL/SQL Analyzer
Low SQL Complexity Artifacts Trigger 1
Moderate Complexity Artifacts Function 1
Never use WHEN OTHER THEN NULL Procedure Exists only on SQL Analyzer
Number of Code Lines Function =
Number of Code Lines Procedure =
Number of Code Lines Trigger =
Number of Code Lines View =
Number of Datablocks Function =
Number of Datablocks Procedure =
Number of Forms Function =
Number of Forms Procedure =
Number of Forms Trigger =
Number of Forms View =
Number of Functions Table =
Number of Tables View =
Number of Views Trigger =
Prefer PRIVATE to PUBLIC synonym Synonym =
SQL Complexity Distribution Function 2
SQL Complexity Distribution Procedure 1
SQL Complexity Distribution Trigger Detected only by PL/SQL Analyzer
SQL Complexity Distribution View =
Small Size Artifacts Function -11
Small Size Artifacts Procedure -11
Small Size Artifacts Trigger Detected only by PL/SQL Analyzer
Small Size Artifacts View Detected only by PL/SQL Analyzer
Tables aliases should not end with a numeric suffix Function Exists only on SQL Analyzer
Tables aliases should not end with a numeric suffix Procedure Exists only on SQL Analyzer
Tables aliases should not end with a numeric suffix View Exists only on SQL Analyzer
Tables should be aliased Procedure Exists only on SQL Analyzer
Tables should be aliased View Exists only on SQL Analyzer
Triggers should not directly modify tables a procedure or function should be used instead Trigger =
VARCHAR2 and NVARCHAR2 should be used Function 12
VARCHAR2 and NVARCHAR2 should be used Package Detected only by PL/SQL Analyzer but the same violation is duplicated : reported one time on the package function / procedure and also directly on the package
VARCHAR2 and NVARCHAR2 should be used Procedure =
VARCHAR2 and NVARCHAR2 should be used Table =