External tables are created using the SQL
TABLE...ORGANIZATION EXTERNAL statement. When you create an external table, you specify the following attributes:
TYPE - specifies the type of external table. The two available types are the
ORACLE_LOADER type and the
ORACLE_DATAPUMP type. Each type of external table is supported by its own access driver.
ORACLE_LOADER access driver is the default. It loads data from external tables to internal tables. The data must come from text data files. (The
ORACLE_LOADER access driver cannot perform unloads; that is, it cannot move data from an internal table to an external table.)
ORACLE_DATAPUMP access driver can perform both loads and unloads. The data must come from binary dump files. Loads to internal tables from external tables are done by fetching from the binary dump files. Unloads from internal tables to external tables are done by populating the binary dump files of the external table. The
ORACLE_DATAPUMP access driver can write dump files only as part of creating an external table with the SQL
CREATE TABLE AS SELECT statement. Once the dump file is created, it can be read any number of times, but it cannot be modified (that is, no DML operations can be performed).
DIRECTORY - specifies the default directory to use for all input and output files that do not explicitly name a directory object. The location is specified with a directory object, not a directory path. You must create the directory object before you create the external table; otherwise, an error is generated. See "Location of Data Files and Output Files" for more information.
PARAMETERS - describe the external data source and implement the type of external table that was specified. Each type of external table has its own access driver that provides access parameters unique to that type of external table. Access parameters are optional. See "Access Parameters".
LOCATION - specifies the data files for the external table. The files are named in the form
directory portion is optional. If it is missing, then the default directory is used as the directory for the file. If you are using the
ORACLE_LOADER access driver, then you can use wildcards in the file name: an asterisk (*) signifies multiple characters, a question mark (?) signifies a single character.
The following example shows the use of each of these attributes (it assumes that the default directory
def_dir1 already exists):
SQL> CREATE TABLE emp_load 2 (employee_number CHAR(5), 3 employee_dob CHAR(20), 4 employee_last_name CHAR(20), 5 employee_first_name CHAR(15), 6 employee_middle_name CHAR(15), 7 employee_hire_date DATE) 8 ORGANIZATION EXTERNAL 9 (TYPE ORACLE_LOADER 10 DEFAULT DIRECTORY def_dir1 11 ACCESS PARAMETERS 12 (RECORDS DELIMITED BY NEWLINE 13 FIELDS (employee_number CHAR(2), 14 employee_dob CHAR(20), 15 employee_last_name CHAR(18), 16 employee_first_name CHAR(11), 17 employee_middle_name CHAR(11), 18 employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy" 19 ) 20 ) 21 LOCATION ('info.dat') 22 ); Table created.
The information you provide through the access driver ensures that data from the data source is processed so that it matches the definition of the external table. The fields listed after
emp_load are actually defining the metadata for the data in the
dat source file.