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

"mcstock" <mcstock @ enquery .com> wrote i

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

The SELECT COUNT(*) just like that is IMO a bad idea performance wise.

If you do want to do it like this, use the the FIRST_ROWS hint and add a ROWNUM=1 stop criteria.

After all, all you want it to see if there's an exception and not the actual rowcount.

--
Billy