Important Announcement
This forum will be discontinued.
The creation of new topics is disabled.
Please open GitHub issues for the corresponding topics/products in the following Trivadis repositories:
- PL/SQL & SQL Coding Guidelines
- PL/SQL Cop Command Line
- PL/SQL Cop for SonarQube
- PL/SQL Cop for SQL Developer
- PL/SQL Cop Validators
- PL/SQL Analyzer
- PL/SQL Unwrapper
A lot of users have already a GitHub account and the management of the issues is better than in this forum. For example, closing a forum topic is never related to a product change. Closing a GitHub issue for an accepted bug means that the bug is fixed. This should simplify the work for all involved parties.
See this blog post for more information.
Thank you for your understanding.
I downloaded PL/SQL analyzer
Readme document mentions about analyzing "Static SQL or PL/SQL code"
Is this for analyzing standalone SQL files (not compiled in database) , or is this for Dynamic SQL?
It would be of great help if you could point to some example or demo of this
PL/SQL Analyzer is primarily used to analyse code installed in the Oracle database. To analyse all objects in the schema PLSCOPE you may run the following command, after creating the Oracle user TVDCA as described in the ReadMe.pdf:
tvdca.sh user=tvdca password=tvdca host=localhost sid=odb schema=PLSCOPE
DDLs found in the schema PLSCOPE are stored in the table TVD_PARSED_OBJECTS_T. Each DDL is also represented as a parse tree in XML format. These XML documents are used for further analysis.
For example, the query 
SELECT operation, table_name 
  FROM tvd_object_usage_v
 WHERE owner = 'PLSCOPE'
   AND object_type = 'PACKAGE BODY'
   AND object_name = 'ETL'
   AND procedure_name = 'LOAD_FROM_TAB'
 ORDER BY operation, table_name;
