Home » SQL & PL/SQL » SQL & PL/SQL » dbms_flashback package problem (10.2.0.1.0, WIN XP)
dbms_flashback package problem [message #324563] Tue, 03 June 2008 02:51 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

I am trying to use the dbms_flashback package but it is not giving the desired results. Here is the code right from the beginning

Quote:
/*creating a test table*/

create table names_test (n varchar(10));


Quote:
inserted 10 names in it
/*deleting the rows created*/

delete from names_Test;
   commit;


Quote:
/*testing the dbms_flashback package*/

declare
     cursor c1 is select * from names_Test;
     a c1%rowtype;
  begin
     dbms_flashback.enable_to_time(sysdate - 15/1440);
     open c1;
     dbms_flashback.disable;
     loop
     fetch c1 into a ;
     exit when c1%notfound;
     insert into names_Test values(a.n);

Quote:
/*also tried displaying the values with dbms_output.put_line/

end loop;
     end;


The result
Quote:
PL/SQL procedure successfully completed


but
select * from names_Test;
Quote:
/*gives no results*/

Quote:
no rows selected


Is there any other way to use the dbms_flashback or can this block be corrected to display the exact results?



Re: dbms_flashback package problem [message #324582 is a reply to message #324563] Tue, 03 June 2008 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste the actual session, does not describe it, we are able to read and interpret your SQL*Plus session.

Regards
Michel
Re: dbms_flashback package problem [message #324673 is a reply to message #324582] Tue, 03 June 2008 11:17 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Here.

SQL> select * from names_test;

N
-------------------------------------------------------------------------------

ABO
ABP
ABQ
ABR
ABS
ABT
ABU
ABV
ABW
AAA

Quote:
10 rows selected.


SQL> delete from names_test;


Quote:
10 rows deleted.


SQL> commit;


Quote:
Commit complete.


SQL> declare 
cursor c1 is select * from names_test; 
a c1%rowtype; 
begin 
dbms_flashback.enable_at_time(sysdate - 15/1440); 
open c1; 
dbms_flashback.disable; 
loop
fetch c1 into a; 
exit when c1%notfound; 
insert into names_Test values(a.n); 
end loop; end;
  2  /


Quote:
PL/SQL procedure successfully completed.


SQL> select * from names_test;


Quote:
no rows selected


The select statement should display the contents of the table but they are not. Are you able to make out the problem?

Re: dbms_flashback package problem [message #324675 is a reply to message #324563] Tue, 03 June 2008 11:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Are you able to make out the problem?
There is no COMMIT in PL/SQL code

[Updated on: Tue, 03 June 2008 11:22] by Moderator

Report message to a moderator

Re: dbms_flashback package problem [message #324886 is a reply to message #324675] Wed, 04 June 2008 07:34 Go to previous message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

I tried to commit but it still gives the same answer.

Any idea why this may have happened?

Previous Topic: Difference while summing
Next Topic: ORA-27369: job of type EXECUTABLE failed with exit code: Unknown erro
Goto Forum:
  


Current Time: Sat Dec 10 02:57:32 CST 2016

Total time taken to generate the page: 0.06071 seconds