Dynamic PL/SQL date parameter with time value retained

Question

It might be a silly problem but I cant find a solution with "DATE" type passed in a PL/SQL proc which is called dynamically. What I need is to pass both date and time parts in the called proc:

create or replace 
PROCEDURE DATE_TIME_TEST (  dte_Date_IN  IN DATE ) 
IS
    vch_SQL_Stmnt VARCHAR2(2000);
BEGIN
    DBMS_OUTPUT.PUT_LINE('Date is :'||TO_CHAR(dte_Date_IN, 'DD-Mon-YYYY HH24:MI:SS'));

END;
/

declare
    v_sql varchar2(2000);
begin
    v_sql := 'begin DATE_TIME_TEST( dte_Date_IN => '''|| 
              sysdate || ''''|| '); end;';
    execute immediate v_sql;
end; 
/

The output here is - Date is :27-Aug-2013 00:00:00.

I want it to be - Date is :27-Aug-2013 13:01:09

Solution

Use bind variables

SQL> create or replace procedure proc( p_dt in date )
  2  as
  3  begin
  4    dbms_output.put_line( to_char( p_dt, 'yyyy-mm-dd hh24:mi:ss' ));
  5  end;
  6  /

Procedure created.

SQL> declare
  2    l_sql varchar2(1000);
  3  begin
  4    l_sql := 'begin proc(:dt); end;';
  5    execute immediate l_sql using sysdate;
  6  end;
  7  /
2013-08-26 22:14:26

PL/SQL procedure successfully completed.

The problem with your code is that in order to build up your string, Oracle has to convert the DATE to a VARCHAR2. It does that using your session's NLS_DATE_FORMAT. But your session's NLS_DATE_FORMAT probably doesn't include the time component so the time is lost when your procedure is actually called. Using bind variables means that you don't have to deal with that sort of implicit conversion (it is also more efficient and more secure).

If you really wanted to avoid using bind variables, you could explicitly cast sysdate to a string using a to_char and then put a to_date in the dynamic procedure call. But that's a lot of extra code and a number of unnecessary conversions.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_sql varchar2(1000);
  3  begin
  4    l_sql := q'{begin proc(to_date('}' ||
  5               to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') ||
  6               q'{', 'yyyy-mm-dd hh24:mi:ss')); end;}';
  7    execute immediate l_sql;
  8* end;
SQL> /
2013-08-26 22:19:52

PL/SQL procedure successfully completed.

Comments

[raj_arni]

Great. Thanks a lot Justin. This is exactly I was looking for. The only thing is I want to use this in DBMS_SCHEDULER.create_job, will you have any idea how I can use bind variables there?