PL/Scope was introduced with Oracle Database version 11.1 and covered PL/SQL only. SQL statements such as SELECT, INSERT, UPDATE, DELETE and MERGE were simply ignored. Analysing PL/SQL source code without covering SQL does not provide a lot of value. Hence, PL/Scope was neglected by the Oracle community. But this seems to change with version 12.2. PL/Scope covers SQL statements, finally. This makes fine grained dependency analysis possible. Fine grained means on column level and on package unit level (procedure/function).
PL/Scope is something like a software development kit (SDK) for source code analysis. It consists basically of the following two components:
- The compiler, which collects information when compiling source code (e.g. plscope_settings=’identifiers:all’)
- The dictionary views, providing information about collected identifiers (dba_identifiers) and SQL statements (dba_statements).
The provided views are based on a recursive data structure which is not that easy to understand. Querying them will soon need recursive self joins and joins to other Oracle data dictionary views. Everybody is going to build some tools (scripts, reports, views, etc.) for their analysis. Wouldn’t it make sense to have some open sourced library doing that once for all? – Obviously the answer is yes. This library exists. It is available as a GitHub repository named plscope-utils.
Compile
First you have to compile the source code you’d like to analyse. All source code, in all relevant schemas. If you do not compile them, they cannot be referenced and therefore they will be simply missing in your analysis. This step is completely independent of whether you are going to use plscope-utils or not.
Here’s a simplistic ETL example using emp and dept as source data.
|
1 |
ALTER SESSION SET plscope_settings='identifiers:all, statements:all'; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE OR REPLACE PROCEDURE load_from_tab IS BEGIN 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; END load_from_tab; / |
Views by plscope-utils
After installing plscope-utils, you have access to the following 5 views from any user within the database instance:
- plscope_identifiers – for PL/Scope identifiers
- plscope_statements – for SQL statements
- plscope_tab_usage – for table usages
- plscope_col_usage – for column usages
- plscope_ins_lineage – for where-lineage of insert statements
For each of these views you find an example query and result in the readme of plscope-utils.
Nonetheless I’m going to show some query results in this post as well.
PLSCOPE_IDENTIFIERS
The view plscope_identifiers combines dba_identifiers and dba_statements and provides all columns from dba_identifiers plus procedure_name, name_path, path_len, ref_owner, ref_object_type and ref_object_name.
Here’s an example query for the procedure defined above.
|
1 2 3 4 5 6 |
SELECT procedure_name, line, col, name, name_path, path_len, type, usage, ref_owner, ref_object_type, ref_object_name FROM plscope_identifiers WHERE object_name = 'LOAD_FROM_TAB' AND owner = USER ORDER BY line, col; |
PLSCOPE_STATEMENTS
This view is very similar to dba_statements. It just adds a is_duplicate column to easily identify duplicate SQL statements.
|
1 2 3 4 5 |
SELECT owner, object_type, object_name, line, col, type, sql_id, is_duplicate, full_text FROM plscope_statements S WHERE owner = USER AND is_duplicate = 'YES' ORDER BY sql_id, owner, object_type, object_name, line, col; |
PLSCOPE_TAB_USAGE
This view reports synonym, view and table usages. The column direct_dependency is used to indicate whether the usage has been resolved by an synonym or view.
|
1 2 3 4 5 |
SELECT * FROM plscope_tab_usage WHERE object_name = 'LOAD_FROM_TAB' AND owner = USER ORDER BY owner, object_type, object_name, line, col, direct_dependency; |
PLSCOPE_COL_USAGE
This view reports view/table column usages. Column-less accesses (e.g. insert statements without column-list or *) and accesses on synonyms and views are resolved. The value ‘NO’ in the column direct_dependency indicates such an access.
|
1 2 3 4 5 |
SELECT * FROM plscope_col_usage WHERE object_name = 'LOAD_FROM_TAB' AND owner = USER ORDER BY owner, object_type, object_name, line, col, direct_dependency; |
PLSCOPE_INS_LINEAGE
This view reports the where-lineage of “INSERT … SELECT” statements. I’ll have a talk named “SQL Lineage Made Easy with PL/Scope 12.2?” at the Trivadis TechEvent tomorrow and at APEX Connect 17 on May, 11 2017, where I’ll cover this topic in more detail. I’ll write a dedicated blog post about this topic soon.
Here’s a query which shows that the target column salary is based on the source columns sal and comm in the table emp.
|
1 2 3 4 5 6 |
SELECT line, col, from_owner, from_object_type, from_object_name, from_column_name, to_owner, to_object_type, to_object_name, to_column_name FROM plscope_ins_lineage WHERE object_name = 'LOAD_FROM_TAB' ORDER BY to_column_name; |
Summary
Dependency analysis within the Oracle Database 12.2 has become much easier, thanks to PL/Scope. But using the views provided by plscope-utils makes it almost a trivial thing.
3 Comments
[…] this post I showed how to do some code analysis with PL/Scope and how the views and packages of the […]
Hallo Philipp,
that’s a very nice utility that you’ve built. I’ve give it a try yesterday and it works very fine.
I though have a question about it. In your PLSCOPE_TAB_USAGE-example the tables DEPT and EMP are reported as beeing INSERTED, which is not the case (they are only used in the select part of the insert as select statement).
So, in my opinion, the operation type in that case should be SELECT instead of INSERT. Is this a desired behaviour? If so, is there a way to only report the tables on which an INSERT-Operation is really performed?
Thank you,
Bogdan Bachis
Hi Bogdan,
I guess you are referring to View PLSCOPE_TAB_USAGE. In this example the tables are reported as being part of an INSERT operation. This does not necessarily mean that these tables are used as targets of an INSERT statement. Unfortunately you cannot answer this question with PL/Scope. For that you need a parser. See also “6. Missing Usages and Structures in Static SQL Statements” in Limitations of PL/Scope and How to Deal with Them. I use our own PL/SQL parser to answer these kind of questions. I hope this answers your question.
Regards,
Philipp