--Package Specification
create or replace package flashback_pkg as

  procedure create_restore_point_for_spec_pdb_prc(v_pdb_name in varchar2);
  procedure create_restore_point_for_all_pdb_prc;

end flashback_pkg;


--Package Body
create or replace package body flashback_pkg as

  --create_restore_point_for_spec_pdb_prc
  procedure create_restore_point_for_spec_pdb_prc(v_pdb_name in varchar2) as
    date1 varchar2(14);
    v_cnt number;
  begin
  
    select to_char(sysdate, 'yyyymmddhh24miss') into date1 from dual;
    select count(1)
      into v_cnt
      from dba_pdbs
     where pdb_name = upper(v_pdb_name);
  
    if v_cnt = 0 then
      dbms_output.put_line('PDB name is incorrect');
    else
      execute immediate ' create restore point RESTORE_POINT_' ||upper(v_pdb_name) || '_' || date1 || ' for pluggable database ' || v_pdb_name;
      --insert into restore_point_list (select name, time, upper(v_pdb_name) from v$restore_point where name = 'RESTORE_POINT_' || upper(v_pdb_name) || '_' || date1);
      --commit;
      dbms_output.put_line('restore point name : ' || 'RESTORE_POINT_' || v_pdb_name || '_' || date1);
    end if;
  end create_restore_point_for_spec_pdb_prc;

  --create_restore_point_for_all_pdb_prc
  procedure create_restore_point_for_all_pdb_prc as
    date1 varchar2(14);
    v_cnt number;
  
    cursor pdb_cur is
      select pdb_name from dba_pdbs where pdb_name <> 'PDB$SEED';
  
  begin
  
    select to_char(sysdate, 'yyyymmddhh24miss') into date1 from dual;
  
    for pdb_rec in pdb_cur loop
      execute immediate ' create restore point restore_point_' || pdb_rec.pdb_name || '_' || date1 || ' for pluggable database ' || pdb_rec.pdb_name;
      --insert into restore_point_list (select name, time, pdb_rec.pdb_name from v$restore_point where name = 'RESTORE_POINT_' || pdb_rec.pdb_name || '_' || date1);
      --commit;
      dbms_output.put_line('restore point name : ' || 'RESTORE_POINT_' || pdb_rec.pdb_name || '_' || date1);
    end loop;
  end create_restore_point_for_all_pdb_prc;

  --flashback_pdb_prc
  procedure flashback_pdb_prc(v_pdb_name      in varchar2,
                              v_restore_point in varchar2) as
    v_cnt1 number;
    v_cnt2 number;
  begin
    select count(1)
      into v_cnt1
      from dba_pdbs
     where pdb_name = upper(v_pdb_name);
    select count(1)
      into v_cnt2
      from v$restore_point
     where name = v_restore_point;
    if v_cnt1 = 0 then
      dbms_output.put_line('PDB name is incorrect');
    elsif v_cnt2 = 0 then
      dbms_output.put_line('RESTORE POINT name is incorrect');
    else
      execute immediate 'alter pluggable database ' || v_pdb_name ||' close immediate ';
      execute immediate 'flashback pluggable database ' || v_pdb_name ||' to restore point ' || v_restore_point;
      execute immediate 'alter pluggable database ' || v_pdb_name ||' open resetlogs';
      dbms_output.put_line('flashback pdb completed');
    end if;
  end flashback_pdb_prc;

  --get_flashback_retention_prc
  function get_flashback_retention_prc return varchar2 as
    v_num varchar2(100);
  begin
    select 'db_flashback_retention_target = ' || value / 60 || ' hours'
      into v_num
      from v$parameter
     where name = 'db_flashback_retention_target';
    return v_num;
  end get_flashback_retention_prc;

end flashback_pkg;