CODEWITHSUNDEEP
oracle-databaseplsqlddl
Mike .........
Mike .........

Reputation: 21

Oracle, drop table if it exists AND empty

I need to drop an Oracle table only if it 1) exists AND 2) Is NOT Empty I wrote this code but if the table does not exist the code does not work:

DECLARE
  rec_cnt1 NUMBER :=0;
  rec_cnt2 NUMBER :=0;
BEGIN
  SELECT COUNT(*) INTO rec_cnt1 FROM ALL_TABLES  WHERE TABLE_NAME  = 'MyTable';
  SELECT num_rows INTO rec_cnt2 FROM USER_TABLES WHERE TABLE_NAME  = 'MyTable';
  IF rec_cnt1 = 1 THEN
  BEGIN
    IF rec_cnt2 < 1 THEN
      EXECUTE IMMEDIATE 'DROP TABLE MyTable cascade constraints';
    END IF;
  END;
  END IF;
END;
/

What am I doing wrong? Please help.

Many thanks in advance

Upvotes: 2

Views: 7499

Answers (4)

Nick Krasnov
Nick Krasnov

Reputation: 27251

If you want to drop a table if it exists and empty(as the title of the question states) you could do this as follows:

create or replace procedure DropTableIfEmpty(p_tab_name varchar2)
is
  l_tab_not_exists exception;
  pragma exception_init(l_tab_not_exists, -942);
  l_is_empty   number;
  l_query      varchar2(1000);
  l_table_name varchar2(32);
begin
  l_table_name := dbms_assert.simple_sql_name(p_tab_name);
  l_query := 'select count(*)
                from ' || l_table_name ||          
             ' where rownum = 1';
  execute immediate l_query
     into l_is_empty;

  if l_is_empty = 0
  then
    execute immediate 'drop table ' || l_table_name;
    dbms_output.put_line('Table "'|| p_tab_name ||'" has been dropped');
  else
    dbms_output.put_line('Table "'|| p_tab_name ||'" exists and is not empty');
  end if;

exception
  when l_tab_not_exists
  then dbms_output.put_line('Table "'|| p_tab_name ||'" does not exist');
end;

When you are trying to drop a table, or query a table, which does not exist, Oracle will raise ORA-00942 exception and execution of a pl/sql block halts. We use pragma exception_init statement to associate ORA-00942 exception with our locally defined exception l_tab_not_exists in order to handle it appropriately.

Test case:

SQL> exec droptableifempty('tb_test');  -- tb_test table does not exists

 Table "tb_test" does not exist

SQL> create table tb_test(
  2     col number
  3   );

table TB_TEST created.

SQL> exec droptableifempty('tb_test'); 

 Table "tb_test" has been dropped

As a side note. Before querying num_rows column of [dba][all][user]_tables in order to determine number of rows a table has, you need to gather table statistic by executing dbms_stats.gather_table_stats(user, '<<table_name>>');, otherwise you wont get the actual number of rows.

Upvotes: 4

yongfu
yongfu

Reputation: 1

here is an easy way to solve this problem:

BEGIN 
     EXECUTE IMMEDIATE 'DROP TABLE [sssss]'; 
     EXCEPTION WHEN OTHERS THEN NULL; 
END;

Upvotes: -2

EvilTeach
EvilTeach

Reputation: 28837

In PL/SQL it is 'normal' to catch the exception.

If it is the correct exception then continue with the next part of your code.

DECLARE
  rec_cnt1 NUMBER :=0;
  rec_cnt2 NUMBER :=0;
BEGIN
  SELECT COUNT(*) INTO rec_cnt1 FROM ALL_TABLES  WHERE TABLE_NAME  = 'MyTable';
  SELECT num_rows INTO rec_cnt2 FROM USER_TABLES WHERE TABLE_NAME  = 'MyTable';
  IF rec_cnt1 = 1 THEN
  BEGIN
    IF rec_cnt2 < 1 THEN
      EXECUTE IMMEDIATE 'DROP TABLE MyTable cascade constraints';
    END IF;
  END;
  END IF;
EXCEPTION
  DBMS_OUTPUT.PUT_LINE('OH DEAR AN EXCEPTION WAS THROWN DUE TO' || SQLERRM);
  DBMS_OUTPUT.PUT_LINE('THE ORACLE CODE IS ' || SQLCODE);
  -- if it is the oracle code for no such table, or no data selected
  -- everything is fine.

END;

Upvotes: 0

DCookie
DCookie

Reputation: 43523

Of course it won't work if the table doesn't exist. Your second select would get a "No data found" exception, and you're not doing any exception handling. At least you should move the second select inside the first IF block. Best to add exception handling.

Upvotes: -1

Related Questions