Skip to Main Content
  • Questions
  • Where can I find out the default values of parameters of functions or procedures in system tables?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Bob.

Asked: January 09, 2024 - 9:17 am UTC

Last updated: January 09, 2024 - 3:50 pm UTC

Version: 19

Viewed 1000+ times

You Asked

Where can I find out the default values of parameters of functions or procedures in system tables? :

PROCEDURE proc (param1 IN NUMBER DEFAULT 888)

and Chris said...

Unfortunately the DEFAULT_VALUE column of *_ARGUMENTS is Reserved for future use.

You can find the information in a round-about way by using *_IDENTIFIERS to find parameters with defaults. Then joining this to the corresponding line in *_SOURCE to get the text:

create or replace procedure p ( 
  p0 number,
  p1 number default 1,
  p2 number default 2
) as
begin
  null;
end p;
/

select ui.object_name, us.line, us.text
from   user_identifiers ui
join   user_source us
on     us.name = ui.object_name
and    us.line = ui.line
where  ui.object_name = 'P'
and    ui.usage = 'ASSIGNMENT'
and    ui.type = 'FORMAL IN'
order  by us.line;

OBJECT_NAME       LINE  TEXT                       
P                    3   p1 number default 1,
P                    4   p2 number default 2


Though this only works if the default is declared on the same line as the parameter name!

Change the procedure definition to wrap the default to the next line and you'll get:

create or replace procedure p ( 
  p0 number,
  p1 number default 1,
  p2 number 
    default 2
) as
begin
  null;
end p;
/

OBJECT_NAME       LINE  TEXT                       
P                    3   p1 number default 1,
P                    4   p2 number 


You just scan *_SOURCE for the text %DEFAULT% too. This could lead to lots of false positives though as the DEFAULT keyword is present in some SQL statements, it may appear in comments or your object names, etc.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library