Oracle Live SQL - Script: Session 01

Statement 1

DECLARE 
--select all table names from backup tables (ex: BCK_tablename) 
CURSOR cur_temp_tbl IS 
    SELECT table_name 
    FROM all_tables  
    WHERE OWNER = 'BCKUP' 
    ORDER BY 1;   
 
--select all table names from original tables (ex: ORIG_tablename) 
 CURSOR cur_original_tbl IS 
    SELECT table_name 
    FROM all_tables  
    WHERE OWNER = 'ORIG' 
    ORDER BY 1;     
    l_tbl_nm VARCHAR2(30 CHAR); 
 
BEGIN 
    --first loop to delete all tables from backup 
    FOR a IN cur_temp_tbl LOOP 
       l_tbl_nm := a.table_name;                                     
       EXECUTE IMMEDIATE 'DELETE FROM '||  l_tbl_nm; 
       l_deleted_cnt :=  l_deleted_cnt +1;             
    END LOOP; 
 
    --second loop to insert data from original to backup         
    FOR b IN cur_original_tbl LOOP             
         l_tbl_nm := b.table_name;    
        CASE 
          WHEN INSTR(l_tbl_nm,'ORIG_') > 0 THEN 
          l_tbl_nm := REPLACE(l_tbl_nm,'ORIG_','BCK_'); 
          ELSE 
           l_tbl_nm := 'BCK_' || l_tbl_nm; 
        END CASE;   
 
        EXECUTE IMMEDIATE 'INSERT INTO '  || l_tbl_nm || ' SELECT * FROM ' || b.table_name; 
        l_inserted_cnt :=  l_inserted_cnt +1; 
    END LOOP;  
 
    dbms_output.put_line('Deleted/truncated tables from backup :' ||l_deleted_cnt); 
    dbms_output.put_line('No of tables inserted with data from original to backup :' ||l_inserted_cnt); 
EXCEPTION 
 WHEN OTHERS THEN 
 dbms_output.put_line(SQLERRM); 
 dbms_output.put_line(l_tbl_nm); 
END;

ORA-06550: line 22, column 8:
PLS-00201: identifier 'L_DELETED_CNT' must be declared

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 2

DECLARE 
--select all table names from backup tables (ex: BCK_tablename) 
CURSOR cur_temp_tbl IS 
    SELECT table_name 
    FROM all_tables  
    WHERE OWNER = 'BCKUP' 
    ORDER BY 1;   
 
--select all table names from original tables (ex: ORIG_tablename) 
 CURSOR cur_original_tbl IS 
    SELECT table_name 
    FROM all_tables  
    WHERE OWNER = 'ORIG' 
    ORDER BY 1;     
    l_tbl_nm VARCHAR2(30 CHAR); 
l_inserted_cnt number(5) :=0; 
l_deleted_cnt number(5) :=0; 
BEGIN 
    --first loop to delete all tables from backup 
    FOR a IN cur_temp_tbl LOOP 
       l_tbl_nm := a.table_name;                                     
       EXECUTE IMMEDIATE 'DELETE FROM '||  l_tbl_nm; 
       l_deleted_cnt :=  l_deleted_cnt +1;             
    END LOOP; 
 
    --second loop to insert data from original to backup         
    FOR b IN cur_original_tbl LOOP             
         l_tbl_nm := b.table_name;    
        CASE 
          WHEN INSTR(l_tbl_nm,'ORIG_') > 0 THEN 
          l_tbl_nm := REPLACE(l_tbl_nm,'ORIG_','BCK_'); 
          ELSE 
           l_tbl_nm := 'BCK_' || l_tbl_nm; 
        END CASE;   
 
        EXECUTE IMMEDIATE 'INSERT INTO '  || l_tbl_nm || ' SELECT * FROM ' || b.table_name; 
        l_inserted_cnt :=  l_inserted_cnt +1; 
    END LOOP;  
 
    dbms_output.put_line('Deleted/truncated tables from backup :' ||l_deleted_cnt); 
    dbms_output.put_line('No of tables inserted with data from original to backup :' ||l_inserted_cnt); 
