Home » SQL & PL/SQL » SQL & PL/SQL » Flashback Transaction Query and date datatypes (10g 2)
Flashback Transaction Query and date datatypes [message #331942] Sun, 06 July 2008 15:53 Go to next message
bpeasey
Messages: 46
Registered: March 2005
Member
Hi,

I've been testing out the Flashback Transaction Query and noticed a problem. In the undo_sql column with inserts, date data type is missing the hours, minutes and seconds values.
select undo_sql from flashback_transaction_query where table_owner = 'SCOTT' and operation = 'DELETE'

UNDO_SQL
insert into "SCOTT"."TEST"("TEST_ID","DTM") values ('2500',TO_DATE('06-JUL-08', 'DD-MON-RR'));
insert into "SCOTT"."TEST"("TEST_ID","DTM") values ('2499',TO_DATE('06-JUL-08', 'DD-MON-RR'));
insert into "SCOTT"."TEST"("TEST_ID","DTM") values ('2501',TO_DATE('06-JUL-08', 'DD-MON-RR'));


The DTM column was originally populated with sysdate, and existing data contains hours, minutes and seconds values.
select * from scott.test where rownum < 4

TEST_ID	DTM
4	10/06/2008 1:35:14 PM
17	10/06/2008 4:50:11 PM
18	10/06/2008 5:05:13 PM


Is there a step I've missed, or is it something I need just be aware of if I used this feature in the future?

Best regards,
Brian

Re: Flashback Transaction Query and date datatypes [message #331971 is a reply to message #331942] Mon, 07 July 2008 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please use SQL*Plus and copy and paste the whole session in order for us to see exactly what have done (and maybe answer to "Is there a step I've missed") and be able to reproduce it.
Also post your NLS settings regarding date/time parameters.

Regards
Michel
Re: Flashback Transaction Query and date datatypes [message #331985 is a reply to message #331942] Mon, 07 July 2008 02:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect this might be an NLS parameter problem.

Try
ALTER SESSION SET NLS_DATE_FORMAT = 'dd-Mon-yyyy hh24:mi:ss'

and then do the Flashback query again.
Re: Flashback Transaction Query and date datatypes [message #332093 is a reply to message #331985] Mon, 07 July 2008 08:49 Go to previous message
bpeasey
Messages: 46
Registered: March 2005
Member
Thanks J. and Michel,

The NLS_DATE_FORMAT was it.

Best Regards,
Brian
Previous Topic: Display Record in Specific Order
Next Topic: Outer Join
Goto Forum:
  


Current Time: Fri Feb 14 09:58:35 CST 2025