调用函数时出现"Literal does not match format"错误

Question

当我尝试执行函数时，函数抛出错误literal does not match format as string。

CREATE OR REPLACE FUNCTION fn_payroll_earn (p_person_id    IN NUMBER,
p_pay_period   IN DATE)
RETURN VARCHAR2
IS
earnings   NUMBER (7, 2);
BEGIN
SELECT SUM (
DECODE (pec.classification_name,
'Earnings', TO_NUMBER (prrv.result_value),
0))
earnings
INTO earnings
FROM per_all_people_f papf,
per_all_assignments_f paaf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
hr_all_organization_units_tl halu,
per_all_positions pap,
per_time_periods ptp,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f pivf,
pay_personal_payment_methods_f pppmf,
pay_element_classifications pec,
pay_element_types_f petf
WHERE     SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND ppa.effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND papf.person_id = paaf.person_id
AND paaf.primary_flag = 'Y'
AND paaf.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('R', 'Q')
AND paa.action_status = 'C'
AND ppa.action_type <> 'B'
AND paaf.organization_id = halu.organization_id
AND ppa.time_period_id = ptp.time_period_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prrv.run_result_id = prr.run_result_id
AND prrv.input_value_id = pivf.input_value_id
AND pivf.name = 'Pay Value'
AND pivf.uom = 'M'
AND TO_CHAR (ptp.start_date, 'RRRRMM') =
NVL (p_pay_period, TO_CHAR (ptp.start_date, 'RRRRMM'))
AND paaf.assignment_id = pppmf.assignment_id(+)
AND (       pppmf.assignment_id IS NOT NULL
AND ppa.effective_date BETWEEN pppmf.effective_start_date
AND pppmf.effective_end_date
OR pppmf.assignment_id IS NULL)
AND papf.person_id = p_person_id
AND pec.classification_id = petf.classification_id
AND petf.element_type_id = prr.element_type_id
AND SYSDATE BETWEEN petf.effective_start_date
AND petf.effective_end_date
ORDER BY papf.person_id;
RETURN earnings;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
WHEN OTHERS
THEN
RETURN 'error' || SQLERRM;
END fn_payroll_earn;

Answer 1

我相信错误在的下面一行

AND TO_CHAR(PTP.START_DATE, 'RRRRMM') = NVL(P_PAY_PERIOD, TO_CHAR(PTP.START_DATE, 'RRRRMM'))

p_PAY_PERIOD是一个日期，而您正在PTP.START_date上执行to_char。因此，oracle正试图在日期(p_PAY_ERIOD(和字符串之间进行比较，这意味着它需要进行隐式强制转换，但失败了。

将其更改为以下内容应能解决问题

AND TO_CHAR(PTP.START_DATE, 'RRRRMM') = NVL(TO_CHAR(P_PAY_PERIOD, 'RRRRMM'),TO_CHAR(PTP.START_DATE, 'RRRRMM'))

添加了以下问题和修复的工作示例。

--下面复制了您看到的错误。

declare
v_date date := sysdate;
v_test varchar2(1) default null;
begin
select 'X' into v_test from dual where TO_CHAR (sysdate, 'RRRRMM') = NVL    (v_date, TO_CHAR (sysdate, 'RRRRMM'));
end;

--以下修复了问题

declare
v_date date := sysdate;
v_test varchar2(1) default null;
begin
select 'X' into v_test from dual where TO_CHAR (sysdate, 'RRRRMM') = NVL(TO_CHAR(v_date, 'RRRRMM'), TO_CHAR (sysdate, 'RRRRMM'));
end;