PL SQL Procedure using dynamic sql to take backup of a random table in a schema supplied during exec-CodePudding

I would like to create a procedure that:

Creates a backup of a table when supplied the table name and the owner when the procedure is executed.
Does a count of the records of the backup table
Insert a row (owner, table_name and sysdate) into a separate table called backup_mgnt

Please see below for the code i have so far:

CREATE OR REPLACE PROCEDURE OPSADMIN.CREATE_BACKUP_TABLE as
/*
*******************************************************************************************************
Object Name:  CREATE_BACKUP_TABLE



 
*/
-- Private variables
icnt        number;
sqlcmd      varchar2(1000);
backup_dt  varchar2(20) := trunc(sysdate);   
tbl_success number := 0;
tbl_error   number := 0;
tbl_process number := 0;
pbegin_time varchar2(25) := to_char(sysdate,'dd-MON-yyyy hh:mi:ss AM');
pend_time   varchar2(25);
tbegin_time varchar2(25);
tend_time   varchar2(25);
l_affx      varchar2(30) := 'BKP';
 
--
--
begin
--
dbms_output.put_line('******************* OPSADMIN.CREATE_BACKUP_TABLE *****************');
dbms_output.put_line('--');
dbms_output.put_line('--');
--
for tn in (
select table_name, owner
  from all_tables
 where table_name = &1
   and owner = &2
)
LOOP
    BEGIN
    -- Create output table header
    tbegin_time := to_char(sysdate,'dd-MON-yyyy hh:mi:ss AM');
    tbl_process := tbl_process   1;
    dbms_output.put_line('Creating backup table: '||tn.owner||'.'||tn.table_name||'');
    --
    -- Build sql statement to insert into the BACKUP table from the SOURCE table
    sqlcmd := 'create table '||tn.owner||'.'||tn.table_name||'_'||l_month||'_'||l_affx||' AS SELECT * FROM '||tn.owner||'.'||tn.table_name||'’;
    dbms_output.put_line(sqlcmd);
    execute immediate sqlcmd;
    --
    -- Note count of records inserted into icnt
    icnt := SQL%ROWCOUNT;
    dbms_output.put_line('Records inserted into '||tn.owner||'.'||tn.table_name||'_'|| backup_dt||'_'||l_affx||': '||icnt);
    --
    -- Build sql statement to insert record into the BACKUP_MGMT table
    sqlcmd := 'insert into OPSADMIN.BACKUP_MGMT '||tn.owner||'.'||tn.table_name||','||sysdate||'';
    dbms_output.put_line(sqlcmd);
    execute immediate sqlcmd;
    --       
    END;
    dbms_output.put_line('-- Table Start Time:     '||tbegin_time);
    dbms_output.put_line('-- Table Completed Time: '||tend_time);
    dbms_output.put_line('--');
    dbms_output.put_line('--');
END LOOP;
    -- Output process result overview
    pend_time := to_char(sysdate,'dd-MON-yyyy hh:mi:ss AM');
    dbms_output.put_line('******************************************************************');
    dbms_output.put_line('* '||rpad('Process create_backup_table is completed!', 63)||'*');
    dbms_output.put_line('* '||rpad('- Tables Processed . . . . . . . . . . . . . . . . '||tbl_process, 63)||'*');
    dbms_output.put_line('* '||rpad('- Process Start Time:     '||pbegin_time, 63)||'*');
    dbms_output.put_line('* '||rpad('- Process Completed Time: '||pend_time, 63)||'*');
    dbms_output.put_line('******************************************************************');
end;
/

CodePudding user response：

here it is

CREATE OR REPLACE PROCEDURE CREATE_BACKUP_TABLE (p1 varchar2, p2 varchar2) as
/*
*******************************************************************************************************
Object Name:  CREATE_BACKUP_TABLE



 
*/
-- Private variables
icnt        number;
sqlcmd      varchar2(1000);
backup_dt  varchar2(20) := trunc(sysdate);   
tbl_success number := 0;
tbl_error   number := 0;
tbl_process number := 0;
pbegin_time varchar2(25) := to_char(sysdate,'dd-MON-yyyy hh:mi:ss AM');
pend_time   varchar2(25);
tbegin_time varchar2(25);
tend_time   varchar2(25);
l_affx      varchar2(30) := 'BKP';
l_month     varchar2(2):=to_char(sysdate,'mm');
 
--
--
begin
--
dbms_output.put_line('******************* OPSADMIN.CREATE_BACKUP_TABLE *****************');
dbms_output.put_line('--');
dbms_output.put_line('--');
--
for tn in (
select table_name, owner
  from all_tables
 where table_name = p1
   and owner = p2
)
LOOP
    BEGIN
    -- Create output table header
    tbegin_time := to_char(sysdate,'dd-MON-yyyy hh:mi:ss AM');
    tbl_process := tbl_process   1;
    dbms_output.put_line('Creating backup table: '||tn.owner||'.'||tn.table_name);
    --
    -- Build sql statement to insert into the BACKUP table from the SOURCE table
    sqlcmd := 'create table '||tn.owner||'.'||tn.table_name||'_'||l_month||'_'||l_affx||' AS SELECT * FROM '||tn.owner||'.'||tn.table_name;
    dbms_output.put_line(sqlcmd);
    execute immediate sqlcmd;
    --
    -- Note count of records inserted into icnt
    icnt := SQL%ROWCOUNT;
    dbms_output.put_line('Records inserted into '||tn.owner||'.'||tn.table_name||'_'|| backup_dt||'_'||l_affx||': '||icnt);
    --
    -- Build sql statement to insert record into the BACKUP_MGMT table
    -- you can use normal sql here, no need for dynamic
    sqlcmd := 'insert into OPSADMIN.BACKUP_MGMT values('||tn.owner||'.'||tn.table_name||',sysdate)';
    dbms_output.put_line(sqlcmd);
    execute immediate sqlcmd;
    --       
    END;
    dbms_output.put_line('-- Table Start Time:     '||tbegin_time);
    dbms_output.put_line('-- Table Completed Time: '||tend_time);
    dbms_output.put_line('--');
    dbms_output.put_line('--');
END LOOP;
    -- Output process result overview
    pend_time := to_char(sysdate,'dd-MON-yyyy hh:mi:ss AM');
    dbms_output.put_line('******************************************************************');
    dbms_output.put_line('* '||rpad('Process create_backup_table is completed!', 63)||'*');
    dbms_output.put_line('* '||rpad('- Tables Processed . . . . . . . . . . . . . . . . '||tbl_process, 63)||'*');
    dbms_output.put_line('* '||rpad('- Process Start Time:     '||pbegin_time, 63)||'*');
    dbms_output.put_line('* '||rpad('- Process Completed Time: '||pend_time, 63)||'*');
    dbms_output.put_line('******************************************************************');
 

   end;
/

And to run it:

begin
create_backup_table('&p1','&p2);
end;
/

Please note that this is should be amended to check if the table you want to create exists, and if it's name is longer than the allowed object name (30 char in 12c and previous, 128 in 19c) and so on...