Skip Headers
Oracle® Warehouse Builder OMB*Plus Command Reference
11g Release 2 (11.2)

Part Number E14406-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
Mobi · ePub

OMBALTER FLAT_FILE

Purpose

Alter the flat file by renaming it, and/or reset its properties.

Prerequisites

Should be in the context of a flat file module.

Syntax

alterFlatFileCommand =  OMBALTER ( FLAT_FILE "QUOTED_STRING" ( 
          "renameClause" [ "alterPropertiesOrIconSetClause" ] [ 
          "importCopybookClause" ] { "alterRecordClauses" } | "renameClause" [ 
          "alterPropertiesOrIconSetClause" ] { "alterRecordClauses" } | 
          "alterPropertiesOrIconSetClause" [ "importCopybookClause" ] { 
          "alterRecordClauses" } | "alterPropertiesOrIconSetClause" { 
          "alterRecordClauses" } | "alterRecordClauses" { "alterRecordClauses" }
           | "importCopybookClause" ) )
     renameClause =  RENAME TO "QUOTED_STRING"
     alterPropertiesOrIconSetClause =  SET ( "setPropertiesClause" [ SET 
          "setReferenceIconSetClause" | UNSET "unsetReferenceIconSetClause" ] | 
          "setReferenceIconSetClause" ) | UNSET "unsetReferenceIconSetClause"
     importCopybookClause =  IMPORT ( COPYBOOK "QUOTED_STRING" { EXCLUDE 
          "excludeGroupsClause" | EXCLUDE "excludeRedefinesClause" | 
          STORAGE_BOUNDARY "storageBoundaryClause" | ON_ERROR "onErrorClause" | 
          MESSAGE_LEVEL "messageLevelClause" } )
     alterRecordClauses =  ADD "addRecordClauseForAlter" | MODIFY 
          "modifyRecordClause" | DELETE "deleteRecordClause"
     setPropertiesClause =  PROPERTIES "(" "propertyNameList" ")" VALUES "(" 
          "propertyValueList" ")"
     setReferenceIconSetClause =  ( REFERENCE | REF ) ICONSET "QUOTED_STRING"
     unsetReferenceIconSetClause =  ( REFERENCE | REF ) ICONSET
     excludeGroupsClause =  GROUPS
     excludeRedefinesClause =  REDEFINES
     storageBoundaryClause =  "INTEGER_LITERAL"
     onErrorClause =  ( CHECK | LOAD_VALID | CANCEL )
     messageLevelClause =  ( NONE | ERROR | WARNING | INFO )
     addRecordClauseForAlter =  RECORD "QUOTED_STRING" [ SET 
          "setPropertiesClause" ] { ADD "addFieldClauseForAlter" }
     modifyRecordClause =  RECORD "QUOTED_STRING" ( "renameClause" [ SET 
          "setPropertiesClause" ] { "alterFieldClauses" } | SET 
          "setPropertiesClause" { "alterFieldClauses" } | "alterFieldClauses" { 
          "alterFieldClauses" } )
     deleteRecordClause =  RECORD "QUOTED_STRING"
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
     propertyValueList =  "propertyValue" { "," "propertyValue" }
     addFieldClauseForAlter =  FIELD "QUOTED_STRING" [ AT POSITION 
          "INTEGER_LITERAL" ] [ SET "setPropertiesClause" ]
     alterFieldClauses =  ADD "addFieldClauseForAlter" | MODIFY 
          "modifyFieldClause" | DELETE "deleteFieldClause"
     propertyValue =  ( "QUOTED_STRING" | "INTEGER_LITERAL" | 
          "FLOATING_POINT_LITERAL" )
     modifyFieldClause =  FIELD "QUOTED_STRING" ( "renameClause" [ 
          "moveFieldToClause" ] [ SET "setPropertiesClause" ] | 
          "moveFieldToClause" [ SET "setPropertiesClause" ] | SET 
          "setPropertiesClause" )
     deleteFieldClause =  FIELD "QUOTED_STRING"
     moveFieldToClause =  MOVE TO POSITION "INTEGER_LITERAL"

Parameters

alterFlatFileCommand

Alter a flat file.

QUOTED_STRING

The name of the flat file to alter.

renameClause

Rename the flat file to the following quoted string.

alterPropertiesOrIconSetClause

Set the properties and/or reference icon set of the flat file.

importCopybookClause

Import a COBOL copybook.

QUOTED_STRING

The file containing the copybook.

alterRecordClauses

Add, modify, or drop a record of the flat file.

setPropertiesClause

Set the properties of the flat file, record, or field.

