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

OMBALTER EXTERNAL_TABLE

Purpose

Alter the external table by renaming it, and/or reset its properties.

Prerequisites

Should be in the context of an Oracle Module.

Syntax

alterExternalTableCommand =  OMBALTER ( EXTERNAL_TABLE "QUOTED_STRING" ( 
          "renameClause" [ "alterExternalTablePropertiesAndReferencesClause" ] [
           "alterExternalTableObjectClauses" ] | 
          "alterExternalTablePropertiesAndReferencesClause" [ 
          "alterExternalTableObjectClauses" ] | 
          "alterExternalTableObjectClauses" ) )
     renameClause =  RENAME TO "QUOTED_STRING"
     alterExternalTablePropertiesAndReferencesClause =  SET ( 
          "setPropertiesClause" [ SET ( REF | REFERENCE ) 
          "alterReferencesToRecordAndLocationClauses" | UNSET ( REF | REFERENCE 
          ) "unsetReferenceIconSetClause" ] | ( REF | REFERENCE ) 
          "alterReferencesToRecordAndLocationClauses" ) | UNSET ( REF | 
          REFERENCE ) "unsetReferenceIconSetClause"
     alterExternalTableObjectClauses =  ADD ( "addExternalTableObjectClause" [ 
          "alterExternalTableObjectClauses" ] | "addDataRuleUsageClause" { 
          "alterDataRuleUsageClauses" } ) | MODIFY ( 
          "modifyExternalTableObjectClause" [ "alterExternalTableObjectClauses" 
          ] | "addDataRuleUsageClause" { "alterDataRuleUsageClauses" } ) | 
          DELETE ( "deleteExternalTableObjectClause" [ 
          "alterExternalTableObjectClauses" ] | "deleteDataRuleUsageClause" { 
          "alterDataRuleUsageClauses" } )
     setPropertiesClause =  PROPERTIES "(" "propertyNameList" ")" VALUES "(" 
          "propertyValueList" ")"
     alterReferencesToRecordAndLocationClauses =  
          "setReferencesToRecordFileModuleClause" [ 
          "setReferencesToLocationClause" ] [ SET ( REF | REFERENCE ) 
          "setReferenceIconSetClause" | UNSET ( REF | REFERENCE ) 
          "unsetReferenceIconSetClause" ] | "setReferencesToFileAndModuleClause"
           [ "setReferencesToLocationClause" ] [ SET ( REF | REFERENCE ) 
          "setReferenceIconSetClause" | UNSET ( REF | REFERENCE ) 
          "unsetReferenceIconSetClause" ] | "setReferencesToLocationClause" [ 
          SET ( REF | REFERENCE ) "setReferenceIconSetClause" | UNSET ( REF | 
          REFERENCE ) "unsetReferenceIconSetClause" ] | 
          "setReferenceIconSetClause"
     unsetReferenceIconSetClause =  ICONSET
     addExternalTableObjectClause =  COLUMN "QUOTED_STRING" [ AT POSITION 
          "INTEGER_LITERAL" ] [ SET "setPropertiesAndReferencesToFieldClauses" ]
           | "addExternalTableDatafileClause"
     addDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING" SET REF DATA_RULE
           "QUOTED_STRING" ( GROUP "QUOTED_STRING" SET REF ( TABLE | VIEW | 
          MATERIALIZED_VIEW | EXTERNAL_TABLE ) "QUOTED_STRING" ( ATTRIBUTE 
          "QUOTED_STRING" SET REF COLUMN "QUOTED_STRING" )+ )+ [ SET 
          "setPropertiesClause" ]
     alterDataRuleUsageClauses =  ADD "addDataRuleUsageClause" | MODIFY 
          "modifyDataRuleUsageClause" | DELETE "deleteDataRuleUsageClause"
     modifyExternalTableObjectClause =  COLUMN "QUOTED_STRING" ( "renameClause" 
          [ "moveExternalTableColumnToClause" ] [ SET 
          "setPropertiesAndReferencesToFieldClauses" ] | 
          "moveExternalTableColumnToClause" [ SET 
          "setPropertiesAndReferencesToFieldClauses" ] | SET 
          "setPropertiesAndReferencesToFieldClauses" ) | DATA_FILE 
          "QUOTED_STRING" ( "renameClause" [ SET "setPropertiesClause" ] | SET 
          "setPropertiesClause" )
     deleteExternalTableObjectClause =  COLUMN "QUOTED_STRING" | DATA_FILE 
          "QUOTED_STRING"
     deleteDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING"
     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"
     addExternalTableDatafileClause =  DATA_FILE "QUOTED_STRING" [ SET 
          "setPropertiesClause" ]
     modifyDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING" ( 
          "renameClause" [ SET "setPropertiesClause" ] | SET 
          "setPropertiesClause" )
     moveExternalTableColumnToClause =  MOVE TO POSITION "INTEGER_LITERAL"
     propertyValue =  ( "QUOTED_STRING" | "INTEGER_LITERAL" | 
          "FLOATING_POINT_LITERAL" )
     setReferencesToFieldClause =  FIELD "QUOTED_STRING"

Parameters

alterExternalTableCommand

Alter the existing external table.

QUOTED_STRING

The name of the external table to alter.

renameClause

Rename the external table to the value of the following quoted string.

alterExternalTablePropertiesAndReferencesClause

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

alterExternalTableObjectClauses

Add, modify, or delete columns or data files.

setPropertiesClause

Set specified properties of the external table.

alterReferencesToRecordAndLocationClauses

Set the referenced record and/or default location and/or references to iconset.

unsetReferenceIconSetClause

Set specified Icon Set.

addExternalTableObjectClause

Add a column to the external table. The name of the new column will be the quoted string.

addDataRuleUsageClause

Add a data rule usage to the relation.

alterDataRuleUsageClauses

Add, modify, or delete data rule usages.

modifyExternalTableObjectClause

Modify the properties of a column or data file or move a column to a new position.

deleteExternalTableObjectClause

Delete a column or data file.

deleteDataRuleUsageClause

Delete a data rule usage.

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

Specify the default location for the external table.

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.

addExternalTableDatafileClause

Add a new data file to the external table. The name of the new data file will be the quoted string. You may also set the properties of the new data file.

modifyDataRuleUsageClause

Rename or modify the properties of a data rule usage.

moveExternalTableColumnToClause

Move a column of the external table.

propertyValue

A property value.

setReferencesToFieldClause

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

EXTERNAL_TABLE Object

Table 2-11 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 2-12 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 2-13 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