Support missing secondary objects in PL/Scope tree

[PhilippSalvisberg]

In the Oracle Database Development Guide are more root identifier types mentioned which are not part of the PL/Scope tree:

• Library
• Materialized View
• Operator
• Sequence
• Synonym

Add these secondary objects to the PL/Scope tree.

Additionally, check relevance of

• Alias
• Object
• Opaque

Object and opaque represent data types, but I've never seen alias.

[PhilippSalvisberg]

synonym is already covered

[PhilippSalvisberg]

The following script shows that sequence is a secondary object. A declaration is created when it is referenced. Unreferenced sequences survive.

alter session set plscope_settings = 'IDENTIFIERS:ALL';
column object_type format a10;
column object_name format a10;
column name format a10;

create sequence test_seq;

select object_type, object_name, name, type, usage
  from user_identifiers
 where object_type in ('PROCEDURE', 'SEQUENCE');

create or replace procedure p is
   l integer;
begin
   select test_seq.nextval into l from dual;
end;
/

select object_type, object_name, name, type, usage
  from user_identifiers
 where object_type in ('PROCEDURE', 'SEQUENCE');

drop procedure p;

select object_type, object_name, name, type, usage
  from user_identifiers
 where object_type in ('PROCEDURE', 'SEQUENCE');

drop sequence test_seq;

select object_type, object_name, name, type, usage
  from user_identifiers
 where object_type in ('PROCEDURE', 'SEQUENCE');

Here's the result:

SQL> alter session set plscope_settings = 'IDENTIFIERS:ALL';

Session altered.

SQL> column object_type format a10;
SQL> column object_name format a10;
SQL> column name format a10;
SQL> 
SQL> create sequence test_seq;

Sequence TEST_SEQ created.

SQL> 
SQL> select object_type, object_name, name, type, usage
  2    from user_identifiers
  3*  where object_type in ('PROCEDURE', 'SEQUENCE');
no rows selected
SQL> 
SQL> create or replace procedure p is
  2     l integer;
  3  begin
  4     select test_seq.nextval into l from dual;
  5  end;
  6* /

Procedure P compiled

SQL> 
SQL> select object_type, object_name, name, type, usage
  2    from user_identifiers
  3*  where object_type in ('PROCEDURE', 'SEQUENCE');

OBJECT_TYP OBJECT_NAM NAME       TYPE               USAGE      
---------- ---------- ---------- ------------------ -----------
PROCEDURE  P          INTEGER    SUBTYPE            REFERENCE  
PROCEDURE  P          P          PROCEDURE          DEFINITION 
PROCEDURE  P          P          PROCEDURE          DECLARATION
PROCEDURE  P          L          VARIABLE           ASSIGNMENT 
PROCEDURE  P          L          VARIABLE           DECLARATION
PROCEDURE  P          TEST_SEQ   SEQUENCE           REFERENCE  
SEQUENCE   TEST_SEQ   TEST_SEQ   SEQUENCE           DECLARATION

7 rows selected. 

SQL> 
SQL> drop procedure p;

Procedure P dropped.

SQL> 
SQL> select object_type, object_name, name, type, usage
  2    from user_identifiers
  3*  where object_type in ('PROCEDURE', 'SEQUENCE');

OBJECT_TYP OBJECT_NAM NAME       TYPE               USAGE      
---------- ---------- ---------- ------------------ -----------
SEQUENCE   TEST_SEQ   TEST_SEQ   SEQUENCE           DECLARATION

SQL> 
SQL> drop sequence test_seq;

Sequence TEST_SEQ dropped.

SQL> 
SQL> select object_type, object_name, name, type, usage
  2    from user_identifiers
  3*  where object_type in ('PROCEDURE', 'SEQUENCE');
no rows selected

[PhilippSalvisberg]

Alias, Object and Opaque are irrelevant.

Other secondary objects make sense especially for the Used by editor/viewer.

[PhilippSalvisberg]

LIBARY is missing as SQLDev node. OPERATOR exists, but the usages are not reported in USER_IDENTIFIERS. Usages of a MATERIALIZED VIEW are reported as TABLE usages in 19c.

IMO it make sense to add SEQUENCE only.