from sqlldr (sql loader) to external table

Prior to Oracle 9i, to import data from a text file, you will have to run sqlldr (sql loader) to load the data into a table.

Starting from Oracel 9i, you can use external table to read text file data from within sqlplus and you can use the sqlloader control file to help you.

Just run something like this:

sqlldr scott/tiger control=mycontrol.ctl EXTERNAL_TABLE=GENERATE_ONLY.

This will create a log file with :

1. CREATE DIRECTORY statement

2. CREATE EXTERNAL TABLE statement

3. sample INSERT statement

4. sample DROP TABLE statement

It's a quick way to move from sql loader to external table.

External tables have these benefits:

1. you do not have to import the data into a table to read it. You can just read from the text file as if it were a table

2. since external table can be used with SELECT, you can write a load program with either SQL or PL/SQL. Prior to Oracle 9i, it was often asked whether it was possible to invoke sql loader from within PL/SQL. With external table, you do not have to.

Links

• Subscribe to this feed