It its known that if not all components are compiled with PL/Scope, the analysis might be incomplete. However, in certain cases the hierarchical data structure of dba_identifiers is inconsistent. The column usage_context_id can hold values which do not exist in the column usage_id for a chosen object. If your analysis relies on the hierarchical structure such as the view plscope_identifiers then the result will not contain all available data.
To reproduce do the following:
1. Enable PL/Scope
ALTER SESSION SET plscope_settings='identifiers:all, statements:all';
2. Create function f
CREATE OR REPLACE FUNCTION f(in_string IN VARCHAR2) RETURN NUMBER IS
l_result NUMBER;
BEGIN
l_result := dbms_random.value(
0,
dbms_utility.get_hash_value(
in_string,
0,
2048
)
);
RETURN l_result;
END f;
/
3. Query dba_identifiers
SELECT name, type, usage, line, col, usage_id, usage_context_id
FROM dba_identifiers
WHERE owner = USER
AND object_type = 'FUNCTION'
AND object_name = 'F'
ORDER by line, col;
NAME TYPE USAGE LINE COL USAGE_ID USAGE_CONTEXT_ID
---------- ------------------ ----------- ---------- ---------- ---------- ----------------
F FUNCTION DEFINITION 1 10 2 1
F FUNCTION DECLARATION 1 10 1 0
IN_STRING FORMAL IN DECLARATION 1 12 3 2
VARCHAR2 CHARACTER DATATYPE REFERENCE 1 25 4 3
NUMBER NUMBER DATATYPE REFERENCE 1 42 5 2
L_RESULT VARIABLE DECLARATION 2 4 6 2
NUMBER NUMBER DATATYPE REFERENCE 2 13 7 6
L_RESULT VARIABLE ASSIGNMENT 4 4 8 2
IN_STRING FORMAL IN REFERENCE 7 23 11 10
L_RESULT VARIABLE REFERENCE 12 12 12 2
10 rows selected.
The in_string reference with usage_id 11 has a usage_context_id 10. But there is no usage_id with the value 10. That's the inconsistency.
4. Query plscope_identifiers (v0.2.0)
SELECT name, type, usage, line, col, usage_id, usage_context_id
FROM plscope_identifiers
WHERE owner = USER
AND object_type = 'FUNCTION'
AND object_name = 'F'
ORDER by line, col;
NAME TYPE USAGE LINE COL USAGE_ID USAGE_CONTEXT_ID
---------- ------------------ ----------- ---------- ---------- ---------- ----------------
F FUNCTION DECLARATION 1 10 1 0
F FUNCTION DEFINITION 1 10 2 1
IN_STRING FORMAL IN DECLARATION 1 12 3 2
VARCHAR2 CHARACTER DATATYPE REFERENCE 1 25 4 3
NUMBER NUMBER DATATYPE REFERENCE 1 42 5 2
L_RESULT VARIABLE DECLARATION 2 4 6 2
NUMBER NUMBER DATATYPE REFERENCE 2 13 7 6
L_RESULT VARIABLE ASSIGNMENT 4 4 8 2
L_RESULT VARIABLE REFERENCE 12 12 12 2
9 rows selected.
The result contains only 9 rows. The in_string reference with usage_id 11 is missing. That's the wrong result.
To fix the issue either an Oracle Support Request has to be opened (chances are that this will be accepted as a bug) or the usage_context_id needs to be fixed on the fly within the plscope_identifiers view. I consider doing both.
It its known that if not all components are compiled with PL/Scope, the analysis might be incomplete. However, in certain cases the hierarchical data structure of
dba_identifiersis inconsistent. The columnusage_context_idcan hold values which do not exist in the columnusage_idfor a chosen object. If your analysis relies on the hierarchical structure such as the viewplscope_identifiersthen the result will not contain all available data.To reproduce do the following:
1. Enable PL/Scope
2. Create function
f3. Query
dba_identifiersThe
in_stringreference with usage_id11has a usage_context_id10. But there is nousage_idwith the value10. That's the inconsistency.4. Query
plscope_identifiers(v0.2.0)The result contains only 9 rows. The
in_stringreference with usage_id11is missing. That's the wrong result.To fix the issue either an Oracle Support Request has to be opened (chances are that this will be accepted as a bug) or the
usage_context_idneeds to be fixed on the fly within theplscope_identifiersview. I consider doing both.