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

> yes, you're using the wrong tool
> where are your data coming from?

We have the "table A" and "table B" in our databases, and periodically give our users the data, to update their tables. Since many many many many (many many!) years ago we created a general program that scans a table and writes table data in the form of 'INSERT INTO...', until now we used that program to generate a file with insert statements for each row.
On customer side, there is a batch file which executes, using sqlplus, that file.
At the moment, we have all customers with table A, some of them with table B.
If I put INSERT INTO statements for both table in one file, I get an error for every INSERT failed (for customers who don't have table B).

By now, my today's best solution was to create 2 files, with same logic: one for insert into table A, and one for insert into table B, and the second file will be delivered just to customers who need it.

To automate that process, I can't use export/import, since (please correct me if I go wrong) if a table does not exist in destination database, it will be created, and that's wrong.

> you probable either need to user exp/imp to move the data (assuming you
> don't have a live DB LINK connection between the databases -- if you do,
> then you likely just need to do an INSERT ... SELECT), or, as suggested,
use
> SQL*Loader

Yes, in conclusion the real problem is not to test if a table exists anymore, but to have a different data import approach. Never used SQL*Loader before, I thought not to need it, but it seems to be a good solution to my problem.

> (i guess this thread would have been a lot shorter if we asked these
> questions in the first place!)

:-) Who knows, anyway it's beautiful to have many opinions!

Thank you, MCS
Regards Received on Fri Dec 05 2003 - 10:43:01 CST