How to generate an external table definition from Oracle 9i sql*loader

The external table introduced in Oracle 9i allows you to map a sequential text file to an Oracle table definition. Once you have defined an external table, you can apply all the functionality of the SQL SELECT statement-including parallel queries-to handle it. External tables are based on Sql*loader, but the syntax for defining them is different. If you're already familiar with Sql*loader, there's a shortcut: Applying the Sql*loader itself generates a script that creates an external table definition for you.

When you add a sql*loader command line, the parameter external_table=generate_only, which translates the control file used by the system into a SQL script and outputs it to the system's log file. You can edit the log files, or copy and paste the code into a new script.

List A is a control file that is used to load different lengths of file records into the database. Executing the following command does not load the row, but it prints an external table in the log file. The output is in list B.

Sqlldr Scott/tiger Control=loademp.ctl

External_table=generate_only

First, the script creates a directory object that points to the directory containing the input files. The directory object gives a short, platform-independent name to the full path name of a particular operating system. Establish an association with the Create Directory command. To do this, we need to have create any directory system permissions, or have the database administrator run it for you to get permission. Also, the system-generated name is bad for memory. Before you run, it's a good idea to edit the script to take a more meaningful name.

Next, the script contains the CREATE TABLE statement itself. This statement refers to the directory object (if you modify the name above, edit it), the file is loaded, and the organization external clause is executed. It also contains syntax to describe the layout of text files.

Finally, the script contains an example of an INSERT statement selected in the outer table of the subquery, and a statement that terminates the external tables and directories after the process completes.