Default Values to Stored Procedure in Oracle
Default values are only used if the arguments are not specified. In your case you did specify the arguments - both were supplied, with a value of NULL. (Yes, in this case NULL is considered a real value :-). Try:
EXEC TEST()
Share and enjoy.
Addendum: The default values for procedure parameters are certainly buried in a system table somewhere (see the SYS.ALL_ARGUMENTS
view), but getting the default value out of the view involves extracting text from a LONG field, and is probably going to prove to be more painful than it's worth. The easy way is to add some code to the procedure:
CREATE OR REPLACE PROCEDURE TEST(X IN VARCHAR2 DEFAULT 'P',
Y IN NUMBER DEFAULT 1)
AS
varX VARCHAR2(32767) := NVL(X, 'P');
varY NUMBER := NVL(Y, 1);
BEGIN
DBMS_OUTPUT.PUT_LINE('X=' || varX || ' -- ' || 'Y=' || varY);
END TEST;
Default-Values are only considered for parameters NOT given to the function.
So given a function
procedure foo( bar1 IN number DEFAULT 3,
bar2 IN number DEFAULT 5,
bar3 IN number DEFAULT 8 );
if you call this procedure with no arguments then it will behave as if called with
foo( bar1 => 3,
bar2 => 5,
bar3 => 8 );
but 'NULL' is still a parameter.
foo( 4,
bar3 => NULL );
This will then act like
foo( bar1 => 4,
bar2 => 5,
bar3 => Null );
( oracle allows you to either give the parameter in order they are specified in the procedure, specified by name, or first in order and then by name )
one way to treat NULL the same as a default value would be to default the value to NULL
procedure foo( bar1 IN number DEFAULT NULL,
bar2 IN number DEFAULT NULL,
bar3 IN number DEFAULT NULL );
and using a variable with the desired value then
procedure foo( bar1 IN number DEFAULT NULL,
bar2 IN number DEFAULT NULL,
bar3 IN number DEFAULT NULL )
AS
v_bar1 number := NVL( bar1, 3);
v_bar2 number := NVL( bar2, 5);
v_bar3 number := NVL( bar3, 8);