-- To make use of this lesson, you must create the pasta table and populate it.

-- While in a flashback state using DBMS_FLASHBACK, you 
-- cannot use insert, update, or delete. So, to recover lost rows,
-- you must use pl/sql to recover the data into a cursor. 
-- From there, you can switch back into the present time, then 
-- insert it back into the table it was deleted from.
-- note that you DISABLE DBMS_FLASBACK immediately after opening the 
-- cursor. This is bug free, unlike the example given in Oracle's 
-- online documentation.

-- Script by Edward Stoever, Jan 16, 2003
-- NOTE: DBMS_FLASHBACK is an Oracle 9i or higher feature that allows 
-- you to read data as it was in the past. You may adjust the 
-- retention of this data with the parameter undo_retention=3600
-- whereas 3600 equals the number of seconds that data is stored in
-- undo segments.

delete from pasta where tomato like '%bb%';
commit; 

 -- Oh no!!! I did not want to do that!
 -- Your boss has just sent you a message over the network...
 -- It reads, "YOU ARE FIRED!"
 -- You think, "Quick! Get that data back!" 


EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE - (15/(24*60))); 

declare
cursor c1 is
    select * from pasta where tomato like '%bb%';
c1_rec pasta%ROWTYPE; 


begin 

 open c1;
 dbms_flashback.disable; 

 loop 

 fetch c1 into c1_rec;
 exit when c1%NOTFOUND; 

  insert into pasta (cheese, tomato, salt, oregano, olive_oil)
             values (c1_rec.cheese,
                     c1_rec.tomato,
                     c1_rec.salt,
                     c1_rec.oregano,
                     c1_rec.olive_oil); 


 end loop;
 commit;
 close c1;
end;
/


-- You receive another message from your boss....
-- "Nevermind!"