EXTERNAL TABLES IN ORACLE

[aspiringprog]

Hi all,

I have some external tables created in my schema in oracle. The data is loaded into these external tables from text files.

I have many files to load data from. It looks like each time I use a select statement to load data into the external tables, the current data is overwritten..i.e, the data is not appended to the existing data in the table. I need the new data to be appended to the already existing data in the external table.

Is there a way I can do this? I can always combine all the different text files into one giant text file and load that into the external tables, but I was wondering if there was anyother way.

all help is greatly appreciated.

thanks

 

[carp]

When you say you "use a select statement to load data into the external table", I am at a loss as to how you can do that. As you say, an external table is populated by a file. Do you mean you are using a query to write to a file? That would make more sense, and also explain why you are overwriting your data. It would be helpful if you posted your code so we could see exactly what you are doing.

Assuming that you are running a query that writes to a file, then you might be able to do something like

SELECT * FROM my_external_table
UNION ALL
SELECT <your query here>;

Or, if utl_file is involved here somewhere, you could open the file in RW mode instead of W. This would append the data instead of overwriting the original data.