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
| analyzer | tqi |
|---|---|
| SQL | 2.59073431994084 |
| Oracle | 2.58794953216398 |
Number of data functions and transaction entry points per APPLICATION
| 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
| 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 | 3 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 | = |