This problem cropped up a few days ago, is not consistent and is unlike anything I've ever encountered. I am using a local variable in a procedure to hold a date value that I use in two different parts of the code. The first is an INSERT statement to a table and the second is a call to another procedure. Here is an example of what the code is doing, edited for brevity and obfuscation of object names:
create or replace procedure myProc
as
lv_date date;
begin
lv_date := sysdate;
dbms_output.put_line ( 'Before Insert: '||to_char ( lv_date,'mm/dd/yyyy hh:mi:ss am') );
insert into local_tab (
DateCol
) values (
lv_date
);
ANOTHER_PKG.ANOTHER_PROC ( pi_date => lv_date );
dbms_output.put_line ( 'After Proc: '||to_char ( lv_date,'mm/dd/yyyy hh:mi:ss am') );
end;
/
The procedure that is called sends the date to a remote database via where it is inserted into a table. The dbms_outputs and the remote table all have one date where the "Local_Tab" has a date that is one second less, but not consistently. For example:
dbms_output = Before Insert: 11/21/2012 02:13:22 pm
Local_tab = "11/21/2012 2:13:21 PM"
dbms_output = "After Proc: 11/21/2012 02:13:22 pm"
Remote table = "11/21/2012 2:13:22 PM"
My first instinct was that the use of SYSDATE was somehow causing a drift as the operations were performed, but the DBMS_OUTPUT shows that the value is unchanged AFTER all the operations. I also tried a variation of the code where I added "returning DateCol into lv_date" to the INSERT, but, again, the second DBMS_OUTPUT showed that the local variable held the expected DATE value.
Any help is greatly appreciated!
Regards,
Eric
DB Version Information:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Edited by: 972503 on Nov 21, 2012 10:08 AM
Edited by: 972503 on Nov 21, 2012 10:11 AM