0

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?

  • Rather than dropping and re-creating everytime, create once (or when table structure changes), truncate and reload data. or that is not an option? – Raj May 28 '14 at 17:16
2

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;
  • 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 – Dzyann Jun 4 '14 at 15:02
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.