../E14406-01.epub /> ../E14406-01.mobi />

OMBCREATE EXTERNAL_TABLE

Purpose

To create an external table.

Prerequisites

Should be in the context of an Oracle Module.

Syntax

createExternalTableCommand =  OMBCREATE ( EXTERNAL_TABLE "QUOTED_STRING" [ 
          SET "setPropertiesAndReferencesClauses" ] [ 
          "addExternalTableSCOClauses" ] )
     setPropertiesAndReferencesClauses =  "setPropertiesClause" [ SET ( REF | 
          REFERENCE ) "setReferencesToRecordAndLocationClauses" ] | ( REF | 
          REFERENCE ) "setReferencesToRecordAndLocationClauses"
     addExternalTableSCOClauses =  ADD ( "addExternalTableColumnClause" [ 
          "addExternalTableSCOClauses" ] | "addExternalTableDatafileClause" { 
          ADD "addExternalTableDatafileClause" } )
     setPropertiesClause =  PROPERTIES "(" "propertyNameList" ")" VALUES "(" 
          "propertyValueList" ")"
     setReferencesToRecordAndLocationClauses =  
          "setReferencesToRecordFileModuleClause" [ 
          "setReferencesToLocationClause" ] [ SET ( REF | REFERENCE ) 
          "setReferenceIconSetClause" ] | "setReferencesToFileAndModuleClause" [
           "setReferencesToLocationClause" ] [ SET ( REF | REFERENCE ) 
          "setReferenceIconSetClause" ] | "setReferencesToLocationClause" [ SET 
          ( REF | REFERENCE ) "setReferenceIconSetClause" ] | 
          "setReferenceIconSetClause"
     addExternalTableColumnClause =  COLUMN "QUOTED_STRING" [ SET 
          "setPropertiesAndReferencesToFieldClauses" ]
     addExternalTableDatafileClause =  DATA_FILE "QUOTED_STRING" [ SET 
          "setPropertiesClause" ]
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
     propertyValueList =  "propertyValue" { "," "propertyValue" }
     setReferencesToRecordFileModuleClause =  RECORD "QUOTED_STRING" OF 
          FLAT_FILE "QUOTED_STRING"
     setReferencesToLocationClause =  DEFAULT_LOCATION "QUOTED_STRING"
     setReferenceIconSetClause =  ICONSET "QUOTED_STRING"
     setReferencesToFileAndModuleClause =  FLAT_FILE "QUOTED_STRING"
     setPropertiesAndReferencesToFieldClauses =  "setPropertiesClause" [ SET ( 
          REF | REFERENCE ) "setReferencesToFieldClause" ] | ( REF | REFERENCE )
           "setReferencesToFieldClause"
     propertyValue =  ( "QUOTED_STRING" | "INTEGER_LITERAL" | 
          "FLOATING_POINT_LITERAL" )
     setReferencesToFieldClause =  FIELD "QUOTED_STRING"

Parameters

createExternalTableCommand

Create a new external table.

QUOTED_STRING

The name of the new external table.

setPropertiesAndReferencesClauses

Set the properties and/or flat file reference of the external table.

addExternalTableSCOClauses

Add columns and/or data files to the external table.

setPropertiesClause

Set specified properties of the external table.

setReferencesToRecordAndLocationClauses

Set the referenced record and/or default location.

addExternalTableColumnClause

Add an external table column.

addExternalTableDatafileClause

Add a data file to the external table.

propertyNameList

The names of the properties whose values you want to set.

propertyNameList

propertyNameList

Basic properties for COLUMN: 

Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Business name of the column

Name: DESCRIPTION
Type: STRING(4000)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Description of the column 

Name: DATATYPE
Type: STRING
Valid Values: BINARY_DOUBLE, BINARY_FLOAT, BLOB, CHAR, CLOB, DATE, FLOAT, INTEGER, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, NCHAR, NCLOB, NUMBER, NVARCHAR2, RAW, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP WITH TIME ZONE, VARHCAR, VARCHAR2
Default: NUMBER
The datatype of a column

Name: LENGTH
Type: NUMBER
Valid Values: 
Default: 1
The length of a number 

Name: PRECISION
Type: NUMBER
Valid Values: 0 - 38
Default: 1
The precision of a number. Use 0 to specify floating-point numbers.

