PL/SQL Date Literal with Cursor: ORA-01861: Literal与格式字符串不匹配

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;

我正在使用PLSQL并试图用游标获取DATE列。

SQL语句按预期运行，但在编译游标时出现错误。

ORA-01861: literal does not match format string

我已经尝试使用其他格式的日期文字(例如日期' 01-07-2016 ')并产生相同的错误。此外，包裹栏"hire_date"with TO_DATE()函数对我不起作用。我在这里做错了什么?提前谢谢。

已更新:工作代码

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;

Answer 1

更新:我已经解决了我的问题。原来以前的编译不知何故卡住了，阻止了我更新我以前的错误。

工作代码供参考:

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;