How to reuse code in Oracle Script? 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&gt;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&gt;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?
You can always nest blocks in PL/SQL

DECLARE
  &lt;&lt;define your other local variables&gt;&gt;

  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' );

  &lt;&lt;more code here&gt;&gt;
END;