Can I execute a procedure with default null parameters?

oracle, plsql, procedure

Solution

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.

Problem

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?

Original source