ORA-01861: literal does not match format string.while executing procedure

Question

I have written procedure which have date paramters defined as below:

in_Spendpaidstartdt            IN     DATE,
in_Spendpaidenddt              IN     DATE,

while with in procedure i am calling these paramters as:

AND (   in_Spendpaidstartdt IS NULL
                       OR err.Spendpaiddt >= in_Spendpaidstartdt)
                  AND (   in_Spendpaidenddt IS NULL
                       OR err.Spendpaiddt <= in_Spendpaidenddt));

however oracle is giving following error:

"ORA-01861: literal does not match format string"

Some one please suggest the work around.

What data type is `err.Spendpaiddt` and how exactly are you _calling_ the procedure? — , Oct 04 '16 at 10:01
And how are you passing in the values of your two parameters when you call your procedure? If it's not something like `to_date('01/01/2016', 'dd/mm/yyyy')` or `DATE '2016-01-01'` (i.e. explicitly converting the date-as-a-string into a DATE datatype), then you're doing it wrong... — Boneist, Oct 04 '16 at 10:05
err.Spendpaiddt is defined as varchar2 in table I am just passing as 10/20/2009 — Kunal Sharma, Oct 04 '16 at 10:08

score 0 · Answer 1 · edited Oct 04 '16 at 11:31

Here is dummy:

CREATE OR REPLACE PROCEDURE XYZ (
   in_startdt            IN     DATE,
   in_enddt              IN     DATE,
   output                        OUT SYS_REFCURSOR)
IS
   rcrdnums   VARCHAR2 (32767);
   rcrd_cnt   INT;
BEGIN
   rcrd_cnt := 500;

   SELECT RTRIM (
             XMLCAST (
                XMLAGG (XMLELEMENT (e, RCRDNUM) ORDER BY RCRDNUM) AS CLOB),
             ',')
     INTO rcrdnums
     FROM (SELECT (ERR.RCRDNUM || ',') AS RCRDNUM
             FROM Table_NAME ERR
                     WHERE     ROWNUM <= rcrd_cnt
                 and (   in_startdt IS NULL
                       OR to_date(err.paiddt, 'dd/mm/yyyy') >= to_date(in_startdt, 'dd/mm/yyyy'))
                  AND (   in_enddt IS NULL
                       OR to_date(err.paiddt, 'dd/mm/yyyy') <= to_date(in_enddt, 'dd/mm/yyyy')));

   IF LENGTH (rcrdnums) = 1
   THEN
      rcrdnums := NULL;
   ELSE
      rcrdnums := rcrdnums;
      --SUBSTR (rcrdnums, 1, LENGTH (rcrdnums) - 1);
   END IF;

   DBMS_OUTPUT.PUT_LINE (rcrdnums);

   OPEN outputFOR
      SELECT *
        FROM Table_NAME ERR
             INNER JOIN (    SELECT REGEXP_SUBSTR (rcrdnums,
                                                   '[^,]+',
                                                   1,
                                                   LEVEL)
                                       AS EVENT
                               FROM DUAL
                         CONNECT BY REGEXP_SUBSTR (rcrdnums,
                                                   '[^,]+',
                                                   1,
                                                   LEVEL)
                                       IS NOT NULL) EVENT_P
                ON EVENT_P.EVENT = ERR.RCRDNUM;
END;
/

ORA-01858: a non-numeric character was found where a numeric was expected------now i am facing this error — Kunal Sharma, Oct 04 '16 at 13:37

score 0 · Answer 2 · edited May 23 '17 at 12:33

As already mentioned to @XING, your issues are two-fold.

You are forcing Oracle to do an implicit conversion of a DATE back to a string, when you used to_date on something that's already a DATE - something I've already mentioned elsewhere on stackoverflow!
You are (probably) not passing in the parameters correctly when calling your procedure.

Here is how I'd amend your procedure:

CREATE OR REPLACE PROCEDURE XYZ (
   in_startdt            IN     DATE,
   in_enddt              IN     DATE,
   output                        OUT SYS_REFCURSOR)
IS
   rcrdnums   VARCHAR2 (32767);
   rcrd_cnt   INT;
BEGIN
   rcrd_cnt := 500;

   SELECT RTRIM (
             XMLCAST (
                XMLAGG (XMLELEMENT (e, RCRDNUM) ORDER BY RCRDNUM) AS CLOB),
             ',')
     INTO rcrdnums
     FROM (SELECT (ERR.RCRDNUM || ',') AS RCRDNUM
             FROM Table_NAME ERR
                     WHERE     ROWNUM <= rcrd_cnt
                 and (   in_startdt IS NULL
                       OR to_date(err.paiddt, 'dd/mm/yyyy') >= in_startdt) -- in_startdt is already a DATE, so no need to convert it
                  AND (   in_enddt IS NULL
                       OR to_date(err.paiddt, 'dd/mm/yyyy') <= in_enddt)); -- in_enddt is already a DATE, so no need to convert it

   IF LENGTH (rcrdnums) = 1
   THEN
      rcrdnums := NULL;
   ELSE
      rcrdnums := rcrdnums;
      --SUBSTR (rcrdnums, 1, LENGTH (rcrdnums) - 1);
   END IF;

   DBMS_OUTPUT.PUT_LINE (rcrdnums);

   OPEN output FOR
      SELECT *
        FROM Table_NAME ERR
             INNER JOIN (    SELECT REGEXP_SUBSTR (rcrdnums,
                                                   '[^,]+',
                                                   1,
                                                   LEVEL)
                                       AS EVENT
                               FROM DUAL
                         CONNECT BY REGEXP_SUBSTR (rcrdnums,
                                                   '[^,]+',
                                                   1,
                                                   LEVEL)
                                       IS NOT NULL) EVENT_P
                ON EVENT_P.EVENT = ERR.RCRDNUM;
END;
/

And to test, I'd call your procedure like so:

declare
  v_refcur sys_refcursor;
begin
  xyz(in_startdt => to_date('01/10/2016', 'dd/mm/yyyy'),
      in_enddt => to_date('05/10/2016', 'dd/mm/yyyy'),
      output => v_refcur);
end;
/

N.B. it's bad practice to use "select *" in production code - you should explicitly specify the columns you're wanting to get back; that way, if someone adds a column, your code won't cause something to break because it won't pass that extra column along.

ORA-01861: literal does not match format string.while executing procedure

2 Answers2