OMBRETRIEVE EXTERNAL_TABLE

Purpose

Retrieve details of an external table.

Prerequisites

Should be in the context of an Oracle module.

Syntax

retrieveExternalTableCommand =  OMBRETRIEVE EXTERNAL_TABLE "QUOTED_STRING" 
          ( "retrieveExternalTableClause" | "retrieveExternalTableColumnClause" 
          | "retrieveExternalTableDatafileClause" | 
          "retrieveDataRuleUsageClause" )
     retrieveExternalTableClause =  GET ( "getPropertiesClause" | 
          "getReferenceIconSetClause" | "getExternalTableObjectsClause" )
     retrieveExternalTableColumnClause =  COLUMN "QUOTED_STRING" GET ( 
          "getPropertiesClause" | FIELD )
     retrieveExternalTableDatafileClause =  DATA_FILE "QUOTED_STRING" GET 
          "getPropertiesClause"
     retrieveDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING" ( GET ( 
          "getPropertiesClause" | GROUPS ) | GROUP "QUOTED_STRING" ( GET ( 
          "getPropertiesClause" | ATTRIBUTES | REF ( TABLE | VIEW | 
          MATERIALIZED_VIEW | EXTERNAL_TABLE ) ) | ATTRIBUTE "QUOTED_STRING" ( 
          GET ( "getPropertiesClause" | REF COLUMN ) ) ) )
     getPropertiesClause =  PROPERTIES "(" "propertyNameList" ")"
     getReferenceIconSetClause =  ( REF | REFERENCE ) ICONSET
     getExternalTableObjectsClause =  COLUMNS | FLAT_FILE | RECORD | 
          DEFAULT_LOCATION | DATA_FILES | COLUMN AT POSITION "INTEGER_LITERAL" |
           DATA_RULE_USAGES
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }

Parameters

retrieveExternalTableCommand

Retrieve details from an external table.

QUOTED_STRING

The name of the external table.

retrieveExternalTableClause

Retrieve details from an external table.

retrieveExternalTableColumnClause

Retrieve details from an external table column.

retrieveExternalTableDatafileClause

Retrieve details from one of the external table's data files.

retrieveDataRuleUsageClause

This clause retrieves the data rule usages.

QUOTED_STRING

Name of data rule usage, group or attribute.

GROUPS

Retrieve the names of all relation groups in the data rule usage.

ATTRIBUTES

Retrieve the names of all attributes in a data rule usage group.

TABLE

Table name associated withthe data rule usage group.

VIEW

View name associated withthe data rule usage group.

MATERIALIZED_VIEW

Materialized view name associated with the data rule usage group.

EXTERNAL_TABLE

External table name associated with the data rule usage group.

COLUMN

Column name associated with the data rule usage group attribute.

getPropertiesClause

Retrieve specified properties.

getReferenceIconSetClause

Get specified Icon Set.

getExternalTableObjectsClause

Retrieve a record reference, flat file reference, columns, or data files.

propertyNameList

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

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.

EXTERNAL_TABLE Object

Table 19-2 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

OMBRETRIEVE EXTERNAL_TABLE

Purpose

Prerequisites

Syntax

Parameters

retrieveExternalTableCommand

QUOTED_STRING

retrieveExternalTableClause

retrieveExternalTableColumnClause

retrieveExternalTableDatafileClause

retrieveDataRuleUsageClause

QUOTED_STRING

GROUPS

ATTRIBUTES

TABLE

VIEW

MATERIALIZED_VIEW

EXTERNAL_TABLE

COLUMN

getPropertiesClause

getReferenceIconSetClause

getExternalTableObjectsClause

propertyNameList

propertyNameList

propertyNameList

EXTERNAL_TABLE Object

See Also