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

OMBRETRIEVE FLAT_FILE

Purpose

Retrieve details of a flat file.

Prerequisites

Create and change context to a flat file module.

Syntax

retrieveFlatFileCommand =  OMBRETRIEVE FLAT_FILE "QUOTED_STRING" ( 
          "retrieveFlatFileClause" | "retrieveFlatFileObjectsClause" )
     retrieveFlatFileClause =  GET ( "getPropertiesClause" | "getRecordsClause" 
          | "getReferenceIconSetClause" )
     retrieveFlatFileObjectsClause =  RECORD "QUOTED_STRING" ( ( GET 
          "getPropertiesClause" ) | "getFieldsClause" | "retrieveFieldClause" )
     getPropertiesClause =  PROPERTIES "(" "propertyNameList" ")"
     getRecordsClause =  RECORDS
     getReferenceIconSetClause =  ( REF | REFERENCE ) ICONSET
     getFieldsClause =  GET FIELDS
     retrieveFieldClause =  "getFieldByNameClause" | "getFieldAtPositionClause"
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
     getFieldByNameClause =  FIELD "QUOTED_STRING" GET "getPropertiesClause"
     getFieldAtPositionClause =  GET FIELD AT POSITION "INTEGER_LITERAL"

Parameters

retrieveFlatFileCommand

Retrieve the details of a flat file.

QUOTED_STRING

The name of a flat file in quotes.

retrieveFlatFileClause

Retrieve properties of the flat file.

retrieveFlatFileObjectsClause

Retrieve details of the flat file's records and fields.

getPropertiesClause

Retrieve specified properties from the flat file, record, or field.

getRecordsClause

Retrieve a list of records from the flat file.

getReferenceIconSetClause

Get the referenced icon set

getFieldsClause

Retrieve a list of fields from the record.

retrieveFieldClause

Identify a specific field to retrieve properties from.

propertyNameList

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

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

getFieldByNameClause

Identify a specific field by its name.

getFieldAtPositionClause

Identify a specific field by its position in the record.

See Also

OMBRETRIEVE