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

From: Keith Jamieson <keith_jamieson_at_hotmail.com>
Date: Fri, 5 Dec 2003 10:54:29 -0000
Message-ID: <GtZzb.3332$nm6.18272@news.indigo.ie>

The simple solution to this problem is then as follows: truncate table A

insert into A ...
insert into A ...
insert into A ...

commit;
begin
truncate table B

insert into B ...
insert into B ...
insert into B ..

commit;
exception
when others -- Use appropriately defined error if possible then

     null; -- ie do nothing
end;

That will solve your problem as outlined. I noted the comments of other posters , but I really was trying to point out that the use of count to determine the number of rows that exist is superfluous, since oracle already provides you with that information. I fully agree that you should not really have tables disappearing and reappearing. It is also quite easy to determine which method (The function vs Data Dictionary Access) is more efficient by simply benchmarking both mechanisms.

I am puzzled why you need to use all these inserts. Surely putting the data into a file and using SQL*Loader would be more appropriate.

"NoName" <nobody_at_nowhere.com> wrote in message news:bqpe1d$133$1_at_grillo.cs.interbusiness.it...
> Thank you everybody. Maybe a little environment explantion of my problem
is
> needed.
>
> In our application, I added a new table for quite critical purposes, but
our
> customers can choose whether using just the "old" table or even the new
one.
> The old and new table are "read-only" for our customers, that is, there is
> no way, through the application, to modify the data.
> Both table datas are updated from us, using an SQL script, periodically.
>
> The SQL file I send to our customer, is likely:
>
> truncate table A
> insert into A ...
> insert into A ...
> insert into A ...
> commit;
> truncate table B
> insert into B ...
> insert into B ...
> insert into B ...
> commit;
>
> Since all custumers have for sure table A, but some of them can have or
not
> table B, I would have a way to test the existance of table B, before
> attempting to truncate and to insert rows.
> All the statements (test included) will go into one SQL script, which I
give
> to our customers.
>
> An error I had using my first solution, was a sort of "too many statements
> in block", or similar.
> Oh, there are approx. 6000 inserts.
>
> Regards
>
>
Received on Fri Dec 05 2003 - 04:54:29 CST