PL/SQL Analyzer

 

Version 1.2.3
Last Update 2017-09-22
License valid to 2018-02-28
File size 40.9 MB
Downloads 1,445

Introduction

Trivadis PL/SQL Analyzer is a command line utility to parse PL/SQL and SQL source code within an Oracle database and store the resulting XML parse-trees in dedicated relational tables – as an extension to the Oracle Data Dictionary – for further analysis.

Analysis is supported for static and dynamic code.

Sample analyses are provided as views and pipelined table functions, e.g.:

  • TVD_OBJECT_HINT_USAGE_V: Used hints in static code.
  • TVD_OBJECT_USAGE_V: Used tables/views in static code.
  • TVD_OBJECT_COL_USAGE_V: Used columns in static code.
  • TVD_COLDEP_PKG: View column dependencies.
  • TVD_SQL_HINT_USAGE_V: Used hints in captures SQL statements.
  • TVD_SQL_USAGE_V: Used tables/views in captured SQL statements.
  • TVD_SQL_COL_USAGE_V: Used columns in captured SQL statements.

The trial/preview version of PL/SQL Analyzer is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.

Concepts

Scope

The primary scope of PL/SQL Analyzer is the analysis of SQL and PL/SQL code within an Oracle database instance as shown in the following picture:

tvdca_scope

Please consider using standard tooling from Oracle before using PL/SQL Analyzer. Oracle provides in that area the following support:

  • Oracle data dictionary views such as DBA_DEPENDENCIES, DBA_SOURCE, DBA_PROCEDURES, DBA_ARGUMENTS, DBA_TYPES, DBA_TYPE_METHODS, DBA_TRIGGERS, DBA_VIEWS, DBA_TABLES
  • PL/Scope and its data dictionary views DBA_IDENTIFIERS and DBA_STATEMENTS (see also the free plscope-utils for SQL Developer)
  • Oracle parser such as UTL_XML.PARSEQUERY (which is a good choice for SELECT statements without plsql_declarations in the with_clause)

As soon as you need deeper information about SQL statements in your code, e.g. column usage in general or in certain clauses (e.g. in the select_list), PL/SQL Analyzer becomes appealing.

Oracle Dictionary Extension for Static Code Analysis

PL/SQL Analyzer populates the following structure as an extension to the Oracle data dictionary for static analysis:

The columns OBJECT_ID, OWNER, OBJECT_NAME, OBJECT_TYPE and LAST_DDL_TIME have the same semantic as the columns in the Oracle data dictionary view DBA_OBJECTS. The column DDL_SOURCE contains the result of a DBMS_METADATA.GET_DDL function call. The column PARSE_TREE contains the parse tree in XML format. The column PARSE_ERRORS contains error messages that occurred during parse or XML serialization. Errors may occur if the DDL contains invalid code; or the DDL contains control characters which may not be serialized in XML; or the Trivadis PL/SQL parser is not able to fully parse the code. However, a partial parse tree is created even if the Trivadis PL/SQL parser throws an error. In that case typically just the failing SQL statement is missing.

The following picture visualizes the population process of the view tvd_parsed_objects_v:

tvdca_parsed_objects

Just outdated rows in DBA_OBJECTS are processed (identified via column LAST_DDL_TIME).

Oracle Dictionary Extension for Dynamic Code Analysis

As explained in chapter “Scope” PL/SQL Analyzer is primarily designed for static code analysis. However, it’s possible to analyze dynamic PL/SQL and SQL if the code is captured in the following structure:

The column CAP_ID is the primary key of the table and should be populated with a sequence value from TVD_CAPTURED_SQL_SEQ to avoid duplicated entries. The column CAP_SOURCE contains the captured SQL or PL/SQL statement. It does not matter how this structure is populated, it simply updates the columns PARSE_TREE and PARSE_ERRORS if the PARSE_TREE column IS NULL (PARSE_TREE_CLOB is a workaround for XMLTYPE bugs). All other columns are optional and may give the SQL a context.

The structure above could be populated on an Oracle instance as follows:

Please note that the column sql_fulltext is not available in v$sql_area before version 11g Release 1 and that the SQL statement gathering process is not part of PL/SQL Analyzer.

Custom Analysis

The sample analysis views and PL/SQL packages show you how the analysis based on the views tvd_parsed_objects_v and tvd_captured_sql_v could be done.

The idea is to query the relevant information from the XML parse tree using XQuery and present the result in a relational structure using the Oracle function XMLTABLE and do all further analysis in classic SQL based way.

It’s recommended to develop the XQuery using an XML editor supporting XQuery.

See XML schema documentation in the xsd directory. The complex type PLSQLFile is a good starting point in the HTML documentation.