I am trying to get variable_name, variable_type and comment of all variables from any package (it can be for example standard package, ULT_HTTP, etc). I want to get these informations by writting an request from all_source view.
Here is what i have tried :
select regexp_substr(text, '\w(\w|#|\$){0,127}', 1, 1)variable_name,regexp_substr(text, '\w(\w|#|\$){0,127}', 1, 2) variable_type,regexp_replace(regexp_substr(text, '--\s*.+', 1, 1, 'n'), '(^--\s*|$)') commentaire,text from all_source
where type = 'PACKAGE' and regexp_like(text, '^\s*(\w(\w|#|\$){0,127})\s*+EXCEPTION|CONSTANT|INTEGER|ROW|VARCHAR2(;|(\s+:=\s+.+;))', 'n')and type not in ('SUBTYPE', 'PROCEDURE', 'FUNCTION', 'FORMAL IN');