title: TO_DATE issue with Oracle DBMS_SQL.EXECUTE function
body: <p>I am trying to invoke an Oracle stored procedure using the DBMS_SQL.EXECUTE function. I am passing the PL/SQL block as the input argument to the function which invokes the DB procedure using the DBMS_SQL.EXECUTE function.  The procedure is having a string argument which I pass the current date in string format using the TO_DATE function.  See below for the syntax I used to invoke the procedure.  When the procedure is invoked the date passed is saved in the database as DATE data type.  The issue I am facing is the time part of the date is truncated and the time becomes 12:00 A.M irrespective of the time that is passed to the function.  Can someone please let me know what I am doing something wrong?</p>
  
  <p>SQL block used to invoke the function:</p>
  
  <pre><code>plsql_block =BEGIN P_USER_TIME(to_date('21-JUL-2012 03:30:30','DD-MON-YYYY HH24:MI:SS')); Return; End;
  </code></pre>
  
  <p>SQL syntax of the function invoking the procedure : </p>
  
  <pre><code>ret_int := DBMS_SQL.OPEN_CURSOR;
  
  DBMS_SQL.PARSE(ret_int,plsql_block,DBMS_SQL.NATIVE);
  
  ret_int_execute := DBMS_SQL.EXECUTE(ln_dbms_cur);
  </code></pre>
  
  <p>PFB the oracle code block</p>
  
  <p>I am using a function that takes date as varchar input and the n passes it to another procedure.I could see that the time past associated with the date is not getting inserted at some scenario's. please find below my function</p>
  
  <pre><code>iv_plsql4 :='10-08-2012 07:30:30';
  ln_dbms_cur := DBMS_SQL.OPEN_CURSOR;
  iv_plsql2 := BEGIN PKG_PRADEEP.P_INSERTDATE(to_date(iv_plsql4,'DD-MM-YYYY HH24:MI:SS'));
  
  DBMS_OUTPUT.put_line(iv_plsql);
  DBMS_SQL.PARSE(ln_dbms_cur,iv_plsql,DBMS_SQL.NATIVE);
  ln_cur_execute := DBMS_SQL.EXECUTE(ln_dbms_cur);
  This code inserts the date in to the database but the time comes as 12:00 A.M.
  </code></pre>
  
  <p>but if I change the string iv_plsql2 as given below the date gets inserted with the TIME field.</p>
  
  <pre><code>iv_plsql2 := BEGIN PKG_PRADEEP.P_INSERTDATE(to_date('10-AUG-2012 07:30:30','DD-MM-YYYY HH24:MI:SS'));
  </code></pre>
  
  <p>Can someone explain why this happens?</p>