Where can I find out the default values of parameters of functions or procedures in system tables? :
PROCEDURE proc (param1 IN NUMBER DEFAULT 888)
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.