What SQL is used by the PL/SQL "Desc" command? (for variables/constants in a package)
I have a package called "test_1" generated using the PL/SQL:
create or replace package test_1 as
v_Var1 varchar2(255) := lpad('@', 255, '@');
v_Var2 varchar2(10);
c_Const1 constant pls_integer := 1;
c_Const2 constant varchar2(1) := 'T';
end;
/
When I type "DESC test_1" into PL/SQL I get:
SQL> desc test_1
Element Type
-------- --------
V_VAR1 VARIABLE
V_VAR2 VARIABLE
C_CONST1 CONSTANT
C_CONST2 CONSTANT
SQL>
I am writing some SQL that is supposed to extract variable and constant package information, the DESC command does exactly what I want ... How do I do this in SQL? Ideally the output would be something like:
Package Element Type
---------- -------- --------
TEST_1 V_VAR1 VARIABLE
TEST_1 V_VAR2 VARIABLE
TEST_1 C_CONST1 CONSTANT
TEST_1 C_CONST2 CONSTANT
I'm sure the information is in the USER_* views, but which one is a mystery to me ...
It is the description of the VARIABLES and CONSTANTS I'm after (not the functions/ procedures ... the user_arguments/ user_procedures views are available for that).
Basically I'm developing a solution that will get all the objects in the database, compare them to all the other objects in the database and find out exactly who uses what (and there can be bits of dynamic SQL in here which is why I can't rely on the system views).
I'm getting a bit desperate for a solution to this ... ;-)
