Trivadis PL/SQL & SQL CodeAnalyzer Released

A month ago I talked about “Extending the Oracle Data Dictionary for Fine-Grained PL/SQL and SQL Analysis” during the ODTUG Kscope13 conference in New Orleans. Oracle data dictionary views as DBA_IDENTIFIERS or DBA_DEPENDENCIES are in many cases sufficient to analyze static PL/SQL and SQL code within the Oracle database. But what if more detailed analyses are required, such as the use of tables or columns in PL/SQL package units, in SQL statements or in SQL statement clauses? Wouldn’t a DBA_OBJECT_USAGE view – showing DML and query operations on tables/views per database object – be a helpful tool?

TVDCA – the Trivadis PL/SQL and SQL CodeAnalyzer – is such a tool and helps you to overcome several analysis restrictions in an Oracle 10g, 11g or 12c database. At Kscope13 some of my attentive session attendees got a USB stick with TVDCA 0.4.1 Beta. In the meantime, I was busy fixing bugs to proudly present you an updated trial/preview version free of charge in the download section of this blog.

The following query might give you an idea of the functionality of tvdca:

SQL> SELECT object_name, procedure_name, operation, table_name, column_name
  2    FROM tvd_object_col_usage_v
  3   WHERE owner = 'TVDCA'
  4         AND object_type = 'PACKAGE BODY';

OBJECT_NAME    PROCEDURE_NAME OPERATION TABLE_NAME           COLUMN_NAME
-------------- -------------- --------- -------------------- ----------------
TVD_COLDEP_PKG GET_DEP        SELECT    DBA_DEPENDENCIES     NAME
TVD_COLDEP_PKG GET_DEP        SELECT    DBA_DEPENDENCIES     OWNER
TVD_COLDEP_PKG GET_DEP        SELECT    DBA_DEPENDENCIES     REFERENCED_NAME
TVD_COLDEP_PKG GET_DEP        SELECT    DBA_DEPENDENCIES     REFERENCED_OWNER
TVD_COLDEP_PKG GET_DEP        SELECT    TVD_PARSED_OBJECTS_V OBJECT_NAME
TVD_COLDEP_PKG GET_DEP        SELECT    TVD_PARSED_OBJECTS_V OBJECT_TYPE
TVD_COLDEP_PKG GET_DEP        SELECT    TVD_PARSED_OBJECTS_V OWNER
TVD_COLDEP_PKG PROCESS_VIEW   SELECT    DBA_TAB_COLUMNS      COLUMN_ID
TVD_COLDEP_PKG PROCESS_VIEW   SELECT    DBA_TAB_COLUMNS      OWNER
TVD_COLDEP_PKG PROCESS_VIEW   SELECT    DBA_TAB_COLUMNS      TABLE_NAME

Please have a look at my slides or the information in the download section if you are interested in learning more about tvdca.

Comments are closed.