Dynamically Create/Drop Restore Points

Task Objective: To create a Guaranteed Restore Point Dynamically and remove the earlier Restore Point if more than 2 exist.

set serveroutput on
declare

v_kount	number := 0;
v_name varchar2(100);
v_name2 varchar2(100);
v_time	date;
v_sql varchar2(4000);
v_sql2 varchar2(4000);


begin

dbms_output.enable(100000);

select to_char(sysdate,'MONDD') into v_name from dual;

	begin

	v_sql := 'create restore point ' || v_name || ' guarantee flashback database';
        
        
	execute immediate v_sql;
	commit;
	
	dbms_output.put_line('Restore Point Created ' || v_name);
	
	exception
	when others then
	dbms_output.put_line(sqlerrm);

	end;

begin


select count(*) into v_kount from v$restore_point;

     if v_kount > 2 then
     
     select name, time into v_name2, v_time from v$restore_point where time = (select min(time) from v$restore_point);
     
     v_sql2 := 'drop restore point ' || v_name2;
     
     execute immediate v_sql2;  
     commit;
     
     dbms_output.put_line('Restore Point Dropped ' || v_name2);
     
     
     else
       
       dbms_output.put_line('Nothing to Drop');
       
     end if;

     exception
     when others then
     dbms_output.put_line(sqlerrm);
 
 end;
 

end;
/