How to set default value of date input parameter in procedure if they are null?

Question

I am having a Procedure , which is accepting fromDate and toDate input parameters I need to set default values as First date of Last month and last date of previous month respectively. I am trying to use below code, but still proper default dates are not being set. Please let me know if below code is valid or i can do something to correct it:

 create or replace PROCEDURE        "TEST" 
        (
         fromdate_in              IN varchar2,
         todate_in                IN varchar2,
         type_in              IN number DEFAULT 01
        )
    is

        V_date              varchar2(3000);

    begin

        select to_date(fromdate_in) into V_date from dual; -- Correct date entered

        Exception WHEN Others THEN
        select to_char(trunc(trunc(sysdate, 'MM') - 1, 'MM'),'DD/MM/RRRR') into V_date from dual; -- if fromdate_in --is null then set V_date to first date of Previous month

        -- calculations using V_date

    end TEST;

Please note , I have shown only how I am setting first date of Previous month to From Date for simplicity.

Answer 1

You could do it much easier like this:

create or replace PROCEDURE        "TEST" 
    (
     fromdate_in              IN DATE,
     todate_in                IN DATE,
     type_in              IN number DEFAULT 1
    )
is

    V_date              DATE;

begin

    V_date := NVL(fromdate_in, TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM'));
    -- calculations using V_date

end TEST;

If you are forced to use VARCHAR2 for fromdate_in then convert the value to DATE:

V_date := NVL(TO_DATE(fromdate_in, 'DD/MM/YYYY'), TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM'));

Answer 2

If you want to set a default value for a parameter, do so as in this example:

SQL> create or replace procedure p_test
  2    (fromdate_in in varchar2
  3       default to_char(trunc(add_months(sysdate, -1), 'mm'), 'dd/mm/rrrr'))
  4  is
  5  begin
  6    dbms_output.put_Line('fromdate_in = ' || fromdate_in);
  7  end;
  8  /

Procedure created.

As you wanted, its default value is the first day of the previous month. Works like this:

SQL> set serveroutput on;
SQL> exec p_test;
fromdate_in = 01/08/2018

PL/SQL procedure successfully completed.

SQL> exec p_test('24/09/2018');
fromdate_in = 24/09/2018

PL/SQL procedure successfully completed.

SQL>

Note that you should really consider switching to DATE datatype parameter instead of VARCHAR2 - you're dealing with dates anyway, so - why bother with invalid values passed to the procedure? If it is a string, what will prevent someone from passing e.g. '99/66/x-FZ' to the procedure? Now you have to worry about it, write exception handler ... everything because you didn't set parameters' datatype to DATE.