Frequently, I am asked to run a script which has numerous insert statements. These are generated from flat file(s), and their size can be quite large (in the MB’s).
A useful and practical alternative is using External Tables. These are tables (for all intents and purposes), which use delimited flat files as their source.
Here’s what I recommended to my developers (on 10gR2).
File Name: MyFile.csv
Since you would know the number of columns (and hopefully their intelligent names) you can create an external table like so:
CREATE TABLE mmme_external
( entitycode VARCHAR2(1000),
( DEFAULT DIRECTORY random_dir ß This points to the oracle directory where file is located on the database server
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘,’ ß Specify the delimiter
LOCATION (‘MyFile.csv’) ß Actual file name
The only caveat is that the source file will need to be placed on the database server by someone who has access to it.
Once the table is created, it can be queried with ease.
External tables are a flexible and efficient way to load data into the database vs lets say SQL Loader (see Tom Kyte’s comments).
External Tables Concepts: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/et_concepts.htm
External Tables: http://www.orafaq.com/node/848