Procedure to Create Backup Table For multiple table each having different Where condition

Question

Create or replace procedure PROC AS
V_TABLE_NAME  VARCHAR2(255);
V_LIST SYS_REFCURSOR;
DATE_VALUE_INS VARCHAR2(10);
BEGIN
    DATE_VALUE_INS:=TO_CHAR(SYSDATE,'YYMMDD');
    OPEN V_LIST FOR
    SELECT NAME FROM DW.table_name_list ;
     LOOP 
        FETCH V_LIST
        INTO V_TABLE_NAME;
        EXIT WHEN V_LIST%NOTFOUND;
        EXECUTE IMMEDIATE 'CREATE TABLE Schema.'||V_TABLE_NAME||'_'||DATE_VALUE_INS||' AS SELECT * FROM DW.'||V_TABLE_NAME;
     END LOOP;
    CLOSE V_LIST; 
end;

I have created this Proc which takes value from a table which has Table_name and create Backup using Execute Immediate.

Now the requirement has changed that i only need to create backup for partial records (i.e. where clause on each table )

I have 6 tables as such .

New Approach i am thinking is :

 EXECUTE IMMEDIATE 'CREATE TABLE Schema.'||V_TABLE_NAME||'_'||DATE_VALUE_INS||' AS SELECT * FROM DW.'||V_TABLE_NAME where some condition;

But the problem becomes all 6 have different column to filter on.

My Ask is How should I change my design of proc to Adjust this new Requirement.

Answer 1

6 tables? Why bother? Create a procedure which - depending on table name passed as a parameter - in IF-THEN-ELSE runs 6 different CREATE TABLE statements.

On the other hand, another approach would be to create backup tables in advance (at SQL level), add BACKUP_DATE column to each of them, and - in procedure - just perform INSERT operation which doesn't require dynamic SQL at all.

For example:

create table emp_backup as select * from emp where 1 = 2;
alter table emp_backup add backup_date date;

create or replace procedure p_backup (par_table_name in varchar2) is
begin
  if par_table_name = 'EMP' then
     insert into emp_backup (empno, ename, job, sal, backup_date)
     select empno, ename, job, sal, trunc(sysdate)
     from emp 
     where deptno = 20;       --> here's your WHERE condition
  elsif par_table_name = 'DEPT' then
     insert into dept_backup (...)
     select ..., trunc(sysdate)
     from dept
     where loc = 'DALLAS';
  elsif ...
     ...
  end if;
end;
/

Doing so, you'd easier access backup data as you'd query only one table, filtered by BACKUP_DATE. That's also good if you have to search for some data that changed several days ago, but you don't know exact day. What would you rather do: query 10 tables (and still not find what you're looking for), or query just one table and find that info immediately?