Oracle, drop table if it exists AND empty

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.

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;

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.

here is an easy way to solve this problem:

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