Forum Stats

  • 3,836,948 Users
  • 2,262,207 Discussions
  • 7,900,151 Comments

Discussions

PLSQL Variables

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');

Comments