Where are package level variables stored in oracle? Any table?

Question

The variables that we declare at the package level, which can be used within the package, where are these stored when the package is being executed.

Is their any table where we can check?

Checked a few websites including the oracle documentation, but could not find a precise answer to my question. Please advice.

Is this a coincidence, or did you create a separate account to ask almost the same question [here](https://stackoverflow.com/q/56019765/409172)? It's better to edit your question, and respond to others, than to ask the same question multiple times. — Jon Heller, May 10 '19 at 01:37

score 0 · Answer 1 · answered May 07 '19 at 05:48

Yes you can obtain it from *_IDENTIFIERS data dictionary view.

first set the PL/SQL compiler to analyze the identifiers of your program when it is compiled. See PL/Scope

ALTER SESSION SET 
plscope_settings='IDENTIFIERS:ALL'
/

When PL/Scope is enabled and your program unit is compiled, the ALL_IDENTIFIERS view is populated with information about all the identifiers found in that unit.

If I want to see all the declared variables in a program unit, I can execute this query:

SELECT ai.object_name
     , ai.object_type
     , ai.name variable_name
     , ai.name context_name
  FROM all_identifiers ai
 WHERE ai.owner = USER AND 
       ai.TYPE = 'VARIABLE' AND 
       ai.usage = 'DECLARATION'
ORDER BY ai.object_name, 
ai.object_type, ai.usage_id

Where are package level variables stored in oracle? Any table?

1 Answers1