I have a Function defined in a package that uses a REF CURSOR and IF ELSE logic to assign a select statement to the cursor. The Function executes with an ORA-01722: invalid number error. I believe this is due to a mistake in the escape characters:
' SELECT TO_CHAR(tcis_case.offence_datetime, ''yyyy'') YEAR, tcis_case.status, COUNT(tcis_case.ticket_no) TICKET_NO, ' ||
' SUM(tcis_part_payment.AMT_ORIGINAL) ORIGINAL, ' ||
' SUM(tcis_part_payment.AMT_PAID) PAID, ' ||
' SUM(CASE WHEN tcis_person.reason_code = ''A'' THEN 1 ELSE 0 END) A, ' ||
' SUM(CASE WHEN tcis_person.reason_code = ''D'' THEN 1 ELSE 0 END) D, ' ||
' SUM(CASE WHEN tcis_person.reason_code = ''F'' THEN 1 ELSE 0 END) F, ' ||
' SUM(CASE WHEN tcis_person.reason_code = ''N'' THEN 1 ELSE 0 END) N, ' ||
' SUM(CASE WHEN tcis_person.reason_code = ''O'' THEN 1 ELSE 0 END) O, ' ||
' SUM(CASE WHEN tcis_person.reason_code = ''INF'' THEN 1 ELSE 0 END) INF, ' ||
' SUM(CASE WHEN tcis_person.reason_code = ''WFR'' THEN 1 ELSE 0 END) WFR ' ||
' FROM tcis_case ' ||
' join tcis_part_payment on tcis_case.tcis_case_id = tcis_part_payment.tcis_case_id ' ||
' join tcis_person on tcis_case.tcis_case_id = tcis_person.tcis_case_id ' ||
' where tcis_person.person_no = 1 ' ||
' AND tcis_case.unit = ''M'' ' ||
' AND tcis_case.sub_unit = ''P'' ' ||
' AND tcis_case.status IN (''P'', ''C'') ' ||
' AND (''' || P_DATE_FROM ||''' IS NULL OR tcis_case.offence_datetime >= TO_DATE(TO_CHAR(''' || P_DATE_FROM ||''', ''yyyy''),''yyyy'')) ' ||
' AND (''' || P_DATE_TO ||''' IS NULL OR tcis_case.offence_datetime < TO_DATE(TO_CHAR(''' || P_DATE_TO ||''', ''YYYY''),''yyyy'')) ' ||
' GROUP BY to_char(tcis_case.offence_datetime, ''yyyy''), tcis_case.status ';
The command from sqlplus is:
select pkg_name.function('DD-MMM-YY','DD-MMM-YY', 1) from dual;
Aucun commentaire:
Enregistrer un commentaire