Store Exception errors in LOOP and still continue operation

The_Cute_DBA

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;

Frank Kulash

HI, @The_Cute_DBA

What is the big picture here? Why are you dropping tables in PL/SQL? This sounds like a poor way to solve whatever business problem you have. What is the business problem? If you can say what you need to do, someone can help you find a good way to do it.

If, when a loop gets an error, you want to log the error and continue with the next iteration of the loop, then use a BEGEN ... EXCEPTION ... END block nested inside the loop, like this:

BEGIN	-- Main block
	...
	LOOP
		...
		BEGIN	-- Nested block
			...
		EXCEPTION
			... -- Log errors
		END;	-- Nested block
		...
	END LOOP;
	...
END;	-- Main block

It might be better to write a procedure that does whatever you do in the loop, and have that procedure's EXCEPTION section log the errors.

cormaco

There is no need for TABLE01. All tables in a schema can be found by

select table_name from USER_TABLES

Solomon Yakobson

Just wrap

execute immediate 'drop table '||tab_list.tabname||' purge ';

into begin/end block:

begin
    execute immediate 'drop table '||tab_list.tabname||' purge ';
  exception
    when others
      then
        -- capture the error and store in LOG_TABLE
        commit;
end;

And there is no need for dynamic commit. Plain static commit will do.

SY.

Frank Kulash

HI, @The_Cute_DBA

What is the big picture here? Why are you dropping tables in PL/SQL? This sounds like a poor way to solve whatever business problem you have. What is the business problem? If you can say what you need to do, someone can help you find a good way to do it.

If, when a loop gets an error, you want to log the error and continue with the next iteration of the loop, then use a BEGEN ... EXCEPTION ... END block nested inside the loop, like this:

BEGIN	-- Main block
	...
	LOOP
		...
		BEGIN	-- Nested block
			...
		EXCEPTION
			... -- Log errors
		END;	-- Nested block
		...
	END LOOP;
	...
END;	-- Main block

It might be better to write a procedure that does whatever you do in the loop, and have that procedure's EXCEPTION section log the errors.

EdStevens

What is the big picture here? Why are you dropping tables in PL/SQL? This sounds like a poor way to solve whatever business problem you have. What is the business problem? If you can say what you need to do, someone can help you find a good way to do it.

I'd like to upvote that. You beat me to it.

@The_Cute_DBA - please don't ignore Frank's comment. This may be in interesting PL/SQL exercise, in the details. But it is a very poor approach to the larger task. Any time you find yourself writing PL/SQL procedures to dynamically create/drop/modify objects, you need to step back and question the overall design of the system.