Oracle外部表ORACLE_LOADER类型的创建语法详解

This chapter describes the access parameters for the default external tables access driver, ORACLE_LOADER. You specify these access parameters when you create the external table.

To use the information in this chapter, you must have some knowledge of the file format and record format (including character sets and field datatypes) of the datafiles on your platform. You must also know enough about SQL to be able to create an external table and perform queries against it.

The following topics are discussed in this chapter:

access_parameters Clause

record_format_info Clause

field_definitions Clause

column_transforms Clause

Reserved Words for the ORACLE_LOADER Access Driver

You may find it helpful to use the EXTERNAL_TABLE=GENERATE_ONLY parameter in SQL*Loader to get the proper access parameters for a given SQL*Loader control file. When you specify GENERATE_ONLY, all the SQL statements needed to do the load using external tables, as described in the control file, are placed in the SQL*Loader log file. These SQL statements can be edited and customized. The actual load can be done later without the use of SQL*Loader by executing these statements in SQL*Plus.

Description of the illustration et_access_param.gif

Description of the illustration et_record_spec.gif


Description of the illustration et_string.gif


Description of the illustration et_cond_spec.gif

Note that if the condition specification contains any conditions that reference field names, then the condition specifications are evaluated only after all fields have been found in the record and after blank trimming has been done. It is not useful to compare a field to BLANKS if blanks have been trimmed from the field.

The following are some examples of using condition_spec:

Description of the illustration et_condition.gif


Description of the illustration et_fields_clause.gif

Description of the illustration et_delim_spec.gif

If ENCLOSED BY is specified, the access driver starts at the current position in the record and skips over all whitespace looking for the first delimiter. All whitespace between the current position and the first delimiter is ignored. Next, the access driver looks for the second enclosure delimiter (or looks for the first one again if a second one is not specified). Everything between those two delimiters is considered part of the field.
If TERMINATED BY 
Description of the illustration et_trim_spec.gif

NOTRIM indicates that no characters will be trimmed from the field.
LRTRIM, LTRIM, and RTRIM are used to indicate that characters should be trimmed from the field. LRTRIM means that both leading and trailing spaces are trimmed. LTRIM means that leading spaces will be trimmed. RTRIM means trailing spaces are trimmed.
LDRTRIM is used to provide compatibility with SQL*Loader trim features. It is the same as NOTRIM except in the following cases:
If the field is not a delimited field, then spaces will be trimmed from the right.
If the field is a delimited field with OPTIONALLY ENCLOSED BY specified, and the optional enclosures are missing for a particular instance, then spaces will be trimmed from the left.
The default is LDRTRIM. Specifying NOTRIM yields the fastest performance.
The trim_spec clause can be specified before the field list to set the default trimming for all fields. If trim_spec is omitted before the field list, then LDRTRIM is the default trim setting. The default trimming can be overridden for an individual field as part of the datatype_spec.
If trimming is specified for a field that is all spaces, then the field will be set to NULL.
In the following example, all data is fixed-length; however, the character data will not be loaded with leading spaces. The example is followed by a sample of the datafile that can be used to load it.

Description of the illustration et_field_list.gif


Description of the illustration et_position_spec.gif


Description of the illustration et_datatype_spec.gif

If the number of bytes or characters in any field is 0, then the field is assumed to be NULL. The optional DEFAULTIF clause specifies when the field is set to its default value. Also, the optional NULLIF clause specifies other conditions for when the column associated with the field is set to NULL. If the DEFAULTIF or NULLIF clause is true, then the actions of those clauses override whatever values are read from the datafile.

Description of the illustration et_dateformat.gif


Description of the illustration et_init_spec.gif

Only one NULLIF clause and only one DEFAULTIF clause can be specified for any field. These clauses behave as follows:
If NULLIF 
Description of the illustration et_column_trans.gif


Description of the illustration et_transform.gif

The NULL transform is used to set the external table column to NULL in every row. The CONSTANT transform is used to set the external table column to the same value in every row. The CONCAT transform is used to set the external table column to the concatenation of constant strings and/or fields in the current record from the datafile. The LOBFILE transform is used to load data into a field for a record from another datafile. Each of these transforms is explained further in the following sections.

