How to reuse code in Oracle Script?

Question

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?

• Raj: Rather than dropping and re-creating everytime, create once (or when table structure changes), truncate and reload data. or that is not an option?

Answer 1

You can always nest blocks in PL/SQL

DECLARE
 <<define your other local variables>>
 PROCEDURE drop_if_exists( p_table_name IN VARCHAR2 )
 AS
 l_num_tables pls_integer;
 BEGIN
 SELECT COUNT(*)
 INTO l_num_tables
 FROM user_tables
 WHERE table_name = p_table_name;
 IF( l_num_tables = 1 )
 THEN
 EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name;
 END IF;
 END drop_if_exists;
BEGIN
 drop_if_exists( 'MY_TEST_TABLE' );
 drop_if_exists( 'MY_TEST_TABLE' );
 <<more code here>>
END;

• Dzyann: I had to change the where clause to: WHERE table_name = UPPER(p_table_name); Because sometimes they could send the table name in lower case and in the user_tables the table names seem to be saved in Upper Case

Answer 2

you could put your table into a temporary table or a table of varchar2. ex:

Instantiation :

create global temporary table gpt_name(table_name varchar2(64)) on commit preserve rows;
insert into gpt_name values('MY_TEST_TABLE');
insert into gpt_name values('MY_TEST_TABLE2');

..

code:

declare
 tableExists number;
begin
for line in (select * from gpt_name )
loop
SELECT COUNT(*)
 INTO tableExists
FROM user_tables
WHERE table_name =line.table_name;
if tableExists>0 then
 execute immediate 'drop table '||line.table_name;
end if;
end loop;
end;