PL/SQL Date Literal with Cursor: ORA-01861: literal does not match format string

Question

CURSOR c_senior_employees is
    SELECT first_name || ' ' || last_name as full_name, job_title, email, hire_date FROM employees
    WHERE hire_date < date '2016-07-01'
    ORDER BY hire_date, full_name;

I am using PLSQL and trying to fetch a DATE column with a cursor.

The SQL statement runs as expected, but an error occurs when I complied the cursor.

ORA-01861: literal does not match format string

I have tried using date literal in other formats (e.g. date '01-JUL-2016') and produces the same error. In addition, wrapping column "hire_date" with TO_DATE() function did not work for me. What did I do wrong here? Thanks in advance.

UPDATED: working code

CREATE OR REPLACE PROCEDURE P_employees
AS
  CURSOR c_senior_employees is
    SELECT first_name || ' ' || last_name as full_name, job_title, email, hire_date FROM employees
    WHERE hire_date < date '2016-07-01'
    ORDER BY hire_date, full_name;

  CURSOR c_junior_employees is
    SELECT first_name || ' ' || last_name as full_name, job_title, email, hire_date FROM employees
    WHERE not hire_date < date '2016-07-01'
    ORDER BY hire_date, full_name;

  r_employee c_senior_employees%rowtype;

BEGIN
  open c_senior_employees;
  open c_junior_employees;
  
  htp.p('<h1>Fake Employees</h1>');

  htp.p('<p>Senior Employees</p>');
  htp.p('<table border="1">');
  htp.p('<tr>');
  htp.p('<th>Employee Name</th>');
  htp.p('<th>Job Title</th>');
  htp.p('<th>Email</th>');
  htp.p('<th>Hired on</th>');
  htp.p('</tr>');
  loop
    fetch c_senior_employees into r_employee;
    exit when c_senior_employees%notfound;
    htp.p('<tr>');
    htp.p('<td>' || r_employee.full_name || '</td>');
    htp.p('<td>' || r_employee.job_title || '</td>');
    htp.p('<td>' || r_employee.email || '</td>');
    htp.p('<td>' || r_employee.hire_date || '</td>');
    htp.p('</tr>');
  end loop;
  htp.p('</table>');
  close c_senior_employees;

  htp.p('<p>Junior Employees</p>');
  htp.p('<table border="1">');
  htp.p('<tr>');
  htp.p('<th>Employee Name</th>');
  htp.p('<th>Job Title</th>');
  htp.p('<th>Email</th>');
  htp.p('<th>Hired on</th>');
  htp.p('</tr>');
  loop
    fetch c_junior_employees into r_employee;
    exit when c_junior_employees%notfound;
    htp.p('<tr>');
    htp.p('<td>' || r_employee.full_name || '</td>');
    htp.p('<td>' || r_employee.job_title || '</td>');
    htp.p('<td>' || r_employee.email || '</td>');
    htp.p('<td>' || r_employee.hire_date || '</td>');
    htp.p('</tr>');
  end loop;
  htp.p('</table>');

  close c_junior_employees;
END;

It looks like that format you use for the date doesn't match to Oracle's default date format. I believe that this link will help you:[https://stackoverflow.com/questions/22542882/sql-error-ora-01861-literal-does-not-match-format-string-01861] — HRK, Jun 24 '22 at 08:19
Please post your full code so we can reproduce - not just the cursor definition. Also "wrapping column "hire_date" with TO_DATE() function did not work for me" - what does "not work for me" mean - error message ? — Koen Lostrie, Jun 24 '22 at 08:27
The cursor is in a procedure. By not working I mean using "to_date(cast(hire_date as varchar2(50)), 'yyyy-mm-dd')" instead of "hire_date" fails to make proper comparsion & produces the same error — Dave Yu, Jun 24 '22 at 08:30
Update: I have solved my problem. Turns out previous compile somehow got stuck and prevented me from updating my previous mistake. The cursor is fine. — Dave Yu, Jun 24 '22 at 08:54

score 0 · Accepted Answer · answered Jun 27 '22 at 02:55

Update: I have solved my problem. Turns out previous compile somehow got stuck and prevented me from updating my previous mistake. The cursor is fine.

Working code for your reference:

CREATE OR REPLACE PROCEDURE P_employees
AS
  CURSOR c_senior_employees is
    SELECT first_name || ' ' || last_name as full_name, job_title, email, hire_date FROM employees
    WHERE hire_date < date '2016-07-01'
    ORDER BY hire_date, full_name;

  CURSOR c_junior_employees is
    SELECT first_name || ' ' || last_name as full_name, job_title, email, hire_date FROM employees
    WHERE not hire_date < date '2016-07-01'
    ORDER BY hire_date, full_name;

  r_employee c_senior_employees%rowtype;

BEGIN
  open c_senior_employees;
  open c_junior_employees;
  
  htp.p('<h1>Fake Employees</h1>');

  htp.p('<p>Senior Employees</p>');
  htp.p('<table border="1">');
  htp.p('<tr>');
  htp.p('<th>Employee Name</th>');
  htp.p('<th>Job Title</th>');
  htp.p('<th>Email</th>');
  htp.p('<th>Hired on</th>');
  htp.p('</tr>');
  loop
    fetch c_senior_employees into r_employee;
    exit when c_senior_employees%notfound;
    htp.p('<tr>');
    htp.p('<td>' || r_employee.full_name || '</td>');
    htp.p('<td>' || r_employee.job_title || '</td>');
    htp.p('<td>' || r_employee.email || '</td>');
    htp.p('<td>' || r_employee.hire_date || '</td>');
    htp.p('</tr>');
  end loop;
  htp.p('</table>');
  close c_senior_employees;

  htp.p('<p>Junior Employees</p>');
  htp.p('<table border="1">');
  htp.p('<tr>');
  htp.p('<th>Employee Name</th>');
  htp.p('<th>Job Title</th>');
  htp.p('<th>Email</th>');
  htp.p('<th>Hired on</th>');
  htp.p('</tr>');
  loop
    fetch c_junior_employees into r_employee;
    exit when c_junior_employees%notfound;
    htp.p('<tr>');
    htp.p('<td>' || r_employee.full_name || '</td>');
    htp.p('<td>' || r_employee.job_title || '</td>');
    htp.p('<td>' || r_employee.email || '</td>');
    htp.p('<td>' || r_employee.hire_date || '</td>');
    htp.p('</tr>');
  end loop;
  htp.p('</table>');

  close c_junior_employees;
END;

PL/SQL Date Literal with Cursor: ORA-01861: literal does not match format string

1 Answers1