Page tree
Skip to end of metadata
Go to start of metadata

Target audience:

Users of the extension providing source code analysis support for SQL files.

Summary: This document provides basic information about the extension that provides source code analysis support for SQL files.

Extension ID

com.castsoftware.sqlanalyzer

Description

The SQL Analyzer provides support for database technologies using the ANSI SQL-92/99 language. This extension uses the Universal Analyzer framework and is intended to analyse DDL, DML and SQL exports for a large variety of SQL variants:

  • This extension provides source code analysis support for DDL and DML*.sql files using an over language of the various sql variants.
  • This extension also accepts src and uaxDirectory files. Check here for more details about sqltablesize files.

In what situation should you install this extension?

  • If you need to analyze PostgreSQL, MySQL, MariaDB, SQLite, Db2, Sybase, Microsoft SQL ServerTeradata or Informix
  • If you need to analyze Oracle for the first time or you want to transition to SQL Analyzer and you have not analyzed Oracle Forms / Reports.
  • If your application contains schemas from database vendors not supported "out of the box" by CAST AIP but, which are compliant with ANSI SQL-92 / 99
  • When you do not have access to the online database to perform an extraction for use with CAST AIP and have instead been provided with DDL scripts

Transitioning from from the CAST AIP Db2 Analyzer to the SQL Analyzer extension

If you have been actively analyzing Db2 (z/OS or LUW) with the Db2 Analyzer (provided out-of-the-box in CAST AIP) you can transition to using the SQL Analyzer extension to analyze your Db2 source code. The process of transitioning is described in SQL Analyzer - To do transition from the CAST AIP Db2 Analyzer to the SQL Analyzer extension.

Reversed links

When transitioning from the Db2 Analyzer to the SQL Analyzer, links between Tables and Indexes, Foreign Keys, Primary Keys and Unique Keys will appear to be reversed when comparing the analysis results of the Db2 Analyzer and the SQL Analyzer. This is because the representation of links in the SQL Analyzer uses a different method (which is identical for all supported RDBMS) to the Db2 Analyzer.

Transitioning from the CAST AIP Oracle Analyzer to the SQL Analyzer extension

If you have been actively analyzing Oracle with the Oracle Analyzer (provided out-of-the-box in CAST AIP) you can transition to using the SQL Analyzer extension to analyze your Oracle source code. The process of transitioning is described in SQL Analyzer - To do transition from the CAST AIP Oracle Analyzer to the SQL Analyzer extension.

Analyzing the same code will generate differences in results across object types, object links and structural rules. If you do decide to move to the SQL Analyzer extension, see SQL Analyzer - Mapping between SQL analyzers for more information about what you can expect.


(warning) Important things (warning)

Don't change analyzed sources, keep analyzing src files. 

Only GUIDs are changed during transitions, checksums are recalculated, and that will impact transactions. 

Oracle Forms and Reports particular case

  • If you have already analyzed Oracle Forms and Reports code that is linked to PL/SQL code analyzed with the SQL analyzer embedded in CAST AIP, CAST recommends that you continue using the SQL analyzer embedded in CAST AIP.

Transitioning from the CAST AIP SQL Server Analyzer to the SQL Analyzer extension

If you have been actively analyzing MS SQL Server with the MS SQL Server Analyzer (provided out-of-the-box in CAST AIP) you can transition to using the SQL Analyzer extension to analyze your SQL Server source code. The process of transitioning is described in How to do transition from the CAST MS SQL Server Analyzer to the SQL Analyzer extension.

Analyzing the same code will generate differences in results across object types, object links and structural rules. If you do decide to move to the SQL Analyzer extension, see SQL Analyzer - Mapping between SQL analyzers for more information about what you can expect.


(warning) Important things (warning)

Don't change analyzed sources, keep analyzing src files. 

Only GUIDs are changed during transitions, checksums are recalculated, and that will impact transactions. 

Transitioning from the CAST AIP ASE Sybase Analyzer to the SQL Analyzer extension

