Can I execute a procedure with default null parameters?

Question

I recently created a procedure that is defined like this:

create or replace
PACKAGE
pkg_dml_legal_transactions
AS
  PROCEDURE spm_update_court_cost(
    p_court_state     IN legal_court_cost.state%TYPE,
    p_tran_code       IN legal_court_cost.transaction_code%TYPE,
    p_legal_court     IN legal_court_cost.court%TYPE default null,
    p_end_date        IN legal_court_cost.end_date%TYPE,
    p_cost_min        IN legal_court_cost.cost_range_min%TYPE,
    p_cost_max        IN legal_court_cost.cost_range_max%TYPE,
    p_bal_min         IN legal_court_cost.bal_range_min%TYPE DEFAULT NULL,
    p_bal_max         IN legal_court_cost.bal_range_max%TYPE DEFAULT NULL);

end pkg_dml_legal_transactions;

When I attempt to execute the procedure, I get an error stating that:

PLS-00306: wrong number or types of arguments in call to 'SPM_UPDATE_COURT_COST'

Here is what my execute statement looks like:

execute pkg_dml_legal_transactions.spm_update_court_cost('NJ',1,sysdate,1000,40000);

Now I understand what the error means, but I figured if the parameters are defaulted to null then I could just skip them over, but apparently not. Is there a way around this?

Try making `p_cost_max` to `legal_court_cost.cost_range_max%TYPE DEFAULT NULL` Otherwise you have to pass a parameter to procedure because it expects a value as you have not defined `DEFAULT NULL` — Jacob, Aug 23 '13 at 15:52
If you are not expecting it to be null, then you must pass a parameter to procedure. — Jacob, Aug 23 '13 at 15:55
The only parameters that are allowed to be null are the court, and balance min and max. In my execute I have all the ones that are not allowed to be null. — user2405778, Aug 23 '13 at 15:56
See Justin's answer as he has explained it well. `p_cost_max` cannot be null. — Jacob, Aug 23 '13 at 15:57

score 43 · Accepted Answer · answered Aug 23 '13 at 15:54

In PL/SQL, you can call a procedure using either named parameter notation or positional notation. If you want to skip some parameters, you'll need to use named parameter notation

execute pkg_dml_legal_transactions.spm_update_court_cost( p_court_state => 'NJ',
                                                          p_tran_code => 1,
                                                          p_end_date => sysdate,
                                                          p_cost_min => 1000,  
                                                          p_cost_max => 40000 );

Generally, when you're designing a procedure, you would put all the optional parameters at the end so that the caller could also use positional notation.

Ahhh, yes that rings a bell. I remember reading about this awhile ago but it must have slipped my mind. Thank you very much! — user2405778, Aug 23 '13 at 15:57

score 8 · Answer 2 · answered Aug 25 '13 at 19:18

You can use mixed approach, positional notation until first omitted parameter, named notation for the rest.

declare
  procedure do_something(p_foo IN NUMBER
                        ,p_bar IN VARCHAR2 DEFAULT NULL
                        ,p_baz IN VARCHAR2) IS
  begin
    null;
  end;
begin
  do_something(12, p_baz => 'abc');
end;

But I would choose what Justin proposed.

Can I execute a procedure with default null parameters?

2 Answers2