Description of the illustration et_lobfile_attr.gif

The FROM clause lists the names of all directory objects that will be used for LOBFILEs. It is used only when a field name is specified for the directory object of the name of the LOBFILE. The purpose of the FROM clause is to determine the type of access allowed to the named directory objects during initialization. If directory object in the value of field is not a directory object in this list, then the row will be rejected.
The CLOB attribute indicates that the data in the LOBFILE is character data (as opposed to RAW data). Character data may need to be translated into the character set used to store the LOB in the database.
The CHARACTERSET attribute contains the name of the character set for the data in the LOBFILEs.
The BLOB attribute indicates that the data in the LOBFILE is raw data.
If neither CLOB nor BLOB is specified, then CLOB is assumed. If no character set is specified for character LOBFILEs, then the character set of the datafile is assumed.
Reserved Words for the ORACLE_LOADER Access Driver
When identifiers (for example, column or table names) are specified in the external table access parameters, certain values are considered to be reserved words by the access parameter parser. If a reserved word is used as an identifier it must be enclosed in double quotation marks. The following are the reserved words for the ORACLE_LOADER access driver:
ALL
AND
ARE
ASTERISK
AT
ATSIGN
BADFILE
BADFILENAME
BACKSLASH
BENDIAN
BIG
BLANKS
BY
BYTES
BYTESTR
CHAR
CHARACTERS
CHARACTERSET
CHARSET
CHARSTR
CHECK
CLOB
COLLENGTH
COLON
COLUMN
COMMA
CONCAT
CONSTANT
COUNTED
DATA
DATE
DATE_CACHE
DATE_FORMAT
DATEMASK
DAY
DEBUG
DECIMAL
DEFAULTIF
DELIMITBY
DELIMITED
DISCARDFILE
DOT
DOUBLE
DOUBLETYPE
DQSTRING
DQUOTE
DSCFILENAME
ENCLOSED
ENDIAN
ENDPOS
EOF
EQUAL
EXIT
EXTENDED_IO_PARAMETERS
EXTERNAL
EXTERNALKW
EXTPARM
FIELD
FIELDS
FILE
FILEDIR
FILENAME
FIXED
FLOAT
FLOATTYPE
FOR
FROM
HASH
HEXPREFIX
IN
INTEGER
INTERVAL
LANGUAGE
IS
LEFTCB
LEFTTXTDELIM
LEFTP
LENDIAN
LDRTRIM
LITTLE
LOAD
LOBFILE
LOBPC
LOBPCCONST
LOCAL
LOCALTZONE
LOGFILE
LOGFILENAME
LRTRIM
LTRIM
MAKE_REF
MASK
MINUSSIGN
MISSING
MISSINGFLD
MONTH
NEWLINE
NO
NOCHECK
NOT
NOBADFILE
NODISCARDFILE
NOLOGFILE
NOTEQUAL
NOTERMBY
NOTRIM
NULL
NULLIF
OID
OPTENCLOSE
OPTIONALLY
OPTIONS
OR
ORACLE_DATE
ORACLE_NUMBER
PLUSSIGN
POSITION
PROCESSING
QUOTE
RAW
READSIZE
RECNUM
RECORDS
REJECT
RIGHTCB
RIGHTTXTDELIM
RIGHTP
ROW
ROWS
RTRIM
SCALE
SECOND
SEMI
SETID
SIGN
SIZES
SKIP
STRING
TERMBY
TERMEOF
TERMINATED
TERMWS
TERRITORY
TIME
TIMESTAMP
TIMEZONE
TO
TRANSFORMS
UNDERSCORE
UINTEGER
UNSIGNED
VALUES
VARCHAR
VARCHARC
VARIABLE
VARRAW
VARRAWC
VLENELN
VMAXLEN
WHEN
WHITESPACE
WITH
YEAR
ZONED
参考至：http://web.njit.edu/info/oracle/DOC/server.102/b14215/et_params.htm#g1031955如有错误，欢迎指正邮箱:czmcj@163.com