ORA-01861: literal does not match format string in package function

Question

I have the following select with the returned result of one row:

        SELECT *
          FROM (SELECT   (SELECT dokdatum
                            FROM dokumente
                           WHERE dokid = '00100002LNWCAJ') AS tckdatum,
                         aktzeitdatum aktdatum,
                         (SELECT tckdokzeit
                            FROM tickets
                           WHERE tckid = '00100000000ICQ') tckzeit,
                         (hh * 60 + mm) * 60 AS aktzeit, firmaid
                    FROM (SELECT TO_DATE (aktdatumuhrzeitunitup,
                                          'DD.MM.YYYY'
                                         ) aktzeitdatum,
                                 a.firmaid,
                                 TO_NUMBER (TO_CHAR (aktdatumuhrzeitunitup, 'HH24')
                                           ) hh,
                                 TO_NUMBER (TO_CHAR (aktdatumuhrzeitunitup, 'MI')) mm
                            FROM aktivitaeten a, aktivitaetenarten aa
                           WHERE a.aktartid = aa.aktartid(+)
                             /* downtime terminated */
                             AND aktunitup = 1
                             AND tckid = '00100000000ICQ'
                             AND (aktartstatistik IS NULL OR aktartstatistik = 1)
                             AND a.aktdatumuhrzeitunitup IS NOT NULL)
                ORDER BY ((aktdatum - tckdatum) * 24 * 60 * 60 + (aktzeit - tckzeit)
                         ) DESC)
         WHERE ROWNUM < 2;

Result is:

 11.03.2016 ||  11.03.2016  ||  41334 ||  41940  ||  001

This statement is executable without errors. But when I try to use it in a package function with

 FUNCTION xy ...
 IS
        CURSOR c_cdt_aktdatumuhrzeitunitup
        IS
             --the above select

        r_cdt_aktdatumuhrzeitunitup   c_cdt_aktdatumuhrzeitunitup%ROWTYPE;

  BEGIN

        OPEN c_cdt_aktdatumuhrzeitunitup;

        --Exception is thrown with this statement
        FETCH c_cdt_aktdatumuhrzeitunitup
        INTO r_cdt_aktdatumuhrzeitunitup;

Then I receive the ORA-01861: literal does not match format string error on the FETCH...INTO line

Does anyone know why?

I see the same value `aktdatumuh...`used within a `TO_CHAR` and a `TO_DATE`; is this a `varchar` or a `date` or else? depending on this, one of its usage is wrong. If it is a `varchar2`, are you sure it is in format `'DD.MM.YYYY'`? PS it would be easier it fou could post your code as text, not images — Aleksej, Mar 11 '16 at 11:16
So, the `to_date` at row 61 can be a problem; maybe you needed a `to_char` — Aleksej, Mar 11 '16 at 11:24
I can't use a `to_char` there because I need a date for the `ORDER BY` in the outer select (it's called `aktdatum` there) — wasp256, Mar 11 '16 at 11:27
Okay I fixed it by using `to_char` in the inner one and then `to_date` in the outer `ORDER BY`, thanks for the help — wasp256, Mar 11 '16 at 11:34
Maybe I'm missing something, but if you need the field as a date, and it's stored as a date on DB, you can even simply remove to_date, without to_char; if you need to cut off the hh.mm.ss informations you can use trunc() on the date field — Aleksej, Mar 11 '16 at 11:35

ORA-01861: literal does not match format string in package function

0 Answers0