Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: PLSQL: how to test if a table exists?
"NoName" <nobody_at_nowhere.com> wrote in message
news:bqq1h9$5d6$1_at_grillo.cs.interbusiness.it...
| > The simple solution to this problem is then as follows:
| [snip]
| > begin
| > truncate table B
| > insert into B ...
| > commit;
| > exception
| > when others -- Use appropriately defined error if possible
| > then
| > null; -- ie do nothing
| > end;
|
| I tried, but when executin this script, Oracle returns two errors:
| 1) truncate table B; --> PLS-00103: found "TABLE" but expected one of the
| following: := . ( @ % ;
| 2) PLS-00123: program too large
TRUNCATE, like other DDL, cannot be directly used in a PL/SQL program (required execute immediate)
|
| > 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.
|
| Well, for sure it is a better solution. The insert way was born as the
| simplest way to syncronize the data...
| maybe at this point I can make a separate program to load data. I think
the
| "program too large" error, due to many inserts, makes useless using any
| PLSQL instruction.
| Or not?
|
|
yes, you're using the wrong tool
where are your data coming from?
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
(i guess this thread would have been a lot shorter if we asked these questions in the first place!)