Monday, 26 August 2013

Dynamic PL/SQL date parameter with time value retained

Dynamic PL/SQL date parameter with time value retained

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

No comments:

Post a Comment