Re: Test for the exsistance of a table

From: Mark D Powell <mark.powell_at_eds.com>
Date: 8 Mar 2002 11:58:58 -0800
Message-ID: <178d2795.0203081158.3c4f13b5@posting.google.com>

"Philip Morrow" <cracker_at_mymorrow.com> wrote in message news:<3c5i8.78860$TV4.13070960_at_typhoon.tampabay.rr.com>...
> Is there a way to test for the exsistance of a table with SQL code. I need
> to test to see if a table exsists and if so drop it. If it doesn't the drop
> table statement doesn't need to be executed.
>
> Thanks for the help.
>
> Phil

Phil, Oracle comes with something referred to in the documentation as the rdbms data dictionary tables. If an object exists and you have privilege to it you can see it in the all_xxx views.

Example:

UT1> set echo on
UT1> set serveroutput on
UT1> declare
  2  --
  3  v_return      boolean ;

  4 --
  5 function test_tbl_exists(
  6 p_owner in all_tables.owner%type   7 ,p_table_name in all_tables.table_name%type   8 ) return BOOLEAN is
  9 --
 10 v_hold varchar2(1) ;
 11 --
 12 begin
 13 --
 14 select 'X'
 15 into v_hold
 16 from sys.all_tables
 17 where table_name = upper(p_table_name)  18 and owner = upper(p_owner);  19 --
 20 return TRUE ;
 21 --
 22 exception
 23 when no_data_found then
 24 return FALSE ;
 25 end test_tbl_exists ;
 26 --
 27 begin
 28 --
 29 v_return := test_tbl_exists('MPOWEL01','EMP') ;  30 if v_return

 31     then dbms_output.put_line('Found EMP ')        ;
 32     else dbms_output.put_line('Did not find EMP ') ;
 33  end if ;
 34  v_return := test_tbl_exists('MPOWEL01','XXX')        ;
 35  if v_return
 36     then dbms_output.put_line('Found XXX ')        ;
 37     else dbms_output.put_line('Did not find XXX ') ;

 38 end if ;
 39 end ;
 40 /
Found EMP
Did not find XXX

PL/SQL procedure successfully completed.

HTH
-- Mark D Powell -- Received on Fri Mar 08 2002 - 13:58:58 CST