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

From: Keith Jamieson <keith_jamieson_at_hotmail.com>
Date: Thu, 4 Dec 2003 22:14:29 -0000
Message-ID: <mmOzb.3284$nm6.18203@news.indigo.ie>

More code + Dynamic SQL = more work for Oracle, and as a result, slower code. As well as this, the use of Native Dynamic SQL outlined here does not use Bind Variables.

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.

"mcstock" <mcstock @ enquery .com> wrote in message news:o46dnXJiS5uHA1KiRVn-gQ_at_comcast.com...
> "Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message
> news:b75vsvkhgqodojbvkifd245daqdqidj1c5_at_4ax.com...
> | On Thu, 4 Dec 2003 14:58:58 +0100, "NoName" <nobody_at_nowhere.com>
> | wrote:
> |
> | >Thanks to everybody.
> | >Using your suggestions, I produced the following code:
> | >
> | >declare
> | > c numeric;
> | >begin
> | > select count(*) into c from user_tables where table_name =
'TABLEXY';
> | > if c > 0 THEN
> | > -- statements to do
> | > end if;
> | >end;
> | >
> | >Hope this could help other newbies :-)
> | >Regards
> | >
> |
> | They would better trust appropiate exception handling and not trust
> | this horrible approach.
> |
> |
> | --
> | Sybrand Bakker, Senior Oracle DBA

>

> I assume that you're using dynamic SQL, otherwise the procedure would not
> compile, if a referenced table (or other object) does not exist (whether
> it's an anonymous block or a stored procedure).

>

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

>

> More code, but avoids a data dictionary view access (which would be
> superfluous for the hopefully majority of the cases when the table
actually
> exists)

>
> Also, more correct (more correcter?) since the object may not actually be
a
> table, and may not be owned by the user -- i.e., a synonym or a view would
> not be listed in USER_TABLES.

>

> --
> Mark C. Stock
> mcstock -> enquery(dot)com
> www.enquery.com training & consulting

>
>
>
>
>

Received on Thu Dec 04 2003 - 16:14:29 CST