drop table employee;
drop table keep_scn;
REM keep_scn is a temporary table to store scns that we are interested in
create table keep_scn (scn number);
set echo on
create table employee (
employee_no number(5) primary key,
employee_name varchar2(20),
employee_mgr number(5)
constraint mgr_fkey references employee on delete cascade,
salary number,
hiredate date
);
REM Populate the company with employees
insert into employee values (1, 'John Doe', null, 1000000, '5-jul-81');
insert into employee values (10, 'Joe Johnson', 1, 500000, '12-aug-84');
insert into employee values (20, 'Susie Tiger', 10, 250000, '13-dec-90');
insert into employee values (100, 'Scott Tiger', 20, 200000, '3-feb-86');
insert into employee values (200, 'Charles Smith', 100, 150000, '22-mar-88');
insert into employee values (210, 'Jane Johnson', 100, 100000, '11-apr-87');
insert into employee values (220, 'Nancy Doe', 100, 100000, '18-sep-93');
insert into employee values (300, 'Gary Smith', 210, 75000, '4-nov-96');
insert into employee values (310, 'Bob Smith', 210, 65000, '3-may-95');
commit;
REM Show the entire org


select lpad(' ', 2*(level-1)) || employee_name Name
from employee
connect by prior employee_no = employee_mgr
start with employee_no = 1
order by level;
REM Sleep for 5 minutes to avoid querying close to the table creation
REM (the mapping of scn->time has 5 minutes granularity)
execute dbms_lock.sleep(300);
REM Store this snapshot for later access through Flashback
declare
I number;
begin
I := dbms_flashback.get_system_change_number;
insert into keep_scn values (I);
commit;
end;
/
REM Scott decides to retire but the transaction is done incorrectly
delete from employee where employee_name = 'Scott Tiger';
commit;
REM notice that all of scott's employees are gone
select lpad(' ', 2*(level-1)) || employee_name Name
from employee
connect by prior employee_no = employee_mgr
start with employee_no = 1
order by level;
REM Flashback to see Scott's organization
declare
restore_scn number;
begin
select scn into restore_scn from keep_scn;
dbms_flashback.enable_at_system_change_number (restore_scn);
end;
/
REM Show Scott's org.
select lpad(' ', 2*(level-1)) || employee_name Name
from employee
connect by prior employee_no = employee_mgr
start with employee_no =
(select employee_no from employee where employee_name = 'Scott Tiger')
order by level;
REM Restore scott's organization.
declare
scotts_emp number;
scotts_mgr number;
cursor c1 is
select employee_no, employee_name, employee_mgr, salary, hiredate
from employee
connect by prior employee_no = employee_mgr
start with employee_no =
(select employee_no from employee where employee_name = 'Scott Tiger');
c1_rec c1 % ROWTYPE;
begin
select employee_no, employee_mgr into scotts_emp, scotts_mgr from employee
where employee_name = 'Scott Tiger';
/* Open c1 in flashback mode */
open c1;
/* Disable Flashback */
dbms_flashback.disable;
loop
fetch c1 into c1_rec;
exit when c1%NOTFOUND;
/*
Note that all the DML operations inside the loop are performed
with Flashback disabled
*/
if (c1_rec.employee_mgr = scotts_emp) then
insert into employee values (c1_rec.employee_no,
c1_rec.employee_name,
scotts_mgr,
c1_rec.salary,
c1_rec.hiredate);
else
if (c1_rec.employee_no != scotts_emp) then
insert into employee values (c1_rec.employee_no,
c1_rec.employee_name,
c1_rec.employee_mgr,
c1_rec.salary,
c1_rec.hiredate);
end if;
end if;
end loop;
end;
REM Show the restored organization.
select lpad(' ', 2*(level-1)) || employee_name Name
from employee
connect by prior employee_no = employee_mgr
start with employee_no = 1
order by level;[@more@]