If you have been actively analyzing ASE Sybase with the ASE Sybase Analyzer (provided out-of-the-box in CAST AIP) you can transition to using the SQL Analyzer extension to analyze your ASE Sybase source code. The process of transitioning is described in SQL Analyzer - To do transition from the CAST AIP ASE Sybase Analyzer to the SQL Analyzer extension.

Analyzing the same code will generate differences in results across object types, object links and structural rules. If you do decide to move to the SQL Analyzer extension, see SQL Analyzer - Mapping between SQL analyzers for more information about what you can expect.


(warning) Important things (warning)

Don't change analyzed sources, keep analyzing src files. 

Only GUIDs are changed during transitions, checksums are recalculated, and that will impact transactions. 

Vendor compatibility matrix - official support

IconVendorVersionSupported?

IBM Db2 for LUW

Up to version 11.x

(tick)

IBM Db2 for z/OS Up to version 12(tick)

IBM Db2 for i *)Up to version 7.4(tick)

IBM InformixUp to version 14.x(tick)

MariaDBUp to version 10.x(tick)

Microsoft SQL ServerUp to version 2019(tick)

MySQLUp to version 8.x(tick)

Oracle ServerUp to version 19c(tick)

PostgreSQLUp to version 12(tick)

SAP ASE (formerly known as Sybase ASE)Up to version 16(tick)

SQLiteUp to version 3.x(tick)

TeradataUp to version 16(tick)

*) added in 3.5.4-funcrel

Function Point, Quality and Sizing support

This extension provides the following support:

  • Function Points (transactions): a green tick indicates that OMG Function Point counting and Transaction Risk Index are supported
  • Quality and Sizing: a green tick indicates that CAST can measure size and that a minimum set of Quality Rules exist

Function Points
(transactions)

Quality and Sizing

(tick)(tick)

CAST AIP compatibility

This extension is compatible with:

CAST AIP releaseSupported
8.3.x(tick)
8.2.x(tick)
8.1.x(tick)
8.0.x(tick)
7.3.x(error)

Supported DBMS servers used for CAST AIP schemas

This extension is compatible with the following DBMS servers used to host CAST AIP schemas:

CAST AIP releaseCSSOracleMicrosoft
All supported releases(tick)(tick)(tick)

Prerequisites

(tick)An installation of any compatible release of CAST AIP (see table above)

Download and installation instructions

Please see:

  • The latest release status of this extension can be seen when downloading it from the CAST Extend server.
  • Please see Known Limitations and Issues for information about an error that may occur when installing the extension if you have also already installed a very old and unsupported Universal Analyzer langage pack that conflicts with the SQL Analyzer.

Upgrade from the SQL Script extension

If you have previously used the SQL Script extension (com.castsoftware.sqlscript) on existing schemas, you should proceed as following:

  • In CAST Server Manager use the Manage Extensions option on the CAST AIP schemas in which the SQL Script extension is installed
  • Select Analyzer for SQL files and choose deactivate to remove the existing extension. No further actions are required.

Packaging, delivering and analyzing your source code

Please see: SQL Analyzer - 2.x and 3.x - Packaging, delivering and analyzing your source code.

What results can you expect?

Once the analysis/snapshot generation has completed, you can view the results in the normal manner (for example via CAST Enlighten) - click to enlarge:

You can also use the CAST Management Studio option View Analysis Unit Content to see the objects that have been created following the analysis:

Objects

The following objects are displayed in CAST Enlighten:

IconObject TypeDescription

Database

Objects created for MS SQL Server, ASE Sybase and IBM Db2, see details about how database is determined here: SQL Analyzer - How object identity is determined#Howobjectidentityisdetermined-Howdatabaseisdetermined

Schema

Objects created for all RDBMS, see details about how schema is determined here : SQL Analyzer - How object identity is determined#Howobjectidentityisdetermined-Howschemaisdetermined

Table

Objects created with CREATE TABLE statements. 

Records in *.pf *.pf38 files, specific to Db2fori variant. Table name is given by the file name. *)

View

Objects created with CREATE VIEW / REPLACE VIEW statements.

Records in *.lf *.lf38 files, specific to Db2fori variant. View name is given by the file name. *)

Table Column

Columns detected in CREATE TABLE statements.

