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

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

OMBCREATE EXTERNAL_TABLE

Purpose

Prerequisites

Syntax

Parameters

createExternalTableCommand

QUOTED_STRING

setPropertiesAndReferencesClauses

addExternalTableSCOClauses

setPropertiesClause

setReferencesToRecordAndLocationClauses

addExternalTableColumnClause

addExternalTableDatafileClause

propertyNameList

propertyNameList

propertyNameList

propertyValueList

setReferencesToRecordFileModuleClause

setReferencesToLocationClause

setReferenceIconSetClause

setReferencesToFileAndModuleClause

setPropertiesAndReferencesToFieldClauses

propertyValue

setReferencesToFieldClause

EXTERNAL_TABLE Object

EXTERNAL_TABLE_COLUMN Object

DATA_FILE Object

See Also