setReferenceIconSetClause

Set specified Icon Set.

unsetReferenceIconSetClause

Unset specified Icon Set.

excludeGroupsClause

Indicates that fields should not be created for group items.

excludeRedefinesClause

Indicates that fields should not be created for redefined items or items subordinate to redefined items.

storageBoundaryClause

Specifies the storage boundary for the data in the file.

onErrorClause

Specifies how the processing should continue if an error is encountered.

messageLevelClause

Specifies the types of messages to be shown.

addRecordClauseForAlter

Add a record named by the following quoted string.

modifyRecordClause

Modify a record specified by the following quoted string.

deleteRecordClause

Delete a record specified by the following quoted string.

propertyNameList

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

propertyNameList

Properties for FLAT_FILE:

Name: DATA_FILE_NAME
Type: STRING
Valid Values: Any valid character string in supported character set.
Default ''
The name of the "sampled" file.  Also the default data file value used in SQL*Loader maps and External Tables.

Name: IS_DELIMITED
Type: BOOLEAN
Valid Values: true, false, 1, 0
Default: true
True indicates that this flat file is delimited.  False indicates that its fields are defined by fixed lengths

Name: CHARACTERSET
Type: STRING
Valid Values: AL24UTFFSS,AR8ARABICMAC,AR8ARABICMACS,AR8ISO8859P6,AR8MSAWIN,AR8MSWIN1256,BLT8CP921,BLT8EBCDIC1112,BLT8MSWIN1257,BLT8PC775,CDN8PC863,CL8EBCDIC1025,CL8EBCDIC1025X,CL8ISO8859P5,CL8KOI8R,CL8MACCYRILLIC,CL8MACCYRILLICS,CL8MSWIN1251,D8EBCDIC273,DK8EBCDIC277,EE8EBCDIC870,EE8ISO8859P2,EE8MACCE,EE8MACCES,EE8MACCROATIAN,EE8MACCROATIANS,EE8MSWIN1250,EE8PC852,EL8EBCDIC875,EL8ISO8859P7,EL8MACGREEK,EL8MACGREEKS,EL8MSWIN1253,EL8PC437S,EL8PC737,EL8PC869,F8EBCDIC297,I8EBCDIC280,IS8MACICELANDIC,IS8MACICELANDICS,IS8PC861,IW8EBCDIC424,IW8ISO8859P8,IW8MACHEBREW,IW8MACHEBREWS,IW8MSWIN1255,JA16EBCDIC930,JA16EUC,JA16EUCYEN,JA16MACSJIS,JA16SJIS,JA16SJISYEN,JA16VMS,KO16KSC5601,LT8MSWIN921,N8PC865,NEE8ISO8859P4,RU8PC855,RU8PC866,S8EBCDIC278,SE8ISO8859P3,TH8MACTHAI,TH8MACTHAIS,TH8TISASCII,TR8EBCDIC1026,TR8MACTURKISH,TR8MACTURKISHS,TR8MSWIN1254,TR8PC857,US7ASCII,US8PC437,UTF8,WE8EBCDIC284,WE8EBCDIC285,WE8EBCDIC37,WE8EBCDIC37C,WE8EBCDIC500,WE8EBCDIC500C,WE8EBCDIC871,WE8ISO8859P1,WE8ISO8859P9,WE8MACROMAN8,WE8MACROMAN8S,WE8MSWIN1252,WE8PC850,WE8PC860,ZHS16CGB231280,ZHS16GBK,ZHS16MACCGB231280,ZHT16BIG5,ZHT16MSWIN950,ZHT32EUC
Default: WE8MSWIN1252
The character set of the data file.

