plscope_tab_usage ignores object tables

[rvo-cs]

Example:

Using utPLSQL, release 3.1.12, installed in the UT3 schema.

exec plscope_context.set_attr('OWNER', 'UT3');
exec plscope_context.set_attr('OBJECT_TYPE', 'PACKAGE BODY');
exec plscope_context.set_attr('OBJECT_NAME', 'UT_SUITE_CACHE_MANAGER');

select line,
       col,
       operation,
       ref_owner,
       ref_object_type,
       ref_object_name,
       direct_dependency,
       text
  from plscope.plscope_tab_usage
 where procedure_name = 'SAVE_OBJECT_CACHE'
 order by line,
       col,
       ref_owner,
       ref_object_type,
       ref_object_name;

Results:

LINE    COL    OPERATION    REF_OWNER    REF_OBJECT_TYPE    REF_OBJECT_NAME           DIRECT_DEPENDENCY    TEXT                                                               
    285     19 DELETE       UT3          TABLE              UT_SUITE_CACHE_PACKAGE    YES                        delete from ut_suite_cache_package t                         
    293     14 SELECT       UT3          TABLE              UT_SUITE_CACHE_PACKAGE    YES                          from ut_suite_cache_package t                              
    299     35 UPDATE       UT3          TABLE              UT_SUITE_CACHE_SCHEMA     YES                          update /*+ no_parallel */ ut_suite_cache_schema t          
    304     42 INSERT       UT3          TABLE              UT_SUITE_CACHE_SCHEMA     YES                            insert /*+ no_parallel */ into ut_suite_cache_schema     
    309     36 UPDATE       UT3          TABLE              UT_SUITE_CACHE_PACKAGE    YES                          update  /*+ no_parallel */ ut_suite_cache_package t        
    315     42 INSERT       UT3          TABLE              UT_SUITE_CACHE_PACKAGE    YES                            insert /*+ no_parallel */ into ut_suite_cache_package    

Expected;

LINE    COL    OPERATION    REF_OWNER    REF_OBJECT_TYPE    REF_OBJECT_NAME           DIRECT_DEPENDENCY    TEXT                                                               
    281     19 DELETE       UT3          TABLE              UT_SUITE_CACHE            YES                        delete from ut_suite_cache t                                 
    285     19 DELETE       UT3          TABLE              UT_SUITE_CACHE_PACKAGE    YES                        delete from ut_suite_cache_package t                         
    293     14 SELECT       UT3          TABLE              UT_SUITE_CACHE_PACKAGE    YES                          from ut_suite_cache_package t                              
    299     35 UPDATE       UT3          TABLE              UT_SUITE_CACHE_SCHEMA     YES                          update /*+ no_parallel */ ut_suite_cache_schema t          
    304     42 INSERT       UT3          TABLE              UT_SUITE_CACHE_SCHEMA     YES                            insert /*+ no_parallel */ into ut_suite_cache_schema     
    309     36 UPDATE       UT3          TABLE              UT_SUITE_CACHE_PACKAGE    YES                          update  /*+ no_parallel */ ut_suite_cache_package t        
    315     42 INSERT       UT3          TABLE              UT_SUITE_CACHE_PACKAGE    YES                            insert /*+ no_parallel */ into ut_suite_cache_package    
    320     21 DELETE       UT3          TABLE              UT_SUITE_CACHE            YES                          delete from ut_suite_cache t                               
    324     40 INSERT       UT3          TABLE              UT_SUITE_CACHE            YES                          insert /*+ no_parallel */ into ut_suite_cache t            
    350     40 INSERT       UT3          TABLE              UT_SUITE_CACHE            YES                          insert /*+ no_parallel */ into ut_suite_cache t            

UT_SUITE_CACHE is an object table, and as such, is not listed in DBA_TABLES [^object_tables_in_dict].

For that reason, as of v1.0.0, it is ignored by plscope_tab_usage, which only takes into account tables from DBA_TABLES.

[^object_tables_in_dict]: Object tables are treated specially in the data dictionary: they appear in DBA_OBJECTS with object_type = 'TABLE', but they are not listed in DBA_TABLES. Instead, they are found in the DBA_OBJECT_TABLES and DBA_ALL_TABLES views. Further, their "columns" are listed in DBA_TAB_COLUMNS, yet PL/Scope records references to these columns as "object attribute references", not as "column references" as it would for columns of ordinary tables.

[PhilippSalvisberg]

Thanks for reporting this issue.

It's clearly a bug since

set pagesize 1000
set linesize 1000
column name format a30
column text format a70
select line, col, name, type, text
  from plscope_identifiers
 where procedure_name = 'SAVE_OBJECT_CACHE'
   and type = 'TABLE'
 order by line, col;

produces

      LINE        COL NAME                           TYPE               TEXT                                                                  
---------- ---------- ------------------------------ ------------------ ----------------------------------------------------------------------
       372         19 UT_SUITE_CACHE                 TABLE                    delete from ut_suite_cache t                                    
       376         19 UT_SUITE_CACHE_PACKAGE         TABLE                    delete from ut_suite_cache_package t                            
       384         14 UT_SUITE_CACHE_PACKAGE         TABLE                      from ut_suite_cache_package t                                 
       390         35 UT_SUITE_CACHE_SCHEMA          TABLE                      update /*+ no_parallel */ ut_suite_cache_schema t             
       395         42 UT_SUITE_CACHE_SCHEMA          TABLE                        insert /*+ no_parallel */ into ut_suite_cache_schema        
       400         36 UT_SUITE_CACHE_PACKAGE         TABLE                      update  /*+ no_parallel */ ut_suite_cache_package t           
       406         42 UT_SUITE_CACHE_PACKAGE         TABLE                        insert /*+ no_parallel */ into ut_suite_cache_package       
       411         21 UT_SUITE_CACHE                 TABLE                      delete from ut_suite_cache t                                  
       415         40 UT_SUITE_CACHE                 TABLE                      insert /*+ no_parallel */ into ut_suite_cache t               
       441         40 UT_SUITE_CACHE                 TABLE                      insert /*+ no_parallel */ into ut_suite_cache t               

10 rows selected. 

There are different line numbers, since the version of utPLSQL is most probably different. But it's important, that UT_SUITE_CACHE is part of the result in this view. Hence it should be also part of plscope_tab_usage.