0

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

Mélanie
  • 31
  • 2
  • 10

1 Answers1

1

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'))"
F.Madsen
  • 702
  • 4
  • 6
  • Getting a different error message : Connecting to the database localDB. ORA-00904: "MAY": invalid identifier ORA-06512: at "RTT.INSERT_OR_UPD_MOVEMENTS_SCHEDULE_FIELDS_PROC", line 18 ORA-06512: at line 14 merge into MOVEMENTS m using (select :id as movement_id,:dt 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 MOVEMENT_ETD = 01-MAY-19 when not matched then insert (MOVEMENT_ID, MOVEMENT_DATE,MOVEMENT_ETD) values (:id,:dt,:value1) – Mélanie Mar 15 '19 at 13:14
  • and it still is missing the time – Mélanie Mar 15 '19 at 13:15
  • Have you removed the TO_DATE around arguments that are already dates? Can you show the current state of your procedure? – F.Madsen Mar 15 '19 at 15:32
  • Yes you can see the output of the proc in my comment. – Mélanie Mar 15 '19 at 16:04
  • I edited my original post with SP. I dont have an error message now but the time is still being truncacted :( – Mélanie Mar 15 '19 at 16:15
  • Not that easy :), seems like it might be the execute immediate that does an implicit cast from date and back, and then it depends on your NLS_DATE_FORMAT setting. But then might be easier to control the format al the way thru. I will edit my answer. – F.Madsen Mar 15 '19 at 17:34
  • Thanks for this! So now indeed i can see the statement execute with the time so that s great :) But when I open my table to look at my row then I can only see the date in the column. Would you know why? – Mélanie Mar 19 '19 at 11:22
  • IF you have a DATE Columbia, its contains both date and time. Normally you will have i date format setting in your tool, but you can also specify the format... Select to_char(col, 'yyyy-mm-dd hh24:mi').... – F.Madsen Mar 24 '19 at 12:53