Name: RECORD_DELIMITER
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
The character(s) which denote the end of a physical record in a data file.  A hex value may be entered by entering embedded single quotes twice as:   'x''0f''' (all are single quotes).  The outside single quote indicates a quoted string and the inside single quotes  single-quote  x  single-quote single-quote   0F  single-quote single-quote single-quote.  (Please note that this is not the FIELD_DELIMITER.

Name: RECORD_LENGTH
Type: NUMBER
Valid Values: 0+
Default: 0 (Records are delimited by default)
The length (in characters) of the records in the data file.

Name: RECORD_TYPE_COLUMN_NUMBER
Type: NUMBER
Valid Values: 0+
Default: 0
The column which contains the record type values for a delimited, multi-record type file.

Name: RECORD_TYPE_START_POSITION
Type: NUMBER
Valid Values: 0+
Default: 0
The starting position of the field (relative to 0) which contains the record type values for a fixed-length, multi-record type file.

Name: RECORD_TYPE_END_POSITION
Type: NUMBER
Valid Values: 0+
Default: 0
The ending position of the field (relative to 0) which contains the record type values for a fixed-length, multi-record type file.

propertyNameList

Name: NUMBER_OF_RECORDS_TO_SKIP
Type: NUMBER
Valid Values: 0 - 1000
Default: 0
The default number of records to skip when loading this file.

Name: FIELD_DELIMITER
Type: STRING
Valid Values: Any single character
Default: ',' (Comma)
The character to divide the fields in a delimited file.

Name: FIELD_LEFT_ENCLOSURE
Type: STRING
Valid Values: Any single character
Default: None
A character to enclose fields which may contain the field delimiter.

Name: FIELD_RIGHT_ENCLOSURE
Type: STRING
Valid Values: Any single character
Default: None
A character to enclose fields which may contain the field delimiter.

Name: NUMBER_OF_PHYSICAL_RECORDS_PER_LOGICAL
Type: Number
Valid Values: 0+
Default: 0
Set this value if you wish to concatenate a fixed number of physical records to form a single logical record.

Name: CONTINUE_IF_ENDS_WITH
Type: STRING
Valid Values: Any single character
Default: None
Set this value if you wish to concatenate a variable number of physical records to form a single logical record, determined by records ending with this character.

Name: CONTINUE_IF_STARTS_WITH
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: None
Set this value if you wish to concatenate a variable number of physical records to form a single logical record, determined by records beginning with this character.

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

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

Properties for RECORD:

Name: RECORD_TYPE_VALUE
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: None
This is a mandatory property for each record of a multi-record type file.  It is the string which will identify this record type in the data file.

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

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

Properties for FIELD:

Name: DATATYPE
Type: STRING
Valid Values: BYTEINT, CHAR, DATE, DECIMAL, DECIMAL EXTERNAL, DOUBLE, FLOAT, FLOAT EXTERNAL, GRAPHIC, GRAPHIC EXTERNAL, INTEGER, INTEGER EXTERNAL, INTEGER UNSIGNED, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, LONG VARRAW, RAW, SMALLINT, SMALLINT UNSIGNED, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP WITH TIME ZONE, VARCHAR, VARCHARC, VARRAW, VARRAWC, ZONED, ZONED EXTERNAL
Default: CHAR
This is the SQL*Loader data type for the field.

Name: MAXIMUM_LENGTH
Type: NUMBER
Valid Values: 0+
Default: 0
This is the maximum length of the field.

Name: LENGTH
Type: NUMBER
Valid Values: 0+
Default: 0
Depreciated.  This is the length of the field in a fixed length file.  This is the max length of the field in a delimited file.

Name: PRECISION
Type: NUMBER
Valid Values: Depends on data type
Default: 0
Precision of the field.

Name SCALE
Type: NUMBER
Valid Values: Depends on data type
Default: 0
Scale of the field

Name: START_POSITION
Type: NUMBER
Valid Values: 1+
Default: 1
The starting position of a field for a fixed length file.

Name: END_POSITION
Type: NUMBER
Valid Values: 1+
Default: 1
The ending position of a field for a fixed length file.

propertyNameList

Name: SQL_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, XMLTYPE
Default: DEFAULT.  This will derive the SQL_DATATYPE from the value of DATATYPE.
The data type which the field will be treated as in mapping and for External Tables.

Name: SQL_LENGTH
Type: NUMBER
Valid Values: 1 - 4000
Default: 0

Name: SQL_PRECISION
Type: NUMBER
Valid Values: 1 - 38
Default: 1

Name: SQL_SCALE
Type: NUMBER
Valid Values: -84 - 127
Default: 0

Name: MASK
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: None
This is the mask used to define the format of DATE fields in the data file.

Name: NULL_IF
Type: STRING
Valid Values: Either = or != followed by either 'BLANKS', a single quoted string, or a hexadecimal string
Default: None
If this condition is true for a field, the value loaded will be NULL.

Name: DEFAULT_IF
Type: STRING
Valid Values: Either = or != followed by either 'BLANKS', a single quoted string, or a hexadecimal string
Default: None
If this condition is true for a field, the value loaded will be either NULL or 0, dependent on data type.

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

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

propertyValueList

The values for the named properties.

addFieldClauseForAlter

Add a field to the record.

alterFieldClauses

Add, modify, or drop a field.

propertyValue

A property value.

modifyFieldClause

Modify the properties of a field specified by the following quoted string.

deleteFieldClause

Delete a field specified by the following quoted string.

moveFieldToClause

Move a field to a new position in the record.