Is there a bug with PL/Scope in combination with associative arrays?

Question

I believe I stumbled upon a bug with PL/Scope in combination with associative arrays, but perhaps I overlook something.

I have the following package

create or replace package tq84_pkg_c as

    procedure proc_1;

end tq84_pkg_c;
/

with its body

create or replace package body tq84_pkg_c as

    type num_t is table of number index by varchar2(10);

    procedure proc_2 is begin
        null;
    end proc_2;

    procedure proc_1 is 
        v_num num_t; 
    begin
        if v_num.exists(1) then
           proc_2;
        end if;
    end proc_1;

end tq84_pkg_c;
/

I compile the the package "with" PL/Scope:

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';

ALTER PACKAGE TQ84_PKG_C COMPILE;

Then, I query all_identifiers:

select
  name          identifier,
  usage,
  type,
  usage_id,
  usage_context_id,
  line,
  col
from
  all_identifiers
where
  object_name in ('TQ84_PKG_C')
order by
  object_name,
  object_type,
  line,
  col;

With the following result:

IDENTIFIER                     USAGE       TYPE                 USAGE_ID USAGE_CONTEXT_ID       LINE        COL
------------------------------ ----------- ------------------ ---------- ---------------- ---------- ----------
TQ84_PKG_C                     DECLARATION PACKAGE                     1                0          1          9
PROC_1                         DECLARATION PROCEDURE                   2                1          3         15
TQ84_PKG_C                     DEFINITION  PACKAGE                     1                0          1         14
NUM_T                          DECLARATION ASSOCIATIVE ARRAY           2                1          3         10
NUMBER                         REFERENCE   NUMBER DATATYPE             3                2          3         28
VARCHAR2                       REFERENCE   CHARACTER DATATYPE          4                3          3         44
PROC_2                         DEFINITION  PROCEDURE                   6                5          5         15
PROC_2                         DECLARATION PROCEDURE                   5                1          5         15
PROC_1                         DEFINITION  PROCEDURE                   7                1          9         15
V_NUM                          DECLARATION VARIABLE                    8                7         10          9
NUM_T                          REFERENCE   ASSOCIATIVE ARRAY           9                8         10         15
V_NUM                          REFERENCE   VARIABLE                   11               10         12         12
PROC_2                         CALL        PROCEDURE                  12               10         13         12

Now, the problem is with the last two records: Their usage_context_id is 10 and this number is supposed (as I believe) to reference usage_id, yet, there is no record with usage_id=10.

So, is this a bug? Or is there a way to include the missing record into the result set?

I believe the problem is caused by the line if v_num.exists(1) then. If I take this if (and the corresponding end if) out, the problem disappears, and usage_conext_id for PROC_2 references an existing usage_id.

Answer 1

It is a bug, because the documentation states:

USAGE_CONTEXT_ID is a reflexive foreign key to USAGE_ID

UPDATE: A bit of troubleshooting...

all_identifiers.usage_id comes from sys.plscope_action$.action#

Given:

SQL> /

IDENTIFIER   USAGE       TYPE                 USAGE_ID USAGE_CONTEXT_ID LINE COL
------------ ----------- ------------------ ---------- ---------------- ---- ---
TQ84_PKG_C   DECLARATION PACKAGE                     1                0    1   9
PROC_1       DECLARATION PROCEDURE                   2                1    2  15
TQ84_PKG_C   DEFINITION  PACKAGE                     1                0    1  14
NUM_T        DECLARATION ASSOCIATIVE ARRAY           2                1    3  10
NUMBER       REFERENCE   NUMBER DATATYPE             3                2    3  28
VARCHAR2     REFERENCE   CHARACTER DATATYPE          4                3    3  44
PROC_2       DECLARATION PROCEDURE                   5                1    5  15
PROC_2       DEFINITION  PROCEDURE                   6                5    5  15
PROC_1       DEFINITION  PROCEDURE                   7                1    9  15
V_NUM        DECLARATION VARIABLE                    8                7   10   9
NUM_T        REFERENCE   ASSOCIATIVE ARRAY           9                8   10  15
V_NUM        REFERENCE   VARIABLE                   11               10   12  12
PROC_2       CALL        PROCEDURE                  12               10   13  12

13 rows selected.

SQL>

... we're looking for the elusive sys.plscope_action$.action# = 10 for this object:

SQL> select a.obj#, a.action#, a.signature, a.action, a.line, a.col, a.context#, o.name, o.type#
from sys.plscope_action$ a, sys.obj$ o
where a.obj# = o.obj#
and name = 'TQ84_PKG_C'
and action# = 10;

      OBJ#    ACTION# SIGNATURE                            ACTION LINE COL   CONTEXT# NAME                                TYPE#
---------- ---------- -------------------------------- ---------- ---- --- ---------- ------------------------------ ----------
     83504         10 0C679E5FFBC26144A7F875495F68C2E1          3   12  18          7 TQ84_PKG_C                             11

SQL>

That's the missing row.

The ALL_IDENTIFIERS view joins sys.plscope_identifier$ with sys.plscope_action$ on the signature column - there's no matching row in sys.plscope_identifier$, which I suspect is causing the problem.

Answer 2

It looks like this is a bug with the handling of functions on collection variables. Given the following function:

create or replace procedure collection_functions as
  type nested_tab is table of number;
  var  nested_tab;
  num  pls_integer;
  boo  boolean;
begin
  var.extend(1);
  var.trim(1);
  var.delete;
  boo := var.exists(1);
  num := var.count;
end;
/

I get the following output from all_identifiers on 11.2.0.1 and 11.2.0.3:

IDENTIFIER                     USAGE       TYPE                 USAGE_ID USAGE_CONTEXT_ID       LINE        COL
------------------------------ ----------- ------------------ ---------- ---------------- ---------- ----------
COLLECTION_FUNCTIONS           DEFINITION  PROCEDURE                   2                1          1         11 
COLLECTION_FUNCTIONS           DECLARATION PROCEDURE                   1                0          1         11 
NESTED_TAB                     DECLARATION NESTED TABLE                3                2          2          8 
NUMBER                         REFERENCE   NUMBER DATATYPE             4                3          2         31 
VAR                            DECLARATION VARIABLE                    5                2          3          3 
NESTED_TAB                     REFERENCE   NESTED TABLE                6                5          3          8 
NUM                            DECLARATION VARIABLE                    7                2          4          3 
PLS_INTEGER                    REFERENCE   SUBTYPE                     8                7          4          8 
BOO                            DECLARATION VARIABLE                    9                2          5          3 
BOOLEAN                        REFERENCE   BOOLEAN DATATYPE           10                9          5          8 
VAR                            REFERENCE   VARIABLE                   12               11          7          3 
VAR                            REFERENCE   VARIABLE                   14               13          8          3 
VAR                            REFERENCE   VARIABLE                   16               15          9          3 
BOO                            ASSIGNMENT  VARIABLE                   17                2         10          3 
VAR                            REFERENCE   VARIABLE                   19               18         10         10 
NUM                            ASSIGNMENT  VARIABLE                   20                2         11          3 
VAR                            REFERENCE   VARIABLE                   22               21         11         10

Which is missing USAGE_IDs 11, 13, 15, 18 and 21. These appear in the USAGE_CONTEXT_ID for the instances of VAR.<fn>.