Thanks for the question, Shivani.
Asked: December 04, 2015 - 2:12 am UTC
Last updated: December 04, 2015 - 9:20 am UTC
Version: Oracle 11.2
Viewed 1000+ times
You Asked
Hello All,
I am trying to write the code where if someone passes input string which can't be converted into date then function should go to exception handler section and for example print 'XXX'.But every time I am executing this code I am getting below error instead of printing 'XXX':
ORA-01861: literal does not match format string
ORA-06512: at "SCOTT.DATE_CHECK", line 14
ORA-01843: not a valid month
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.
create or replace function date_check(p_date varchar2 )
return date is
v_date date ;
v_errc varchar2(2000);
v_errm varchar2(2000);
begin
v_date := to_date(p_date,'YYYYMMDD');
return v_date;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('XXXX');
RETURN p_date;
end;
and Chris said...
It is printing XXXX. It's just raising the exception too.
This is because the function returns a date. In your exception handler you're returning the input parameter again. So Oracle tries to convert this to a date (again). If it's not able to do this, it raises the exception.
You need to change your code to return null instead:
SQL>create or replace function date_check(
2 p_date varchar2 )
3 return date
4 is
5 v_date date ;
6 v_errc varchar2(2000);
7 v_errm varchar2(2000);
8 begin
9 v_date := to_date(p_date,'YYYYMMDD');
10 return v_date;
11 exception
12 when others then
13 dbms_output.put_line('XXXX');
14 return p_date;
15 end;
16 /
Function created.
SQL>
SQL>select date_check ('2015-01-01') from dual;
select date_check ('2015-01-01') from dual
*
ERROR at line 1:
ORA-01861: literal does not match format string
ORA-06512: at "CHRIS.DATE_CHECK", line 14
ORA-01843: not a valid month
XXXX
SQL>
SQL>create or replace function date_check(
2 p_date varchar2 )
3 return date
4 is
5 v_date date ;
6 v_errc varchar2(2000);
7 v_errm varchar2(2000);
8 begin
9 v_date := to_date(p_date,'YYYYMMDD');
10 return v_date;
11 exception
12 when others then
13 dbms_output.put_line('XXXX');
14 return null;
15 end;
16 /
Function created.
SQL>
SQL>select date_check ('2015-01-01') from dual;
DATE_CHEC
---------
XXXX
SQL>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment