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

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message news:b75vsvkhgqodojbvkifd245daqdqidj1c5_at_4ax.com...
| On Thu, 4 Dec 2003 14:58:58 +0100, "NoName" <nobody_at_nowhere.com>
| wrote:
|
| >Thanks to everybody.
| >Using your suggestions, I produced the following code:
| >
| >declare
| > c numeric;
| >begin
| > select count(*) into c from user_tables where table_name = 'TABLEXY';
| > if c > 0 THEN
| > -- statements to do
| > end if;
| >end;
| >
| >Hope this could help other newbies :-)
| >Regards
| >
|
| They would better trust appropiate exception handling and not trust
| this horrible approach.
|
|
| --
| Sybrand Bakker, Senior Oracle DBA

I assume that you're using dynamic SQL, otherwise the procedure would not compile, if a referenced table (or other object) does not exist (whether it's an anonymous block or a stored procedure).

With that assumption, Sybrand's suggestion would look something like this:

create or replace function count_rows( ip_table in varchar2 ) return number
is
  n_count number;
begin
  execute immediate 'select count(*) from ' || ip_table into n_count;   return n_count;
exception
when others
then

    if sqlcode = -942 -- the code for 'table or view does not exist'     then return null;-- or other appropriate error handling     else raise; -- to re-exert any other exception types     end if;
end count_rows;
/

More code, but avoids a data dictionary view access (which would be superfluous for the hopefully majority of the cases when the table actually exists)

Also, more correct (more correcter?) since the object may not actually be a table, and may not be owned by the user -- i.e., a synonym or a view would not be listed in USER_TABLES.

-- 
Mark C. Stock
mcstock -> enquery(dot)com
www.enquery.com training & consulting