Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Store Exception errors in LOOP and still continue operation

The_Cute_DBASep 25 2021 — edited Sep 25 2021

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;
This post has been answered by Frank Kulash on Sep 25 2021
Jump to Answer

Comments

Post Details

Added on Sep 25 2021
4 comments
541 views