Entity Relationship Model for PL/Scope

Today I found a sketch of an ERD from last year when I looked at the new features of PL/Scope in version 12.2. It looked a bit complicated and also wrong. So, I decided to refactor it using SQL Developer Data Modeler and share the result.

You find the model in the plscope-utils Github project here.

The implementation provides the following views:

  • DBA/ALL/USER_IDENTIFIERSfor the entities “Identifier Declaration” and “DB Object Usage”
  • DBA/ALL/USER_STATEMENTSfor the entities “Identifier Declaration”, “SQL Stmt Usage” and  “SQL Statement”

For me the most interesting entity was “Identifier Declaration”, because it helped me to understand the signature attribute/column. PL/Scope creates a signature for every database object and all its components when a database object is compiled with PL/Scope or when a referenced object cannot be compiled with PL/Scope. I call those objects “Secondary Objects”. Such objects are tables, views, materialized views, operators or sequences. Hence you find PL/Scope metadata for every table which is referenced in the PL/SQL code after compiling it with PL/Scope. Here’s an example:

The PL/Scope metadata for table emp will be removed automatically when there are not used by “Primary Objects” (objects which can be compiled with PL/Scope) anymore. Quite nice.

Leave a Reply

Your email address will not be published. Required fields are marked *