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

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.

If you want to know if the table exists IN THE CURRENT USER'S SCHEMA: SELECT
  DECODE(count(*),0,'F','T')
FROM
  user_tables
WHERE
  table_name = 'THE_TABLE_I_NEED';

If you don't care about who owns the table - just ANY table in the database with the name (user must have select privs on the dictionary tables):

SELECT
  DECODE(count(*),0,'F','T')
FROM
  sys.dba_tables
WHERE
  table_name = 'THE_TABLE_I_NEED';

Or the all-around solution where you know the owner explicitly:

SELECT
  DECODE(count(*),0,'F','T')
FROM
  sys.dba_tables
WHERE
  owner = 'OWNER_NAME'
  AND table_name = 'THE_TABLE_I_NEED';

• TRW -- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| | Tim Witort || He is not silent. He is not | | trwATmedicalertDOTorg || whispering. We are not quiet | | || we are not listening. | | Pin: TZ, only one :^( || - Out of the Grey | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||