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

From: Dan Clamage <dclamage_at_idcomm.com>
Date: 1997/09/06
Message-ID: <01bcba72$d843eea0$ad240dd0@djchome>#1/1

One problem with using USER_TABLES is that the table might not be in your schema and that you don't have any kind of privileges on it. If you need to test for the existence of a table in any schema, use DBA_TABLES or DBA_OBJECTS. A PL/SQL package can easily be written, using any of the methods the other folks have described, and compiled by the user SYS (see your DBA!), with EXECUTE privilege granted to PUBLIC.

Have your friendly DBA compile this as SYS:

/* Stored function to test for the existence of a table
** Given a table name, returns TRUE/FALSE/NULL
** Written by Daniel J. Clamage  dclamage_at_idcomm.com
** this software is Public Domain -- share freely!

*/
CREATE OR REPLACE FUNCTION
   table_exists(Ptable_name IN DBA_TABLES.table_name%TYPE) RETURN BOOLEAN IS

• use cursor so that we only do a single fetch (more efficient) CURSOR get_table(Ctable_name DBA_TABLES.table_name%TYPE) IS SELECT owner FROM DBA_TABLES WHERE table_name = Ctable_name; local_owner DBA_TABLES.owner%TYPE; -- need to fetch something foundit BOOLEAN; -- did we find an entry? BEGIN OPEN get_table(UPPER(Ptable_name)); -- convert name to upper case, execute FETCH get_table INTO local_owner; foundit := get_table%FOUND; -- cursor attribute tells us if entry was found CLOSE get_table; -- finished with cursor RETURN (foundit); -- TRUE/FALSE EXCEPTION -- an error occurred WHEN OTHERS THEN IF (get_table%ISOPEN) THEN CLOSE get_table; END IF; RETURN (NULL); -- unknown result END table_exists; / GRANT EXECUTE ON table_exists TO PUBLIC; CREATE PUBLIC SYNONYM table_exists FOR SYS.table_exists;

Note how in the exception handler we cleanup the cursor and also importantly, we have to return something -- otherwise at run-time we might get 'ORA-06503: PL/SQL: Function returned without value'. We don't care what error occurred, only that we can't determine whether the table exists.

Also, we make the SYS user's new function executable and more readily accessible by any public user. Then all any user has to do in a PL/SQL block is:

BEGIN
  IF (table_exists('my_table')) THEN -- exists in data dictionary     ...
  ELSE -- not in the data dictionary!
    ...

You can generalize this function to test for the existence of *any* database object -- index, sequence, view, etc. -- by selecting from DBA_OBJECTS instead. Just supply the object_name and object_type, and optionally the object's owner if you need to be that specific.

-Dan Clamage dclamage_at_idcomm.com

> > 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
> >
> >

> Received on Sat Sep 06 1997 - 00:00:00 CDT