Default Values to Stored Procedure in Oracle

Question

I have a stored procedure as follows.

 CREATE OR REPLACE PROCEDURE TEST(
       X IN VARCHAR2 DEFAULT 'P',
       Y IN NUMBER DEFAULT 1) AS
 BEGIN
 DBMS_OUTPUT.PUT_LINE('X'|| X||'--'||'Y'||Y);
 END;

When I execute the above procedure

 EXEC TEST(NULL,NULL);

It will print X--Y. The input parameters are not defaulting to the specified values in the procedure signature when input parameters are null. What is the use of default values then? What if we pass a null value as input and we want to replace a null value with the default value?

Answer 1

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;