For CREATE VIEW statements only aliases, the names for the expressions selected by the defining query of the view, are considered as columns names.

IndexObjects created with CREATE INDEX statements.

Foreign Key

Objects created with CREATE TABLE ... (... FOREIGN KEY ..) / ALTER TABLE .... FOREIGN KEY ... statements.

Unique Constraint

Objects created with CREATE UNIQUE INDEX or CREATE PRIMARY KEY statements.

For the CREATE PRIMARY KEY / ALTER TABLE ADD CONSTRAINT PRIMARY KEY / ALTER TABLE ADD PRIMARY KEY / CREATE TABLE .... ( ... PRIMARY/UNIQUE KEY ...) statements, tables are marked as having a primary key.

Key in *.pf *.pf38 files, specific to Db2fori variant. Unique constraint name is given by the file name prefixed with PK_. *)

TriggerObjects created with CREATE TRIGGER / CREATE RULE statements.

EventObjects created with CREATE EVENT statements.

MacroObjects created with CREATE MACRO statements.

Package Objects created with CREATE PACKAGE/PACKAGE BODY statements.

ProcedureObjects created with CREATE PROCEDURE / REPLACE PROCEDURE statements. The same type cover Package's procedures.

FunctionObjects created with CREATE FUNCTION statements. The same type cover Package's functions.

TypeObjects created with CREATE TYPE/TYPE BODY statements.

MethodObject Type's methods.

SynonymObject created with CREATE SYNONYM, CREATE ALIAS and CREATE NICKNAME statements.

Database LinkObject created with CREATE DATABASE LINK statements.

Unresolved Schema

When objects accessed via a database link in a DML statement are not resolved, they are missing from analysis result, an Unresolved table or Unresolved Object is created, in a Unresolved Schema.

When the object is more "table like", we add an Unresolved Table.

When the object is more "procedure / function like" we add an Unresolved Object.

In the database link access objects are prefixed with schema name, that schema will belongs to the Database Link object, Unresolved Table / Object will belongs to Unresolved Schema. 

See example here : 416055656 and there : SQL Analyzer - How object identity is determined#Howobjectidentityisdetermined-Unresolvedobjects.

Unresolved Table

Unresolved Object

DML Script File

File with DML SQL statements, like INSERT/UPDATE/DELETE/etc.

DML Script File candidates with more than 80% INSERT statements are considered as data exports and will not be analyzed.

Sometimes DML and DDL statements are mixed and DML Script Files are also considered as DDL.

sourceFile

This is a logical file.

For each source file we add a property named Vendor, see SQL file Vendor - This file is analyzed against XXX variant

*) starting with 3.5.4-funcrel

Table deletion and renaming

DROP TABLE syntax is supported for table objects within the same file. When creating a table through CREATE TABLE tableName (colName int, ...) followed by a DROP TABLE tableName, the table will not be recorded and thus will not be displayed in CAST Enlighten. Similarly, if a table is renamed with a RENAME TABLE statement (or ALTER TABLE RENAME TO as in SQLite and PostgreSQL), this change will be reflected in CAST Enlighten. Presently we consider case-insensitive names, i.e., objects named tableName, TABLEname are considered to be the same object.

Database object

Db2 analyses

Please read this note if you move from sqlanalyzer <= 2.6.9-funcrel to sqlanalyzer >= 3.0.0-alpha2 and if the AIP core is from 8.3.16 to 8.3.28:

The following CREATE TABLE ... IN ... statements will generate a new object: a database for Db2 analyses:

CREATE TABLE .... () ... IN DATABASE_NAME.TABLESPACE_NAME ....  --see ABC 02 example

CREATE TABLE .... () IN DATABASE DATABASE_NAME --see ABC 03 example

Object Full Name will change from <SCHEMA NAME>.<OBJECT NAME> to <DATABASE NAME>.<SCHEMA NAME>.<OBJECT NAME:

prior to 3.0.0-alpha23.0.0-alpha2

SQL Server analyses

Please read this note if you move from sqlanalyzer < 3.4.0-beta5 to sqlanalyzer >= 3.4.0-beta5 and if the AIP core is from 8.3.16 to 8.3.28:

