Find package global variables from data dictionary

问题:

I have a package:

CREATE PACKAGE my_pkg
IS
g_var NUMBER;
END;
/

Is there any way I can query SYS views, to find that this package has this global variable? I'm interested in explicit variable name and data type.

P.S. Parsing user_source does not count.

Edit: I would like to do that without introducing getter/setter functions.

回答1:

There isn't a sys view that contains this information. The best you can probably do here is use user_source or all_source (I know you said that doesn't count, but I think it's the best you can do here).

With that said, if you use the standard g_ prefix for your globals, is parsing all_source really that bad? I would think you could write a PL/SQL to loop over the source and search for "g_". It'll probably take some tweaking and experimentation, but I think it's worth trying.

回答2:

You can expose it with a function in the package:

FUNCTION FN_get_g_var  RETURN number
IS BEGIN   
     return g_var; END
FN_get_g_var ;

And in the view

CREATE VIEW myView AS 
SELECT my_pkg.FN_get_g_var() FROM DUAL;

回答3:

For my own reference, here is a query that gets that information from data dictionary -

select name as variable_name, object_name as package_name, object_type
from dba_identifiers a
 where usage_context_id = 1
   and usage = 'DECLARATION'
   and type = 'VARIABLE'
start with object_type in ('PACKAGE', 'PACKAGE BODY')
connect by prior usage_id = usage_context_id
    and object_name = prior object_name
    and object_type = prior object_type