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

On Thu, 04 Dec 2003 10:39:54 +0100, NoName wrote:

> Hello,
>
> in a PLSQL script, I want to execute code only if a certain table exists.
> A pseudo-solution could be:
>
> declare
> cursor c1 is select * from notable;
> begin
> open c1;
> if c1%ISOPEN then
> -- statements to execute if table exists end if;
> end;
>
> Obviously, the above code is not correct, first of all the script throws
> an execption if "notable" doesn't exist. Any workaround?
>
> Thank you

How about checking USER_TABLES/ALL_TABLES to see if the table exists. Problem is, if you have a cursor declared then at compile time, all objects referenced are validated and the compiler will barf if the table is not there.

You might have to resort to dynamic SQL, but I have a nagging feeling somewhere in the back of my mind, that you can force create a procedure even if objects don't exist. On the other hand, I might be thinking of Views.

Cheers,
Norm.

-- 
Delete the obvious bit from my email address to reply by email.