Oracle Database - External Tables

> Database > Oracle Database

1 - 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.

Advertising

3 - Articles

4 - Example

4.1 - 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;

4.2 - 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;

5 - 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.

5.1 - 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).

Advertising

5.2 - 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.

5.3 - 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

5.4 - 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.

5.4.1 - 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).

6 - List

  • (USER|DBA|ALL)_EXTERNAL_TABLES
SELECT * FROM user_external_tables;
Advertising

7 - Support

7.1 - 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 '"'

7.2 - ORA-29913: error in executing ODCIEXTTABLEFETCH callout

7.2.1 - 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

7.2.2 - 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

8 - Documentation