EXCEPTION 
 WHEN OTHERS THEN 
 dbms_output.put_line(SQLERRM); 
 dbms_output.put_line(l_tbl_nm); 
END;

Deleted/truncated tables from backup :0
No of tables inserted with data from original to backup :0

Statement 3

create table orig_address (emp_id varchar2(5),location varchar2(500), country varchar2(250))

Table created.

Statement 4

insert into orig_address values('001','California','US')

1 row(s) inserted.

Statement 5

select * from orig_address


EMP_ID LOCATION COUNTRY
001 California US

EMP_ID	LOCATION	COUNTRY
001	California	US

Statement 6

create table orig_address (emp_id varchar2(5),location varchar2(500), country varchar2(250))

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 7

insert into orig_address values('001','California','US')

1 row(s) inserted.

Statement 8

create table orig_employees (emp_id varchar2(5),emp_name varchar2(10), emp_age number(5))

Table created.

Statement 9

insert into orig_employees values('001','Bill',20)

1 row(s) inserted.

Statement 10

insert into orig_address values('001','California','US')

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 11

select * from orig_employees


EMP_ID EMP_NAME EMP_AGE
001 Bill 20

EMP_ID	EMP_NAME	EMP_AGE
001	Bill	20

Statement 12

create table orig_address (emp_id varchar2(5),location varchar2(200), country varchar2(150))

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 13

delete from orig_address

2 row(s) deleted.

Statement 14

select * from orig_address


EMP_ID LOCATION COUNTRY
001 California US

EMP_ID	LOCATION	COUNTRY
001	California	US

Statement 15

select * from orig_address


EMP_ID LOCATION COUNTRY
001 California US
001 California US

2 rows selected.

EMP_ID	LOCATION	COUNTRY
001	California	US
001	California	US

Statement 16

insert into orig_address values('001','California','US')

1 row(s) inserted.

Statement 17

select * from bck__employees

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 18

DECLARE 
--select all table names from backup tables (ex: BCK_tablename) 
CURSOR cur_temp_tbl IS 
    SELECT table_name 
    FROM all_tables  
    WHERE table_name like 'BCK_%' 
    ORDER BY 1;   
 
--select all table names from original tables (ex: ORIG_tablename) 
 CURSOR cur_original_tbl IS 
    SELECT table_name 
    FROM all_tables  
    WHERE table_name like 'ORIG_%' 
    ORDER BY 1;     
    l_tbl_nm VARCHAR2(30 CHAR); 
 
BEGIN 
    --first loop to delete all tables from backup 
    FOR a IN cur_temp_tbl LOOP 
       l_tbl_nm := a.table_name;                                     
       EXECUTE IMMEDIATE 'DELETE FROM '||  l_tbl_nm; 
       l_deleted_cnt :=  l_deleted_cnt +1;             
    END LOOP; 
 
    --second loop to insert data from original to backup         
    FOR b IN cur_original_tbl LOOP             
         l_tbl_nm := b.table_name;    
        CASE 
          WHEN INSTR(l_tbl_nm,'ORIG_') > 0 THEN 
          l_tbl_nm := REPLACE(l_tbl_nm,'ORIG_','BCK_'); 
          ELSE 
           l_tbl_nm := 'BCK_' || l_tbl_nm; 
        END CASE;   
 
        EXECUTE IMMEDIATE 'INSERT INTO '  || l_tbl_nm || ' SELECT * FROM ' || b.table_name; 
        l_inserted_cnt :=  l_inserted_cnt +1; 
    END LOOP;  
 
    dbms_output.put_line('Deleted/truncated tables from backup :' ||l_deleted_cnt); 
    dbms_output.put_line('No of tables inserted with data from original to backup :' ||l_inserted_cnt); 
EXCEPTION 
 WHEN OTHERS THEN 
 dbms_output.put_line(SQLERRM); 
 dbms_output.put_line(l_tbl_nm); 
END;

ORA-06550: line 22, column 8:
PLS-00201: identifier 'L_DELETED_CNT' must be declared

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 19

DECLARE 
--select all table names from backup tables (ex: BCK_tablename) 
CURSOR cur_temp_tbl IS 
    SELECT table_name 
    FROM all_tables  
    WHERE table_name like 'BCK_%' 
    ORDER BY 1;   
 
