Data loss by date, minute, and second during Oracle Storage

Data loss by date, minute, and second during Oracle Storage

Today, a developer called me to say that Oracle encountered a strange phenomenon. In the stored procedure, values of date type are lost in minutes, minutes, And seconds. Let's do an experiment:
 
SQL> drop table test purge;
SQL> create table test
(
Fill_date date
);
SQL> insert into test values (sysdate );
SQL> commit;
SQL> select to_char (fill_date, 'yyyy-MM-dd HH24: mi: ss') from test;
TO_CHAR (FILL_DATE ,'
-------------------
 
17:47:22
 
SQL> CREATE OR REPLACE PROCEDURE test_p
Is
S_ SQL varchar2 (500 );
S_date date;
Begin
S_date: = to_date ('2017-07-18 17:24:32 ', 'yyyy-MM-dd HH24: mi: ss ');
S_ SQL: = 'Update test SET fill_date = ''' | s_date | '''';
Execute immediate s_ SQL;
Commit;
End;
 

/
 
SQL> call test_p ();
 

SQL> select to_char (fill_date, 'yyyy-MM-dd HH24: mi: ss') from test;
TO_CHAR (FILL_DATE ,'
-------------------
00:00:00 -- we can see that the time, minute, and second have been lost. It is suspected that it is caused by implicit conversion.
 

-- Adjust the stored procedure to see what is printed.
 

SQL> CREATE OR REPLACE PROCEDURE test_p
Is
S_ SQL varchar2 (500 );
S_date date;
Begin
S_date: = to_date ('2017-07-18 17:24:32 ', 'yyyy-MM-dd HH24: mi: ss ');
S_ SQL: = 'Update test SET fill_date = ''' | s_date | '''';
Dbms_output.put_line (s_ SQL );
Execute immediate s_ SQL;
Commit;
End;
 

/
 

SQL> set serveroutput on
 

SQL> call test_p ();
UPDATE test SET fill_date = '18-August 14-14'
The call is complete.
 
-- If implicit conversion is performed, bind the variable.
 
SQL> CREATE OR REPLACE PROCEDURE test_p
Is
S_ SQL varchar2 (500 );
S_date date;
Begin
S_date: = to_date ('2017-07-18 17:24:32 ', 'yyyy-MM-dd HH24: mi: ss ');
S_ SQL: = 'Update test SET fill_date =: 1 ';
Execute immediate s_ SQL using s_date;
Commit;
End;
 

/
 
SQL> call test_p ();
The call is complete.
 

SQL> select to_char (fill_date, 'yyyy-MM-dd HH24: mi: ss') from test;
TO_CHAR (FILL_DATE ,'
-------------------
17:24:32

Use JDBC to call Oracle stored procedures and functions in Java

Local compilation of Oracle stored procedures

Use of Oracle stored procedures and REF CURSOR

Solution to prompt "insufficient Permissions" during Oracle Storage

Oracle uses the stored procedure to return the result set development report

Tips for using temporary tables in Oracle stored procedures