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

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:1a75df45.0312042332.529272a8_at_posting.google.com...
| "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

thanks for the feedback billy -- it looks like i was not clear on what i was trying to illustrate with this example --

the 'select count(*)' statement was just placeholder to illustrate the actual SQL that normally needs to be run -- it was not intended as a code that would be used to test for table existence

let me offer a more generic, non-running version:

begin

• some code here that accesses the table in question
• whether it uses dynamic sql
• or is part of a stored procedure compiled with AUTHID CURRENT_USER exception when others then if sqlcode = -942 then
  • handle the 'table does not exists' exception end;

in contrast to the original suggestion

declare

begin

    select count(*) into c from all_objects where object_name = 'XXXXX'     if c > 0
    then

• some code here that accesses the table in question
• whether it uses dynamic sql
• or is part of a stored procedure compiled with AUTHID CURRENT_USER
• (same code that would be in the body of the other example) end if; end;