Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
I haven't missed the declaration of proc_2 in the package definition, the procedure is local to the package and not public. –  René Nyffenegger Jan 10 '13 at 13:56
    
@RenéNyffenegger Updated answer with more detail –  Phil Jan 11 '13 at 15:20

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>.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.