Flashback Query and one other thing

[mike]

I convinced someone that uses one of the other inferior products to switch to PL/SQL Developer.

Within a day, he came up with 2 questions that stumped me so I thought I would ask.

What is the best way to use flashback query with PL/SQL Developer?

I can't do an exec dbms_flashback in a SQL window.
In the command window, I don't have a nice grid when I do the select statement after turning flashback query on.
I haven't been successful in the test window either.

The only sort of good way that I've found is to exec dbms_flashback in a command window and then change it to be a SQL window. That seems kind of cheesy though.

The other question was that the other inferior product would create a script to re-create a view including all triggers, synonyms etc. PL/SQL Developer doesn't do that as far as I can tell. He said that it is nice, because in one place he can quickly see everything about the view without multiple clicks. That seemed to be a good point. PL/SQL Developer doesn't do that, right? Or did I miss how to do it?

Thanks,

Mike

 

[Marco Kalter]

You can execute PL/SQL Blocks like this:

begin
dbms_flashback.enable_at_time(...);
end;
/

You can subsequently run your DML statements.

There is indeed no easy way to see the DDL of a view and its triggers. In the Object Browser you can however select a view, open its Triggers folder, and drag & drop the view and triggers in an empty SQL Window.

 

[mike]

Thanks for the reply. When I do that though, I get this error:

ORA-08183: Flashback cannot be enabled in the middle of a transaction
ORA-06512: at "SYS.DBMS_FLASHBACK", line 0
ORA-06512: at line 2

View program sources of error stack?

I got the same error when I tried it within a block in the test window. I can only get it to work when I run it in the command window as exec dbms_flashback.enable_at_time(...);

Maybe we need to be able to execute stuff in the SQL window sometimes?

Thanks,

Mike

 

[Marco Kalter]

Did you start a transaction before enabling flashback? If so, either commit it, or include a commit:

begin
commit;
dbms_flashback.enable_at_time(...);
end;
/

 

[mike]

I didn't think about my AfterConnect.sql starting a transaction. I threw a commit in there and it works now.

Thanks for your help!

Mike

 

[William Robertson]

You could also use an AS OF TIMESTAMP expression in a query to apply flashback to a single table, e.g:

SELECT * FROM scott.emp
AS OF TIMESTAMP TIMESTAMP '2006-04-16 00:00:00';

 

[mike]

Thanks! I'll remember that next time also.

Mike

 

[Kirurgs]

Well, you can apply AS OF TIMESTAMP to join as well, but you have to apply that to every table joined!
In Oracle 9i AS OF TIMESTAMP has +-5mins precision but starting 10g it's +-3sec...