Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Procedures in PL/SQL can have default values for parameters, but the caller can pass values that are identical to the default values. Is there a way from within the PL/SQL to determine whether a parameter is passed? As implied this cannot be done by comparing the value with the default value.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

It might be possible if you consider overloading a procedure. Suppose you want the default of 5 for parameter a and 7 for parameter b and want also to check if the parameters were passed. In this case you have to overload the procedure 4 times for each combination of (with and without parameter). Finally, the real thing happens in the private procedure p_:

set serveroutput on size 100000 format wrapped

create or replace package tq84_test_pck as

   procedure p;
   procedure p(a number          );
   procedure p(          b number);
   procedure p(a number, b number);

end tq84_test_pck;
/

create or replace package body tq84_test_pck as

    procedure p_(a number := 5, a_default boolean,
                 b number := 7, b_default boolean) as
    begin

        dbms_output.put_line(
             'a: ' || a || 
             case when a_default then ' [default]'
                                 else ' [passed ]' end);

        dbms_output.put_line(
             'b: ' || b || 
             case when b_default then ' [default]'
                                 else ' [passed ]' end);

        dbms_output.new_line;

    end p_;

    procedure p is
    begin
        p_(a_default => true, 
           b_default => true);
    end p;

    procedure p (a number) is
    begin
        p_(a         => a, 
           a_default => false, 
           b_default => true);
    end p;

    procedure p (b number) is
    begin
        p_(b         => b, 
           a_default => true, 
           b_default => false);
    end p;

    procedure p (a number, b number) is
    begin
        p_(a         => a,
           b         => b, 
           a_default => false, 
           b_default => false);
    end p;

end tq84_test_pck;
/

show errors

declare

begin

   tq84_test_pck.p;
   tq84_test_pck.p( b => 7);
   tq84_test_pck.p( a => 7 );
   tq84_test_pck.p( a => null);
   tq84_test_pck.p( a => 5, b => 6);

end;
/
share|improve this answer
    
a) I didn't think we wanted to redo the existing code outside of the signature, on account of maintainability, and b) ORA lets you do overloaded procedures? DO WANT! ... thanks! –  jcolebrand Jan 19 '11 at 21:23
    
I had thought of an overloaded procedure, but not one for every combination of parameters possible. That does indeed satisfy the question even though I was hoping for something much simpler. –  Leigh Riffel Jan 19 '11 at 21:49
    
@jcolebrand Oracle only allows overloading of a procedure when its wrapped by a package. You cannot do it for standalone procedures. –  ScottCher Jan 20 '11 at 13:47
    
@ScottCher ~ Thanks for that. –  jcolebrand Jan 20 '11 at 15:03

So the way I do this is I don't use default values. Or rather, I set them to NULL as default. Then within my procedure body I check to see if it's equal to NULL and if so set it to a sane default. At that point you could mangle a flag or two in the same space to see what's being set or not set.

However, there's the edge case of if they intended to pass in NULL for some reason. However, most devs don't deal well with tripletcase, so I don't usually worry about that (additionally, if I'm passing in a value, I need to pass a value, not null, so null for me means never-was-defined)

share|improve this answer
    
+1 Because I think this is as close as can be done today, which is to say it can't be done when you need to know if NULL was passed. –  Leigh Riffel Jan 19 '11 at 18:31
    
What is tripletcase? –  Leigh Riffel Jan 19 '11 at 18:37
    
@leigh idk, something I call it. tripletcase -> TRUE | FALSE | NULL ... I'm sure there's a real name for it –  jcolebrand Jan 19 '11 at 19:02
2  
@jcolebrand: ternary logic? –  René Nyffenegger Jan 19 '11 at 20:37
    
@Rene I associate ternary with ?: so :/ –  jcolebrand Jan 19 '11 at 20:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.