Hi,
Can I try to delete delete multiple (temporary) tables in a Stored Procedures, in one block?
CREATE OR REPLACE PROCEDURE myTest
IS
table_does_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);
BEGIN
-- wrap to continue after an error
begin
execute immediate 'drop table nonExsting';
execute immediate 'drop table testTab2';
exception
WHEN table_does_not_exist THEN DBMS_OUTPUT.put_line('drop table'); -- null
end;
DBMS_OUTPUT.put_line('add code here');
-- simulate some other error
execute immediate 'select * from NotExisting';
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.put_line('something else');
END;
/
Say, I have 10 temporary tables. I want to delete them at the beginning of the SP should they exist. Do I really have to create 10 BEGIN..EXCEPT-Blocks? My goal is to just process all execute immediate drops and have it drop a table if it's process - otherwise continue with the next and finally with the code after the block (saying add code code)
Is this possible?