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

From: Brent D Robinson <ROBBY_at_prodigy.net>
Date: 1997/09/14
Message-ID: <01bcc163$940f8760$a9ccedcc@default>#1/1

Sandra Jones <sfjones_at_bellsouth.net> wrote in article <3415F817.7CDF_at_bellsouth.net>...
>
> greg teets wrote:
> >
> > On Wed, 27 Aug 1997 15:43:33 -0300, "Michael A. Casillas"
> > <casillas_at_icepr.com> wrote:
> >
> > You can select against USER_TABLES
> >

As far as I know, USER_TABLES would only show the tables which are own by the same user who is currently connected, so this may not work for systems where multiple user id's are used to connect and access tables owned by another user id.

But if you are using PL/SQL you can do a SELECT statement on the table (e.g. SELECT 'z' from TABLE1) and then catch the exception. If SQLCODE= -904 in the exception (the code for "TABLE DOES NOT EXIST") then you can take appropriate action.

BEGIN
   SELECT 'x'
   into x
   from table1
   where rownum = 1;

...
...
...

EXCEPTION
   when others then

      if SQLCODE = -904 then
	-- do whatever action here
      end if;

END;
> If you know the name of the table, why not just you
> use
> describe table_name;
>
> if the table exist it will be described
>

The person was asking about detecting the existence of the table in PL/SQL, the "describe" is for SQL PLUS. Received on Sun Sep 14 1997 - 00:00:00 CDT