How to check object exists or not in oracle

Question

We can check whether object exist or not in T-SQL using following query.

IF OBJECT_ID('TBL_COMP_ASSESSMENT') IS NOT NULL
DROP TABLE TBL_COMP_ASSESSMENT

Is there any way to achieve this in Oracle?

Answer 1

It is a bad idea to DROP and CREATE tables on the fly. T-SQL and PL/SQL are very different. PL/SQL is compiled prior to execution and reside in database. What you do in T-SQL might not be good in PL/SQL. You could TRUNCATE the table rather than dropping it on the fly.

Anyway, if you really want to do it, then you need to (ab)use EXECUTE IMMEDIATE to do this.

For example,

SQL>  DECLARE
  2      cnt NUMBER;
  3  BEGIN
  4      SELECT Count(*)
  5      INTO   cnt
  6      FROM   user_tables
  7      WHERE  table_name = 'TBL_COMP_ASSESSMENT';
  8
  9      IF cnt = 1 THEN
 10        BEGIN
 11            EXECUTE IMMEDIATE 'DROP TABLE TBL_COMP_ASSESSMENT';
 12        EXCEPTION
 13            WHEN OTHERS THEN
 14              IF SQLCODE != -942 THEN
 15                RAISE;
 16              END IF;
 17        END;
 18      END IF;
 19  END;
 20
 21  /

PL/SQL procedure successfully completed.

SQL>

Answer 2

You can use ALL_OBJECTS table. Something like this should do:

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = '<OBJ_NAME_HERE>'

You can add more conditions as per your requirement:

AND OWNER='<OWNER>' AND OBJECT_TYPE='<TYPE>'

Full details here: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2005.htm

Answer 3

you can check it using

  begin    
  if ObjType = 'TABLE' then
    select count(*) into v_counter from user_tables where table_name = upper(ObjName);
    if v_counter > 0 then          
      execute immediate 'drop table ' || ObjName || ' cascade constraints';        
    end if;   
  end if;
   end;