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

Source: Internet
Author: User
Tags definition command line create directory table definition

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.