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!

drop multiple tables even if some don't exist

User_2SRRNJun 28 2022

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?

This post has been answered by Stax on Jun 28 2022
Jump to Answer
Comments
Post Details
Added on Jun 28 2022
3 comments
388 views