how to declare a date (with time) variable in pl/sql

Question

I want to use a date( DD/MM/YYYY HH:MI:SS ) variable in pl/sql. I'm using the following code but it doesn't work :

BEGIN
  declare dateMig date ;
  dateMig  := to_date('19/05/2017 05:05:00', 'DD/MM/YYYY HH:MI:SS');
  exec P_MY_PROC(100,'CHECK',dateMig);
END;

Can anyone help please? what am I doing wrong?

Solution

It would be helpful if you could explain what you mean by "doesn't work" - i.e. any error messages and/or unexpected results that you get.

However, there are a couple of obvious things wrong with your procedure:

1. You have the declaration section inside the execution block - that's not going to work for what you're wanting to do. PL/SQL programs consist of the declaration section, the execution section and the exception section in that order.

2. You're attempting to call a procedure using exec inside the PL/SQL program. That's not going to work as exec (or, to give it its full name, execute) is a SQL*Plus command not a PL/SQL command, and it allows you to run a procedure from the command line without having to nest it in a begin/end block. In PL/SQL you don't need to use exec.

So, your code should look something like:

declare
  datemig date;
begin
  datemig := to_date('19/05/2017 05:05:00', 'dd/mm/yyyy hh24:mi:ss');

  p_my_proc(100, 'CHECK', datemig);
end;