--select all table names from original tables (ex: ORIG_tablename) 
 CURSOR cur_original_tbl IS 
    SELECT table_name 
    FROM all_tables  
    WHERE table_name like 'ORIG_%' 
    ORDER BY 1;     
    l_tbl_nm VARCHAR2(30 CHAR); 
 
BEGIN 
    --first loop to delete all tables from backup 
    FOR a IN cur_temp_tbl LOOP 
       l_tbl_nm := a.table_name;                                     
       EXECUTE IMMEDIATE 'DELETE FROM '||  l_tbl_nm; 
       l_deleted_cnt :=  l_deleted_cnt +1;             
    END LOOP; 
 
    --second loop to insert data from original to backup         
    FOR b IN cur_original_tbl LOOP             
         l_tbl_nm := b.table_name;    
        CASE 
          WHEN INSTR(l_tbl_nm,'ORIG_') > 0 THEN 
          l_tbl_nm := REPLACE(l_tbl_nm,'ORIG_','BCK_'); 
          ELSE 
           l_tbl_nm := 'BCK_' || l_tbl_nm; 
        END CASE;   
 
        EXECUTE IMMEDIATE 'INSERT INTO '  || l_tbl_nm || ' SELECT * FROM ' || b.table_name; 
        l_inserted_cnt :=  l_inserted_cnt +1; 
    END LOOP;  
 
    dbms_output.put_line('Deleted/truncated tables from backup :' ||l_deleted_cnt); 
    dbms_output.put_line('No of tables inserted with data from original to backup :' ||l_inserted_cnt); 
EXCEPTION 
 WHEN OTHERS THEN 
 dbms_output.put_line(SQLERRM); 
 dbms_output.put_line(l_tbl_nm); 
END;

ORA-06550: line 22, column 8:
PLS-00201: identifier 'L_DELETED_CNT' must be declared

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 20

DECLARE 
--select all table names from backup tables (ex: BCK_tablename) 
CURSOR cur_temp_tbl IS 
    SELECT table_name 
    FROM all_tables  
    WHERE table_name like 'BCK_%' 
    ORDER BY 1;   
 
--select all table names from original tables (ex: ORIG_tablename) 
 CURSOR cur_original_tbl IS 
    SELECT table_name 
    FROM all_tables  
    WHERE table_name like 'ORIG_%' 
    ORDER BY 1;     
    l_tbl_nm VARCHAR2(30 CHAR); 
 
BEGIN 
    --first loop to delete all tables from backup 
    FOR a IN cur_temp_tbl LOOP 
       l_tbl_nm := a.table_name;                                     
       EXECUTE IMMEDIATE 'DELETE FROM '||  l_tbl_nm; 
       l_deleted_cnt :=  l_deleted_cnt +1;             
    END LOOP; 
 
    --second loop to insert data from original to backup         
    FOR b IN cur_original_tbl LOOP             
         l_tbl_nm := b.table_name;    
        CASE 
          WHEN INSTR(l_tbl_nm,'ORIG_') > 0 THEN 
          l_tbl_nm := REPLACE(l_tbl_nm,'ORIG_','BCK_'); 
          ELSE 
           l_tbl_nm := 'BCK_' || l_tbl_nm; 
        END CASE;   
 
        EXECUTE IMMEDIATE 'INSERT INTO '  || l_tbl_nm || ' SELECT * FROM ' || b.table_name; 
        l_inserted_cnt :=  l_inserted_cnt +1; 
    END LOOP;  
 
    dbms_output.put_line('Deleted/truncated tables from backup :' ||l_deleted_cnt); 
    dbms_output.put_line('No of tables inserted with data from original to backup :' ||l_inserted_cnt); 
EXCEPTION 
 WHEN OTHERS THEN 
 dbms_output.put_line(SQLERRM); 
 dbms_output.put_line(l_tbl_nm); 
END;

ORA-06550: line 22, column 8:
PLS-00201: identifier 'L_DELETED_CNT' must be declared

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 21

commit

Statement processed.

Statement 22

select * from orig_address


EMP_ID LOCATION COUNTRY
001 California US

Session 01

Additional Information