I would not use an intermediate table.An external table is not, I repeat not, an 'intermediate table'.
hoek wrote:I know about external tables.
I'm sorry, but I tend to believe that's bad advice in the year of 2011:I would not use an intermediate table.An external table is not, I repeat not, an 'intermediate table'.
theoa wrote:Why would using SQL*Loader, creating a control file for single use and then deleting that control file again, be any different to creating an external table for single use? It's just as much work and SQL*Loader offers less flexibility. The only 'advantage' SQL*Loader has is that it can upload data from a client machine to the server more easily than external tables can but, in that case, one should seriously question why the 'user' has got data on their client and it's not on the server.
Since this is a one-time read-the-flat-files-into-an-existing-table, using sqlloader would be IMHO just a little bit easier than defining an external table (DDL), insert the records in the existing table (DML), and then delete the external table again (DDL).
If it were a repeating job, then an external table would be the way to go.
Nikhil Juneja wrote:To add to what Justin said, if the file is not on the database server you have two main options:
Decided to go in for external table.. and performance is not that bad..
One more query on this.. currently I am working on Oracle server itself (client on the same machine)
But our UAT servers have a different configurations. we work through separate server having client only
Will external table work on this scenario... or we need to modify the definition.