Re: PL/SQL: How to check if a table exists or not?

From: Marc Billiet <Marc.Billiet_at_alcatel.be>
Date: 1997/08/28
Message-ID: <3405519B.7BA6@alcatel.be>#1/1

Michael A. Casillas wrote:
>
> Yet another one:
>
> How can I check in PL/SQL if a table exists or not. Is there a built in
> function that can return TRUE or FALSE? I know the table name, I just
> want to know if it's there or if I have to create it. Thanks in
> advance.
>
> Michael Casillas

There are two ways : you can check user_tables as specified in the other replies, or you can use the exception handling :

DECLARE
  NoTable Exception;
  Pragma Exception_Init(NoTable,-942);
BEGIN
  select *
  from Some_Table
  where ...;
EXCEPTION
  when NoTable then
    raise_application_error(-20000, 'Hey, Some_Table doesn''t exist'); END; ORA-00942 is the Oracle error number for 'Table or view does not exist'. This error message will also occur if the user can't access the table.

Marc Received on Thu Aug 28 1997 - 00:00:00 CDT