Re: PLSQL: how to test if a table exists?

deanbrown3d wrote:
> if (select table_name from all_tables where owner = 'me' and table_name
> = 'MYTABLE') is null........

This seems like a very common query to perform.

I would probably wrap it up in a function, and place it in my own package for later use:

CREATE OR REPLACE FUNCTION
          tableExists( O VARCHAR2, T VARCHAR2 ) RETURN VARCHAR2 AS

v_TableName all_tables.table_name%type;

BEGIN   SELECT table_name INTO v_TableName
    FROM all_tables
    WHERE owner = UPPER( O )
    AND table_name = UPPER( T );

  RETURN 'Table ' || '"' || T || '"' || ' Exists';

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN 'Table ' || '"' || T || '"' || ' Does Not Exist';     WHEN OTHERS THEN
      RETURN 'Unknown error occurred';

END tableExists;
/

Of course, you could return a boolean type or even a char(1) to signify 'T', 'F' or 'U' if you like.

Cheers,
Chris Val Received on Sun Jun 26 2005 - 18:01:12 CDT