will produce the following result:
OPERATION TABLE_NAME ---------- ------------------------------ INSERT DEPTSAL SELECT DEPT SELECT EMP
Have a look at the source code of the view TVD_OBJECT_USAGE_V how data is extracted from the XML parse tree. Please note that this and other provided views are just considered examples. You typically will need to write your own XQueries for your analysis. 
The DDL excerpt for this example is
PROCEDURE load_from_tab IS
BEGIN
clear_deptsal;
INSERT INTO deptsal (dept_no, dept_name, salary)
SELECT /*+ordered */ d.deptno, d.dname, SUM(e.sal + NVL(e.comm, 0)) AS sal
FROM dept d
LEFT JOIN (SELECT * FROM emp WHERE hiredate > DATE '1980-01-01') e
ON e.deptno = d.deptno
GROUP BY d.deptno, d.dname;
COMMIT;
sys.dbms_output.put_line('deptsal loaded and commited (from table).');
END load_from_tab;
And its parse tree representation in XML looks as follows:
<items xsi:type="plsql:ProcedureDefinition">
<heading>
<sqlObject value="load_from_tab"/>
</heading>
<declareSection xsi:type="plsql:ItemList"/>
<body>
<statements xsi:type="plsql:ProcedureCallOrAssignmentStatement">
<procedureOrTarget>
<object value="clear_deptsal" xsi:type="plsql:SimpleExpressionNameValue"/>
</procedureOrTarget>
</statements>
<statements xsi:type="plsql:InsertStatement">
<singleTableInsert>
<intoClause>
<dmlExpressionClause>
<dmlName value="deptsal"/>
</dmlExpressionClause>
<columns>
<names value="dept_no"/>
</columns>
<columns>
<names value="dept_name"/>
</columns>
<columns>
<names value="salary"/>
</columns>
</intoClause>
<subquery>
<query>
<query xsi:type="plsql:QueryBlock">
<selectList>
<selected xsi:type="plsql:SingleColumnExpression">
<expression binaryOperator="." xsi:type="plsql:BinaryCompoundExpressionLevel6">
<left value="d" xsi:type="plsql:SimpleExpressionNameValue"/>
<right value="deptno" xsi:type="plsql:SimpleExpressionNameValue"/>
</expression>
</selected>
<selected xsi:type="plsql:SingleColumnExpression">
<expression binaryOperator="." xsi:type="plsql:BinaryCompoundExpressionLevel6">
<left value="d" xsi:type="plsql:SimpleExpressionNameValue"/>
<right value="dname" xsi:type="plsql:SimpleExpressionNameValue"/>
</expression>
</selected>
<selected xsi:type="plsql:SingleColumnExpression">
<expression xsi:type="plsql:BinaryCompoundExpressionLevel7">
<left value="SUM" xsi:type="plsql:SimpleExpressionNameValue"/>
<right xsi:type="plsql:ParenthesisExpression">
<parameterListExpression xsi:type="plsql:FunctionOrParenthesisParameterListExpression">
<param>
<parameter>
<parameterValue binaryOperator="+" xsi:type="plsql:BinaryCompoundExpressionLevel2">
<left binaryOperator="." xsi:type="plsql:BinaryCompoundExpressionLevel6">
<left value="e" xsi:type="plsql:SimpleExpressionNameValue"/>
<right value="sal" xsi:type="plsql:SimpleExpressionNameValue"/>
</left>
<right xsi:type="plsql:BinaryCompoundExpressionLevel7">
<left value="NVL" xsi:type="plsql:SimpleExpressionNameValue"/>
<right xsi:type="plsql:ParenthesisExpression">
<parameterListExpression xsi:type="plsql:FunctionOrParenthesisParameterListExpression">
<param>
<parameter>
<parameterValue binaryOperator="." xsi:type="plsql:BinaryCompoundExpressionLevel6">
<left value="e" xsi:type="plsql:SimpleExpressionNameValue"/>
<right value="comm" xsi:type="plsql:SimpleExpressionNameValue"/>
</parameterValue>
</parameter>
</param>
<param>
<parameter>
<parameterValue value="0" xsi:type="plsql:SimpleExpressionNumberValue"/>
</parameter>
</param>
</parameterListExpression>
</right>
</right>
</parameterValue>
</parameter>
</param>
</parameterListExpression>
</right>
</expression>
<alias value="sal"/>
</selected>
</selectList>
<fromList>
<elements>
<queryable>
<tableReference>
<queryTableExpression>
<qteName value="dept"/>
</queryTableExpression>
<tableAlias>
<alias value="d"/>
</tableAlias>
</tableReference>
</queryable>
<join xsi:type="plsql:OuterJoinClause">
<queryable>
<tableReference>
<queryTableExpression>
<subquery>
<query>
<query xsi:type="plsql:QueryBlock">
<selectList>
<selected xsi:type="plsql:MultiColumnExpression">
<wildcard xsi:type="plsql:AllColumnWildcardExpression"/>
</selected>
</selectList>
<fromList>
<elements>
<tableReference>
<queryTableExpression>
<qteName value="emp"/>
</queryTableExpression>
<tableAlias/>
</tableReference>
</elements>
</fromList>
<whereClause>
<condition xsi:type="plsql:BinaryComparisonConditionLevel4">
<left value="hiredate" xsi:type="plsql:SimpleExpressionNameValue"/>
<operator xsi:type="plsql:GTOperator"/>
<right literal="1980-01-01" xsi:type="plsql:DateLiteralExpression">
<date value="DATE" xsi:type="plsql:SimpleExpressionDateValue"/>
</right>
</condition>
</whereClause>
<groupByClause/>
</query>
<rowLimitingClause/>
</query>
</subquery>
</queryTableExpression>
<tableAlias>
<alias value="e"/>
</tableAlias>
</tableReference>
</queryable>
<outerJoinType leftOuterJoin="true"/>
<condition xsi:type="plsql:BinaryComparisonConditionLevel4">
<left binaryOperator="." xsi:type="plsql:BinaryCompoundExpressionLevel6">
<left value="e" xsi:type="plsql:SimpleExpressionNameValue"/>
<right value="deptno" xsi:type="plsql:SimpleExpressionNameValue"/>
</left>
<operator xsi:type="plsql:EQOperator"/>
<right binaryOperator="." xsi:type="plsql:BinaryCompoundExpressionLevel6">
<left value="d" xsi:type="plsql:SimpleExpressionNameValue"/>
<right value="deptno" xsi:type="plsql:SimpleExpressionNameValue"/>
</right>
</condition>
</join>
</elements>
</fromList>
<groupByClause>
<groupByItems binaryOperator="." xsi:type="plsql:BinaryCompoundExpressionLevel6">
<left value="d" xsi:type="plsql:SimpleExpressionNameValue"/>
<right value="deptno" xsi:type="plsql:SimpleExpressionNameValue"/>
</groupByItems>
<groupByItems binaryOperator="." xsi:type="plsql:BinaryCompoundExpressionLevel6">
<left value="d" xsi:type="plsql:SimpleExpressionNameValue"/>
<right value="dname" xsi:type="plsql:SimpleExpressionNameValue"/>
</groupByItems>
</groupByClause>
<hints>/*+ordered */</hints>
</query>
<rowLimitingClause/>
</query>
</subquery>
</singleTableInsert>
</statements>
<statements xsi:type="plsql:CommitStatement"/>
<statements xsi:type="plsql:ProcedureCallOrAssignmentStatement">
<procedureOrTarget>
<object binaryOperator="." xsi:type="plsql:BinaryCompoundExpressionLevel6">
<left binaryOperator="." xsi:type="plsql:BinaryCompoundExpressionLevel6">
<left value="sys" xsi:type="plsql:SimpleExpressionNameValue"/>
<right value="dbms_output" xsi:type="plsql:SimpleExpressionNameValue"/>
</left>
<right xsi:type="plsql:BinaryCompoundExpressionLevel7">
<left value="put_line" xsi:type="plsql:SimpleExpressionNameValue"/>
<right xsi:type="plsql:ParenthesisExpression">
<parameterListExpression xsi:type="plsql:FunctionOrParenthesisParameterListExpression">
<param>
<parameter>
<parameterValue value="deptsal loaded and commited (from table)." xsi:type="plsql:SimpleExpressionStringValue"/>
</parameter>
</param>
</parameterListExpression>
</right>
</right>
</object>
</procedureOrTarget>
</statements>
</body>
<endProcedure value="load_from_tab"/>
</items>
So basically PL/SQL Analyzer provides parse trees of SQL and PL/SQL code in XML format for further analysis. Storing the XML  parse trees is helpful if you plan to join the extracted data with the Oracle data dictionary views or other relational data.
If you want to do static code analysis from SQL and PL/SQL code stored in files without a database connection, then other tools such as PL/SQL Cop might be more suited.