The following statements will generate a database for SQL Server and Sybase analyses:

USE <DATABASE_NAME>

CREATE DATABASE <DATABASE_NAME>

CREATE <OBJECT_TYPE>*)  <DATABASE_NAME>.<SCHEMA_NAME>.<OBJECT_NAME>

*) TABLE, VIEW, etc

Object Full Name will change from <SCHEMA NAME>.<OBJECT NAME> to <DATABASE NAME>.<SCHEMA NAME>.<OBJECT NAME.

Database Link object

Added support for CREATE DATABASE LINK statement : https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm .

The caller object is linked with database link object via relyOn links. The called object is directly linked to the caller object, via Use / Call links.

Example 1 : with a call via database link 

Create Procedure MYPROC_DBLINK
Is
Begin
  MYREMOTESCHEMA.MYREMOTEPROC@DBLINK.DOMAIN(0, 'string1');
End;
 /
 CREATE SHARED PUBLIC DATABASE LINK DBLINK.DOMAIN
  CONNECT TO TOTO
  USING 'TOTO.db.toto.com'
/
 create procedure MYREMOTESCHEMA.MYREMOTEPROC(I_1 int, 
 I_2 varchar2(500)
 as
 begin
     return 1;
 end;
 /

The caller object, MYPROC_DBLINK, is linked with :

  • the database link object DBLINK.DOMAIN via a relyOn link
  • the called object MYREMOTEPROC via a call link

Example 2 : with a call via a synonym on a database link 

create procedure MYREMOTESCHEMA.MYREMOTEPROC(I_1 int, 
 I_2 varchar2(500)
 as
 begin
     return 1;
 end;
 /
CREATE SHARED PUBLIC DATABASE LINK DBLINK.DOMAIN
  CONNECT TO TOTO
  USING 'TOTO.db.toto.com'
/
CREATE PUBLIC SYNONYM MYPROC_SYN_DBLINK 
   FOR MYREMOTESCHEMA.MYREMOTEPROC@DBLINK.DOMAIN;
/
 Create Procedure MYPROC_DBLINK
Is
Begin
  MYPROC_SYN_DBLINK(0, 'string1');
End;
/

The caller object, MYPROC_DBLINK, is linked with the called object MYREMOTEPROC via a call link

The PUBLIC synonyms is on a procedure accessed via a database link and has relyOn links with database link DBLINK.DOMAIN and the real procedure MYREMOTEPROC .

Unresolved objects

When the object accessed via a database link is missing, we add an Unresolved object directly attached to the database link.

 CREATE SHARED PUBLIC DATABASE LINK DBLINK.DOMAIN
  CONNECT TO TOTO
  USING 'TOTO.db.toto.com'
/
Create Procedure MYPROC_UNRESOVLED_DBLINK
Is
Begin
  select MYREMOTESCHEMA.MYREMOTEPROC@DBLINK.DOMAIN()
  from dual;
End;
 /

The caller object, MYPROC_UNRESOLVED_DBLINK, is linked with the called object MYREMOTEPROC via a call link

The PUBLIC synonyms is on a procedure accessed via a database link and has relyOn links with database link DBLINK.DOMAIN and the real procedure MYREMOTEPROC.

MYREMOTEPROC is missing from analysis result, so it is considered Unresolved. 

SQL Analyzer - How object identity is determined#Howobjectidentityisdetermined-Unresolvedobjects

Oracle Public schema

Synonyms and Database Links declared as PUBLIC are from now attached to PUBLIC schema.

Example:

 CREATE SHARED PUBLIC DATABASE LINK DBLINK.DOMAIN
  CONNECT TO TOTO
  USING 'TOTO.db.toto.com'
/

Links are created for transaction and function point needs: 

DDL

You can expect the following links on the DDL side within the same sql file:

  • useSelect, useInsert, useUpdate, useDelete Links from Procedure / Function / MacroEvent  to Table / View
  • callLink from Procedure / Function / Event to Procedure / Function
  • callLink from Macro to Function
  • callLink from Procedure / Function  to Cobol Program


  • callLink from Procedure / Function  to C/C++ Function

  • callLink from Procedure / Function  to Java Method


  • useSelect from View to Table / View used in the query of the view
  • callLink from View to Function
  • relyonLink from Index to the Table
  • relyonLink from Index to the Column implied in the index
  • relyonLink from Synonym to Table / View / Function / Procedure / Package aliased by Synonym
  • referLink from Table / Table Column to a Table / Table Column referenced in a Foreign Key 
  • referDelete, referUpdate from Table to a Table referenced in a Foreign Key. Examples:
    • referDelete:
referUpdate
ALTER TABLE A.TABLE_1 ADD CONSTRAINT FK_NAME_1 FOREIGN KEY (COL_1) REFERENCES A.TABLE_2 (COL_2) ON DELETE;
    • referUpdate:
referUpdate
ALTER TABLE A.TABLE_1 ADD CONSTRAINT FK_NAME_1 FOREIGN KEY (COL_1) REFERENCES A.TABLE_2 (COL_2) ON UPDATE;
    • referDelete with a Referential Action property Restrict for Delete
referDelete
ALTER TABLE A.TABLE_1 ADD CONSTRAINT FK_NAME_1 FOREIGN KEY (COL_1) REFERENCES A.TABLE_2 (COL_2) ON DELETE RESTRICT;
    • referDelete with a Referential Action property Cascade for Delete : 
referDelete
ALTER TABLE A.TABLE_1 ADD CONSTRAINT FK_NAME_1 FOREIGN KEY (COL_1) REFERENCES A.TABLE_2 (COL_2) ON DELETE CASCADE;
    • referDelete with a Referential Action property Cascade for Delete , referUpdate with a Referential Action property No Action for Update
referDelete - referUpdate
CREATE TABLE IF NOT EXISTS `TABLE_1` (
  `COL_1` int NOT NULL,
  `COL_2` char(4) COLLATE latin1_bin NOT NULL,
  KEY `FK__TABLE_1__TABLE_2___5AEE82B9` (`COL_1`),
  CONSTRAINT `FK__TABLE_1__TABLE_2___5AEE82B9` FOREIGN KEY (`COL_1`) REFERENCES `TABLE_1` (`COL_1`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
    • referDelete with a Referential Action property No Action for Delete , referUpdate with a Referential Action property No Action for Update

    • referDelete with a Referential Action property Cascade for Delete 

  • callLink to the correct Trigger where the tables is accessed in insert/update/delete
    • example a Trigger declared as BEFORE INSERT on a table, any insert to that table will call the trigger...

  • example: a table, with an after update trigger, updated in a Client code :

  • inheritLink from sub Type to super Type:
    • example : CREATE OR REPLACE type gayerrortype under gayxmlmsg ....

  • useLink for PL/SQL tables, example : CREATE OR REPLACE TYPE "CNCUBLIST_2" is table of "HIS_TEST_A" ;

DML

You can expect the following links on the DML side :

  • useSelect, useInsert, useUpdate, useDelete Links from SQL Script to Table / View
  • call Links from SQL Script  to Procedure / Function / Macro

Rules

You can find a full list of rules delivered with this extension here:


Client Side Support : COBOL, PB, VB, .NET, JAVA, C/C++, PYTHON, DML code

Name
Never use SQL queries with a cartesian product (SQL) (1101000)
Never use SQL queries with a cartesian product on XXL Tables (SQL) (1101002)
Avoid non-indexed SQL queries (1101004)
Avoid non-indexed XXL SQL queries (1101006)
Avoid non-SARGable queries (1101008)
Avoid NATURAL JOIN queries (1101010)
Specify column names instead of column numbers in ORDER BY clauses (1101012)
Avoid queries using old style join convention instead of ANSI-Standard joins (SQL) (1101014)
Always define column names when inserting values (1101026)
Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries (1101028)

Avoid exists and not exists independent clauses (SQL) (1101032)

DISTINCT should not be used in SQL SELECT statements (1101034)

Use ANSI standard operators in SQL WHERE clauses (EMBEDDED SQL) (1101036)

Replace OR conditions testing equality on the same identifier in SQL WHERE clauses by an IN test condition (1101038)

Do not mix ANSI and non-ANSI JOIN syntax in the same query (1101058)
LIKE operator should not start with a wildcard character (1101060)
Avoid improperly written  triangular joins with XXL tables (1101066)
Avoid explicit comparison with NULL (1101070)
Tables should be aliased (1101072)
Tables aliases should not end with a numeric suffix (1101074)
Column references should be qualified (1101076)

Special note about XXL/XXS support

See SQL Analyzer - working with XXL or XXS tables for more information.

Special notes about Quality Rules on client side

Some Quality Rules are calculated on SQL queries on the client-side with some limitations:

  • Quality Rules will be enabled on client-side code only if the server-side code has been analyzed with SQL Analyzer extension.
  • For Java client-side code, SQL statements used in parameters of methods including a SQL parametrization rule are analyzed.
Example of call to a parametrized method
class Foo
{
   final static String TABLE_NAME = "Person";

	void method()
	{
    	String query = "select * from " + this.TABLE_NAME;
    	java.sql.Statement.execute(query );
	}
}
  • But 'queries' visible in the DLM (that need reviewing) are not analyzed:
Example ofa query visible in the DLM
class Foo
{
	// not passed to an execute something
	private final static String text = "select name from Person";
}
  • Explicit queries used in an ORM context are analyzed (or not) based on if they are visible in Enlighten

  • COBOL EXEC SQL queries are analyzed

  • SQL queries founded in Python code

  • SQL queries founded in .properties (Java Property Mapping objects) are analyzed

Special note about redundant Quality Rules

Please see SQL Analyzer - Redundant Quality Rules not included in the SQL Analyzer.

Errors and warning

Please see SQL Analyzer - errors and warnings for the full list of analysis errors and warnings.

Known limitations and issues

Installation

If you encounter the following error in CAST Server Manager while installing the SQL Analyzer extension, please perform the workaround described here and then attempt the installation again. This error may occur if you have installed a very old and unsupported custom Universal Analyzer language pack that used the same metamodel type names as used in the official SQL Analyzer extension.

SQL Analyzer is incompatible with the schema metamodel. It is generally due to an extension that has changed its ids.

Analysis

  • All name resolution is considered as case insensitive: this may produce erroneous links on a case insensitive platform 'playing with case': two different tables with the same case insensitive name will be both called
  • Procedure resolution handles overriding when the number of parameters are matched or number and optionals are matched. Otherwise, when calling an overridden procedure, all overrides will be called. Below are some examples here is a single call Link, between the second func1 and func2:
Match number of parameters
CREATE FUNCTION func1() RETURNS integer AS
begin
    DELETE FROM table1 WHERE ID in (SELECT ID FROM table2);
end;

CREATE FUNCTION func1(mode integer) RETURNS integer AS
begin
    DELETE FROM table1 WHERE ID (SELECT ID FROM table2);
end;

CREATE FUNCTION func2(mode integer) RETURNS integer AS
begin
    select func1 (mode); 
end;
Match number of parameters and how many are optionals
CREATE FUNCTION func1(i_mode integer) RETURNS integer AS
begin
    DELETE FROM table1 WHERE ID in (SELECT ID FROM table2);
end;

CREATE FUNCTION func1(mode integer := 1) RETURNS integer AS
begin
    DELETE FROM table1 WHERE ID in (SELECT ID FROM table2);
end;

CREATE FUNCTION func2(mode integer) RETURNS integer AS
begin
    select func1 (); 
end;
  • Dynamic SQL statements are resolved when:
    • the SQL statement is readable, even for sliced statements.
    • TABLE1, TABLE2, TABLE3 and TABLE4 are visibles and useSelect link were be added

CREATE PROCEDURE test
AS
L_QryStr varchar2(4000);
begin
L_QryStr := 'select S.COL1, P.COL2, P.COL3, P.COL4, P.COL5, ' ||
' P.COL6, B.COL7, R.COL8, B.COL9, B.COL10' ||
' from TABLE1 S, TABLE2 P, TABLE3 B, ' ||
' ( select distinct R.COL1, R.COL2 ' ||
' from TABLE4 R ' ||
' where R.COL3 = 99999 ';
EXECUTE IMMEDIATE L_QryStr;
end;
/
    • test_table is visible and useDelete link is added:
CREATE PROCEDURE test
AS
begin
EXECUTE IMMEDIATE 'truncate table test_table'; 
end;
/
    • test_table is visible and useDelete link is added:
CREATE PROCEDURE test
AS
L_QryStr varchar2(4000);
begin
	L_QryStr := 'truncate table ';
	L_QryStr := L_QryStr || ' test_table ';
	EXECUTE IMMEDIATE L_QryStr; 
end;
/
    • table name is valued via a variable which could be resolved.
    • emp table is visible and useSelect link will be added
CREATE PROCEDURE test
AS
emp_rec  emp%ROWTYPE;
sql_stmt VARCHAR2(200);
begin
    sql_stmt := 'SELECT * FROM || emp_rec.T1 ||  WHERE job = 1';
    EXECUTE IMMEDIATE sql_stmt;
end;
/
    • emp table is visible and useSelect link will be added
CREATE OR REPLACE package body test_package as
 
 type T_1 is table of varchar2(22);
 emp_rec  emp%ROWTYPE;

 PROCEDURE test
is
sql_stmt VARCHAR2(2000);
emp_tab VARCHAR2(200);
begin
    emp_tab := emp_rec;
    sql_stmt := 'SELECT * FROM || emp_tab ||  WHERE job = :j';
    EXECUTE IMMEDIATE sql_stmt;
end test;
 
end;
/
    • OPEN-FOR-USING : emp table and test procedure are linked by a use SELECT link

create table emp (col1 int);
/
CREATE OR REPLACE type body test_body as

 type t_emp is table of t_table index by varchar2(22);
 emp_rec  emp%ROWTYPE;

 member PROCEDURE test(o_cursor OUT my_cursor)
IS
BEGIN
   OPEN    o_cursor FOR
   'SELECT  rule_id,
           expression_id,
           parent_expression_id,
           operator
   FROM    emp
   ORDER   BY rule_id, expression_id';
END test;

end;
/

create table emp (col1 int);
/
CREATE OR REPLACE type body test_body as

 type t_emp is table of t_table index by varchar2(22);
 emp_rec  emp%ROWTYPE;

 member PROCEDURE test(o_cursor OUT my_cursor)
IS
L_SQL varchar(20000) := '';
BEGIN
    L_SQL := 'SELECT  rule_id,
           expression_id,
           parent_expression_id,
           operator
   FROM    emp
   ORDER   BY rule_id, expression_id';
   
   OPEN o_cursor FOR L_SQL;
END test;

end;
/
  • ALTER TABLE ... ADD ... syntax is supported. All other syntaxes, such as ALTER TABLE ... DELETE .. or ALTER TABLE ... DROP ... or ALTER TABLE ... MODIFY ... etc. are not supported.

  • Moving a table from one database/scheme to another is not supported through RENAME TABLE schema1.tableName1 TO schema2.tableName2.   
  • Sequences are not taken in to account and that is not a limitation but a choice because they have no effect on transactions nor Quality Rules
  • Oracle synonyms on packages are not taken in to account.
  • For the QR 1101012 Specify column names instead of column numbers in ORDER BY clauses, the case when a function that returns a number or a numeric variable is used in order by is not reported to violate the rule.

CAST Health Dashboard (ex. Application Analytics Dashboard)

Starting with release 2.1 of the SQL Analyzer extension, the name used to represent the technology has changed from SQL Analyzer to SQL: if you have already transferred snapshots that contain SQL Analyzer analysis results in to a Measurement Service schema, you'll see SQL Analyzer in the list of technologies instead of SQL.

If you would like to change the technology name for existing snapshots, you can change it using the following SQL query run against the Measurement Service schema:

Change technology name in AAD
update DSS_OBJECT_TYPES
set OBJECT_TYPE_NAME = 'SQL'
where OBJECT_TYPE_ID = 1101000
  • No labels