ORA-01858 while executing created procedure

Question

I have created a procedure to find a date between today and expiry_date. The table column format is already in date format.

While creating procedure it is created successfully without errors but during execution of procedure as below it is showing

ORA-01858: a non-numeric character was found where a numeric was expected

ALTER SESSION SET NLS_DATE_FORMAT ='dd-mm-yyyy';
CREATE OR REPLACE PROCEDURE flow (
    today  IN                  DATE,
    expiry_date       IN       DATE
) AS

BEGIN
    FOR  rec in (
        SELECT *
        FROM flow4
        WHERE englishcalendar BETWEEN 'englishcalendar.today' 
                    AND 'englishcalendar.expiry_date')
    LOOP
        dbms_output.put_line(rec.englishcalendar);
    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(sqlerrm);
END;
/

EXEC FLOW('01-02-2017','03-04-2018');
/

I had also tried adding to_date in both procedure and procedure execution but i got same error I also tried with this reference too Getting Error - ORA-01858: a non-numeric character was found where a numeric was expected

NOTE englishcalendar contains continuous day of 2019 in 'dd-mm-yyyy' date format

Answer 1

'englishcalendar.today' is a string not a date. Likewise 'englishcalendar.expiry_date'. Oracle is attempting to convert these strings to dates and failing, because they are not dates.

Simple solution: reference the parameters as identifiers not strings:

 ... 
  FOR  rec in (
    SELECT *
    FROM flow4
    WHERE englishcalendar BETWEEN today
                AND expiry_date)
...