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

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

  

See Also

OMBRETRIEVE

OMBCREATE EXTERNAL_TABLE

OMBALTER EXTERNAL_TABLE

OMBDROP EXTERNAL_TABLE