Modifying External Tables

Altering External Tables

You can use any of the ALTER TABLE clauses shown in Table 1 below to change the characteristics of an external table. No other clauses are permitted.

Table 1: ALTER TABLE Clauses for External Tables

ALTER TABLE Clause	Description	Example
REJECT LIMIT	Changes the reject limit	ALTER TABLE admin_ext_employees REJECT LIMIT 100;
PROJECT COLUMN	Determines how the access driver validates rows in subsequent queries: PROJECT COLUMN REFERENCED: the access driver processes only the columns in the select list of the query. This setting may not provide a consistent set of rows when querying a different column list from the same external table. This is the default. PROJECT COLUMN ALL: the access driver processes all of the columns defined on the external table. This setting always provides a consistent set of rows when querying an external table.	ALTER TABLE admin_ext_employees PROJECT COLUMN REFERNCED; ALTER TABLE admin_ext_employees PROJECT COLUMN ALL;
DEFAULT DIRECTORY	Changes the default directory specification	ALTER TABLE admin_ext_employees DEFAULT DIRECTORY admin_dat2_dir;
ACCESS PARAMETERS	Allows access parameters to be changed without dropping and re-creating the external table metadata	ALTER TABLE admin_ext_employees ACCESS PARAMETERS (FIELDS TERMINATED BY ';');
LOCATION	Allows data sources to be changed without dropping and re-creating the external table metadata	ALTER TABLE admin_ext_employees LOCATION ('empxt3.txt', 'empxt4.txt');
PARALLEL	No difference from regular tables. Allows degree of parallelism to be changed.	No new syntax
ADD COLUMN	No difference from regular tables. Allows a column to be added to an external table. Virtual columns are not permitted.	No new syntax
MODIFY COLUMN	No difference from regular tables. Allows an external table column to be modified. Virtual columns are not permitted.	No new syntax
SET UNUSED	Transparently converted into an ALTER TABLE DROP COLUMN command. Because external tables consist of metadata only in the database, theDROP COLUMN command performs equivalently to the SET UNUSEDcommand.	No new syntax
DROP COLUMN	No difference from regular tables. Allows an external table column to be dropped.	No new syntax
RENAME TO	No difference from regular tables. Allows external table to be renamed.	No new syntax

Behavior Differences Between SQL*Loader and External Tables

This section describes important differences between loading data with external tables, using the ORACLE_LOADER access driver, as opposed to loading data with SQL*Loader conventional and direct path loads. This information does not apply to the ORACLE_DATAPUMP access driver.

Multiple Primary Input Datafiles

If there are multiple primary input datafiles with SQL*Loader loads, a bad file and a discard file are created for each input datafile. With external table loads, there is only one bad file and one discard file for all input datafiles. If parallel access drivers are used for the external table load, each access driver has its own bad file and discard file.

Syntax and Datatypes

The following are not supported with external table loads:

• Use of CONTINUEIF or CONCATENATE to combine multiple physical records into a single logical record.
• Loading of the following SQL*Loader datatypes: GRAPHIC, GRAPHIC EXTERNAL, and VARGRAPHIC
• Use of the following database column types: LONGs, nested tables, VARRAYs, REFs, primary key REFs, and SIDs

Byte-Order Marks

With SQL*Loader, if a primary datafile uses a Unicode character set (UTF8 or UTF16) and it also contains a byte-order mark (BOM), then the byte-order mark is written at the beginning of the corresponding bad and discard files. With external table loads, the byte-order mark is not written at the beginning of the bad and discard files.

Default Character Sets, Date Masks, and Decimal Separator

For fields in a datafile, the settings of NLS environment variables on the client determine the default character set, date mask, and decimal separator. For fields in external tables, the database settings of the NLS parameters determine the default character set, date masks, and decimal separator.

Use of the Backslash Escape Character

In SQL*Loader, you can use the backslash (\) escape character to mark a single quotation mark as a single quotation mark, as follows:

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''

In external tables, the use of the backslash escape character within a string will raise an error. The workaround is to use double quotation marks to mark the separation string, as follows:

TERMINATED BY ',' ENCLOSED BY "'"