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

On Thu, 4 Dec 2003 22:14:29 -0000, "Keith Jamieson" <keith_jamieson_at_hotmail.com> wrote:

>Personally, I would query the data dictionary, and I believe all_tables will
>have a list of all tables that you have access to. Alternatively, you can
>declare a table with Definer Rights (Ie The code is executed as the owner of
>the procedure, as opposed to the user who is running the procedure).
>
>The Data Dictionary is supposed to be optimised for quick access, so I would
>personally favour this approach.

Tables don't disappear on the fly, unless you have a very whacky environment. Checking for existence should be redundant and been dealt with by exception handling. Misusing the datadictionary over and over again, to check something what doesn't need to be checked will have a noticeable impact on your application as the dictionary cache will grow and trim your shared pool. Your shared pool however will grow because you are using dynamic sql everywhere. Your application will be unscalable. Now would you really like the end-user to experience how an unscalable application works? Need I say more?

--
Sybrand Bakker, Senior Oracle DBA