This discussion is archived
5 Replies Latest reply: Apr 11, 2011 12:01 PM by 551883 RSS

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

360381 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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. Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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