I have a script where I am creating some test data. At the beginning of the script I create some tables where I have to insert the data. Before creating them I check that they do not exist.
declare tableExists number; begin SELECT COUNT(*) INTO tableExists FROM user_tables WHERE table_name = 'MY_TEST_TABLE'; if tableExists>0 then execute immediate 'drop table MY_TEST_TABLE'; end if; SELECT COUNT(*) INTO tableExists FROM user_tables WHERE table_name = 'MY_TEST_TABLE2'; if tableExists>0 then execute immediate 'drop table MY_TEST_TABLE2'; end if; --Here I create both tables: MY_TEST_TABLE and MY_TEST_TABLE2
My question is: Is there a way to avoid repeating the code for checking if the table exists? Is there a way that I could call that code as a method passing the table name as parameter?
I believe I could create/declare a function and then use that. But that kind of function is not temporal. I know this is not like C or C++. But I was wondering if there was a way I could have a sort of "private method" for this script to test the existence of my tables and that way I wouldn't have to repeat the code, but the method wouldn't remain after my script was finished.
What is the correct way, if there is any, to achieve what I want?