5 Replies Latest reply: Apr 11, 2011 2:01 PM by 551883 RSS

    How can I check if there is a table with the given name ?

    360381
      I'm using 8i and 9i.
      Could anybody teach me how to check if there is a table with a certain name in SQLPLUS without using SELECT
      statement ?
      I want to use the following logical procedure.

      if exists "table_to_be_deleted" then drop table "table_to_be_deleted"

      Or, is there any system table that I can check the names of tables like
      count = select "table_to_be_deleted" from "an_oracle_system_table"
      if ( count != 0 ) then drop table "table_to_be_deleted"

      Thanks,

      John
        • 1. re:How can I check if there is a table with the given name ?
          375452
          select owner||'.'||table_name
          from dba_tables
          where table_name = 'table_to_look_for'
          and owner = 'owner_of_this_table'

          you can also replace

          select owner||'.'||table_name

          by

          select count(1)

          if you have not the proper rights try the user_tables or all_tables
          • 2. re:How can I check if there is a table with the given name ?
            41967
            If you're using PL/SQL, then another alternative is simply not to check. Issue the drop command within it's own pl/sql block and include an exception handler to ignore the error if the table isn't there.

            eg:

            begin
            drop table x;
            exception
            when others then
            null;
            end;

            Cheers
            • 3. Re: re:How can I check if there is a table with the given name ?
              449801
              How would you implement this inside of a sql*plus script?
              • 4. Re: re:How can I check if there is a table with the given name ?
                Paul M.
                Just create it, but be aware that DDLs can't be executed directly from Pl/Sql, so you'll have to use dynamic Sql, e.g. :
                SQL> create table test(a number);

                Table created.

                SQL> begin
                  2  drop table test;
                  3  exception
                  4  when others then
                  5  null;
                  6  end;
                SQL> /
                drop table test;
                *
                ERROR at line 2:
                ORA-06550: line 2, column 1:
                PLS-00103: Encountered the symbol "DROP" when expecting one of the following:
                begin case declare exit for goto if loop mod null pragma
                raise return select update while with <an identifier>
                <a double-quoted delimited-identifier> <a bind variable> <<
                close current delete fetch lock insert open rollback
                savepoint set sql execute commit forall merge pipe


                SQL> ed
                Wrote file afiedt.buf

                  1  begin
                  2  execute immediate 'drop table test';
                  3  exception
                  4  when others then
                  5  null;
                  6* end;
                SQL> /

                PL/SQL procedure successfully completed.

                SQL>
                Now the table does not exist anymore, but further executions will not generate errors, due to the exception handler :
                SQL> desc test
                ERROR:
                ORA-04043: object test does not exist


                SQL> /

                PL/SQL procedure successfully completed.

                SQL>
                • 5. Re: How can I check if there is a table with the given name ?
                  551883
                  Use the following procedure before you create table.

                  create or replace PROCEDURE ut_p_drop_obj (
                  obj_name VARCHAR )
                  IS
                  l_obj_type all_objects.object_type%type;
                  l_str VARCHAR2 ( 255 ) ;
                  BEGIN
                  SELECT
                  object_type
                  INTO
                  l_obj_type
                  FROM
                  all_objects
                  WHERE
                  object_name = upper ( obj_name ) ;
                  l_str := 'DROP '|| l_obj_type||' '||obj_name;
                  IF l_obj_type = 'TABLE' THEN
                  l_str := l_str||' cascade constraints';
                  END IF;
                  EXECUTE immediate l_str ;
                  EXCEPTION
                  WHEN no_data_found THEN
                  NULL;
                  END;

                  example:

                  exec ut_p_drop_obj('NewTable');
                  /
                  create table NewTable ( col1 number );


                  Good luck