About
External tables feature lets you access data in external sources as if it were in a table in the database.
The external tables feature is a complement to existing SQL*Loader functionality. External tables can be written to using the ORACLE_DATAPUMP access driver. Neither data manipulation language (DML) operations nor index creation are allowed on an external table. Therefore, SQL*Loader may be the better choice in data loading situations that require additional indexing of the staging table.
Articles Related
Articles
Example
Separator
CREATE TABLE dept_external (
deptno NUMBER(6),
dname VARCHAR2(20),
loc VARCHAR2(25)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY admin
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
BADFILE 'ulcase1.bad'
DISCARDFILE 'ulcase1.dis'
LOGFILE 'ulcase1.log'
SKIP 20
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(
deptno INTEGER EXTERNAL(6),
dname CHAR(20),
loc CHAR(25)
)
)
LOCATION ('ulcase1.ctl')
)
REJECT LIMIT UNLIMITED;
Fix Length
CREATE
TABLE EXT_TABLE
(
TEXT1 VARCHAR2( 4000 CHAR ) NULL
, TEXT2 VARCHAR2( 4000 CHAR ) NULL
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER DEFAULT DIRECTORY EXT_TABLE_DIR ACCESS PARAMETERS
(
records delimited BY newline
NOBADFILE
FIELDS
(
TEXT1 POSITION( 1:2 ),
TEXT2 POSITION( 3:3000 )
)
) LOCATION( EXT_TABLE_DIR:'myFile.txt' )
)
REJECT LIMIT UNLIMITED;
Create table clause
External tables are created using the SQL CREATE TABLE…ORGANIZATION EXTERNAL statement. When you create an external table, you specify the following attributes.
TYPE
TYPE access_driver_type indicates the access driver of the external table. The access driver is the API that interprets the external data for the database. Oracle Database provides two access drivers:
- ORACLE_LOADER (default access driver)
It can perform only data loads, and the data must come from text datafiles. Loads from external tables to internal tables are done by reading from the text-only datafiles in the external table.
- ORACLE_DATAPUMP.
The 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).
DEFAULT DIRECTORY
DEFAULT DIRECTORY lets you specify a default directory object corresponding to a directory on the file system where the external data sources may reside. The default directory can also be used by the access driver to store auxiliary files such as error logs.
ACCESS PARAMETERS
The optional ACCESS PARAMETERS clause lets you assign values to the parameters of the specific access driver for this external table. See the section below
LOCATION
specifies the location of the external data. The location is specified as a list of directory objects and file names. If the directory object is not specified, then the default directory object is used as the file location.
DELIMITED BY
The “DELIMITED BY string” clause is used to indicate the characters that identify the end of a record.
String can be either :
- text. (The text is converted to the character set of the datafile )
- or a series of hexadecimal digits enclosed within quotation marks and prefixed by OX or X.
If DELIMITED BY NEWLINE is specified, then the actual value used is platform-specific (depend of the database system).
List
- (USER|DBA|ALL)_EXTERNAL_TABLES
select * from user_external_tables;
Support
KUP-04021: field formatting error for field FIELD_NAME
When you want to read data from an external table, you may see this error :
KUP-04021: field formatting error for field FIELD_NAME
KUP-04023: field start is after end of record
This error occurs when the loader have a problem to see the delimitation of the field.
The suppression of this line can resolve the problem :
OPTIONALLY ENCLOSED BY '"' AND '"'
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
Found record longer than buffer size supported
When you want to see the data from an external table, you may fired this error :
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04020: found record longer than buffer size supported, 524288, in C:\PathToFile\FileToLoad.txt_
ORA-06512: at "SYS.ORACLE_LOADER", line 52
This error can appear when you use a file which come from an others system because the line separator is not the same on each plateform. You can verify it with JEditor
Modify this line :
RECORDS DELIMITED BY NEWLINE
by
RECORDS DELIMITED BY '\n'
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
No more details
When you get no more details on the error than the error ORA-2991, it's that you have a problem on your system file as :
- no permission for the log, bad and discard file
- no more space