CODEWITHSUNDEEP
oracle-databaseto-dateinput-parameters
Mélanie
Mélanie

Reputation: 31

Oracle - SP Date Input parameter truncated?

I have a procedure which takes some input parameters as date. When I try to execute my SP, here are the values assigned to each parameters enter image description here

However when I execute I get an error message about the date format on the p_schedule_value parameter. Indeed we can see it does not pass the date in the specified format; instead it truncates the time and pass it as yyyy-MMM-dd You can see on below screenshot the dynamic_sql in the output with the error message and the value of p_schedule_value enter image description here

Why is my time being truncated ? it seems it ignores the TO_DATE conversion thanks

EDIT: Tried to remove the TO_DATE in the SP, it compiles but the time is still being truncated. Only the date part is inserted in my row. enter image description here

Upvotes: 0

Views: 155

Answers (1)

F.Madsen
F.Madsen

Reputation: 702

In the "insert_or_upd....." procedure the argument p_schedule_value is already a DATE, so remove the TO_DATE in the merge statement.

UPDATED Answer

It seems like the execute immediate will pass the dates as varchar2's, So it might be better to be explicit in the format of the dates. 

plsql_block := 
'merge into MOVEMENTS m 
using (select :id as movement_id, '||q'"to_date(:dt,'YYYYMMDDHH24MI')"'||' as movement_date from dual) s 
   on (m.MOVEMENT_ID = s.movement_id and m.MOVEMENT_DATE = s.movement_date) 
   when matched then update set ' ||'colname'||q'" = to_date('"'||to_char(sysdate,'YYYYMMDDHH24MI')||q'"','YYYYMMDDHH24MI')
   when not matched then insert (MOVEMENT_ID, MOVEMENT_DATE,MOVEMENT_ETD) 
   (:id,:dt,to_date(:value1,'YYYYMMDDHH24MI'))"' from dual;

execute immediate plsql_block USING (......   ,to_char(p_mvt_date,'YYYYMMDDHH24MI'),to_char(p_schedule_value,'YYYYMMDDHH24MI'));

The statement you execute will then look something like this..

"merge into MOVEMENTS m 
using (select :id as movement_id, to_date(:dt,'YYYYMMDDHH24MI') as movement_date from dual) s 
   on (m.MOVEMENT_ID = s.movement_id and m.MOVEMENT_DATE = s.movement_date) 
   when matched then update set colname = to_date('201903151837','YYYYMMDDHH24MI')
   when not matched then insert (MOVEMENT_ID, MOVEMENT_DATE,MOVEMENT_ETD) 
   (:id,:dt,to_date(:value1,'YYYYMMDDHH24MI'))"

Upvotes: 1

Related Questions