A reader, May 17, 2016 - 10:13 am UTC
I have declared them as DATE datatype only..but still it failing even if use to_Date instead of to_char
start_DAT DATE;
Nstart_DAT DATE;
ORA-01861: literal does not match format string
if start_DAT is not null then
set_clause := set_clause || 'start_DAT =to_date(:new8,'YYYY-MM-DD'),';
where_clause := where_clause || ' and start_DAT = to_date(:old8,'YYYY-MM-DD')';
end if;
if start_DAT is not null then
dbms_sql.bind_variable(cur, 'old8', start_DAT);
dbms_sql.bind_variable(cur, 'new8', Nstart_DAT);
end if;
May 17, 2016 - 12:45 pm UTC
Can you provide a complete test case? i.e. full create table + all the PL/SQL + parameters you use to call it?
It's tricky for us to debug without this...
Your bind vars
Gh, May 17, 2016 - 2:20 pm UTC
Replace ....=to_date(:new8,'YYYY-MM-DD')
By
... = to_date(to_char (:new8,'YYYY-MM-DD'))
For both bind conversion
May 17, 2016 - 3:57 pm UTC
I'm not sure how that would fix the issue?
Also, I doubt that's what the OP wants! Looks to me like they're setting everything to the new value that has the old value.
Followup
Gh, May 17, 2016 - 6:02 pm UTC
Requester said that he define the bind vars as date types. So to_date of date is not a good idea.
That's why I said converting the date will work all the way
May 18, 2016 - 8:06 am UTC
I see now, I missed that part.
Followup
Gh, May 17, 2016 - 6:02 pm UTC
Requester said that he define the bind vars as date types. So to_date of date is not a good idea.
That's why I said converting the date will work all the way
A reader, May 18, 2016 - 7:14 am UTC
Thank you for the suggestions and for your time.
The requirement is to update the old values which are of DATE type with the new date values.
Here both old and new parameters of DATE type.
So it worked without ant conversion.
+++++
if start_Dat is not null then
set_clause := set_clause || 'start_Dat =:new8,';
where_clause := where_clause || ' and start_Dat =:old8';
end if;
+++++
Gh, May 18, 2016 - 2:58 pm UTC
No problem. Here you omit the to_date from the original. So oracle did implicit conversion. .
Yes double convert or do no conversion at all
quote syntax
Alistair Wall, May 20, 2016 - 2:34 pm UTC
In your original code, you have single quotes (around the data format) inside quote-delimited strings. These lines would work with q notation:
set_clause := set_clause || q'{start_DAT =to_char(:new8,'YYYY-MM-DD'),}';
where_clause := where_clause || q'{ and CARD_EXPIRY_DAT = to_date(:old8,'YYYY-MM-DD')}';
May 20, 2016 - 2:42 pm UTC
Yep, good point.