Find global package variables from a data dictionary

Question

I have a package:

CREATE PACKAGE my_pkg IS g_var NUMBER; END; / 

Is it possible to query SYS views in some way to find that this package has this global variable? I'm interested in the explicit variable name and data type.

PS Parsing user_source is not taken into account.

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

Answer 1

For my reference, here is a query that gets this information from a 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 

Answer 2

There is no sys representation that contains this information. The best you can possibly do here is to use user_source or all_source (I know you said you didn’t take it into account, but I think this is the best you can do here).

With that said, if you use the standard g_-prefix for your global variables, does all_source play out really so badly? I think you could write PL / SQL to loop around the source and find "g_". This will probably require some tweaking and experimentation, but I think it's worth a try.

Answer 3

You can open it using the 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;