Experts,
I am learning PL/SQL, and I would like to know how to complete this concept below.
I slightly changed the details but the concept is like this:
We have TABLE01 that is being populated by an application job. This contains all table names for a certain schema. TABLE02 contains table names that are candidate for purging only if they are empty. If while dropping the table, error is encountered, we will capture the error and store the error message in the log_table. If the table is not empty, we will just input entry in the log table that this table is not empty and cannot be dropped.
How can I complete this code...
create table TABLE01 (
tabname varchar2(30)
)
insert into TABLE01 values ('TAB01');
insert into TABLE01 values ('TAB02');
insert into TABLE01 values ('TAB03');
insert into TABLE01 values ('TAB04');
insert into TABLE01 values ('TAB05');
create table TABLE02(
tabname varchar2(3))
)
insert into TABLE01 values ('TAB01');
insert into TABLE01 values ('TAB03');
insert into TABLE01 values ('TAB05');
create table LOG_TABLE (
USERNAME varchar2(30),
table_name varchar2(30),
drop_date date,
row_count number,
error_msg varchar2(1024)
);
NOTES:
In TABLE02 TAB01, TAB03 and TAB05 can only be purged if they are empty
If they are not empty, we will store details on the log_table as shown in the comments below
If dropping encountered error say table is curently in use or there are constraints, or any errors, we will just store the error details and continue with the LOOP.
DECLARE
tbl_data NUMBER := 0;
row_count NUMBER := 0;
sql_01 VARCHAR2(1024) := '';
BEGIN
FOR tab_list IN (select tabname from TABLE02 where tabname in (select tabname from TABLE01)
LOOP
sql_01 := ' select count(*) from '||tab_list.tabname ';
EXECUTE IMMEDIATE sql_01
INTO row_count;
IF row_count = 0 THEN
execute immediate 'drop table '||tab_list.tabname||' purge ';
--if error is encountered while dropping table:
--capture the error and store in LOG_TABLE
--continue with the loop
execute immediate 'commit';
ELSIF row_count > 0 THEN
--store table info in LOG_TABLE with "error_msg" having literal value "Table not empty".
execute immediate 'commit';
ELSE
NULL;
END IF;
END LOOP;
END;