Name: SCALE
Type: NUMBER
Valid Values: -84 - 127
Default: 1
The scale of a number.

Name: FRACTIONAL_SECONDS_PRECISION
Type: NUMBER
Valid Values: 0 - 9
Default: 0
The precision of a timestamp or interval.

propertyValueList

The values for the named properties.

setReferencesToRecordFileModuleClause

Specify the record and full path to the flat file for the external table to reference.

setReferencesToLocationClause

The name of the external table's default location.

setReferenceIconSetClause

Set specified Icon Set.

setReferencesToFileAndModuleClause

Specify the full path to the flat file for the external table to reference.

setPropertiesAndReferencesToFieldClauses

Set the properties and/or field reference of the external table column.

propertyValue

A property value.

setReferencesToFieldClause

Set the name of the field which the external table column references.

EXTERNAL_TABLE Object

Table 7-7 EXTERNAL_TABLE Object

Property Type Choices Min Max Default Description

BAD_FILE_LOCATION

STRING

none

none

none

USE_DEFAULT_LOCATION

 

BAD_FILE_NAME

STRING

none

none

none

empty string

 

CONFIGURATION_DEFAULT_LOCATION

STRING

none

none

none

empty string

Customized location default within each configuration of an external table. If not defined, overall default location of an external table is used.

DEPLOYABLE

BOOLEAN

true, false

none

none

true

Description not available.

DISCARD_FILE_LOCATION

STRING

none

none

none

USE_DEFAULT_LOCATION

 

DISCARD_FILE_NAME

STRING

none

none

none

empty string

 

ENDIAN

STRING

BIG, LITTLE, PLATFORM

none

none

PLATFORM

Description not available.

ERROR_TABLESPACE

STRING(30)

none

none

none

empty string

Description not available.

ERROR_TABLE_NAME

STRING(30)

none

none

none

empty string

Description not available.

GENERATE_ERROR_TABLE_ONLY

BOOLEAN

true, false

none

none

false

Create, drop, replace or upgrade only the error table.

GENERATION_COMMENTS

STRING

none

none

none

empty string

Enter additional comments for the generated code.

LOAD_NULLS_WHEN_MISSING_VALUES

BOOLEAN

true, false

none

none

false

Description not available.

LOG_FILE_LOCATION

STRING

none

none

none

USE_DEFAULT_LOCATION

 

LOG_FILE_NAME

STRING

none

none

none

empty string

The name of the file that will store the log messages

NLS_CHARACTERSET

STRING

none

none

none

empty string

NLS Characterset of the file. When the Access Parameters property is specified for the External Table, this configuration parameter is ignored.

NUMBER_OF_REJECTS_ALLOWED

NUMBER

none

0

2147483647

0

Description not available.

PARALLEL_ACCESS_DRIVERS

NUMBER

none

1

63999

1

Description not available.

PARALLEL_ACCESS_MODE

BOOLEAN

true, false

none

none

false

Description not available.

REJECTS_ARE_UNLIMITED

BOOLEAN

true, false

none

none

true

Description not available.

STRING_SIZES_IN

STRING

BYTES, CHARACTERS

none

none

BYTES

Description not available.

TRIM

STRING

BOTH, LEFT, NONE, RIGHT, SQL*LOADER

none

none

NONE

 

EXTERNAL_TABLE_COLUMN Object

Table 7-8 EXTERNAL_TABLE_COLUMN Object

Property Type Choices Min Max Default Description

META_IMPORT_BYTES_PER_CHAR

NUMBER

none

none

none

1

 

META_IMPORT_CHARSET

STRING(45)

none

none

none

empty string

 

META_IMPORT_USE_CHAR_SEMANTICS

BOOLEAN

true, false

none

none

false

 

DATA_FILE Object

Table 7-9 DATA_FILE Object

Property Type Choices Min Max Default Description

DATA_FILE_LOCATION

STRING

none

none

none

USE_DEFAULT_LOCATION

 

DATA_FILE_NAME

STRING

none

none

none

empty string

 

SOURCE_DATA_FILE_DIRECTORY

STRING

none

none

none

empty string

 

SOURCE_DATA_FILE_NAME

STRING

none

none

none

empty string