oracle-databasedatestored-proceduresdefault-valueinput-parameters

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


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.


Solution

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