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

Marc Billiet wrote in article <3405519B.7BA6_at_alcatel.be>...

>Michael A. Casillas wrote:

 [text snipped]
>
>There are two ways : you can check user_tables as specified in the other
 replies, or you
>can use the exception handling :
>
>DECLARE
> NoTable Exception;
> Pragma Exception_Init(NoTable,-942);
>BEGIN
> select *
> from Some_Table
> where ...;
>EXCEPTION
> when NoTable then
> raise_application_error(-20000, 'Hey, Some_Table doesn''t exist');
>END;
[snip]

>Marc

Unfortunately, the PL/SQL code above would not work. PL/SQL does compile-time checks for objects referenced in the code. The code above would not compile if some_table did not exist at compile time. If some_table existed at compile time but was later removed then the code above would be marked invalid and not execute...it would have to be recompiled at which time it would fail.

You CAN do dynamic sql at runtime, but for the problem you described you are much better off checking the USER_TABLE view as described earlier.

jk Received on Tue Sep 02 1997 - 00:00:00 CEST