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

User · Answer

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

User · Answer

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

[stored-procedures] Default Values to Stored Procedure in Oracle

Examples related to stored-procedures

Examples related to parameters

Examples related to plsql

Examples related to oracle11g

Examples related to default