[ Pobierz całość w formacie PDF ]
.Previous Field Terminated by WhitespaceIf the previous field is TERMINATED BY WHITESPACE, then all thewhitespace after the field acts as the delimiter.The next field starts at the nextnon-whitespace character.Figure 5-5 illustrates this case.Figure 5-5: Fields Terminated by WhitespaceField 1 TERMINATED Field 2 TERMINATEDBY WHITESPACE BY WHITESPACEa a a a b b b bThis situation occurs when the previous field is explicitly specified with theTERMINATED BY WHITESPACE clause, as shown in the example.It alsooccurs when you use the global FIELDS TERMINATED BY WHITESPACEclause.Optional Enclosure DelimitersLeading whitespace is also removed from a field when optional enclosuredelimiters are specified but not present.Whenever optional enclosure delimiters are specified, SQL*Loader scansforward, looking for the first delimiter.If none is found, then the first non-whitespace character signals the start of the field.SQL*Loader skips overwhitespace, eliminating it from the field.This situation is shown in Figure 5-6.Figure 5-6: Fields Terminated by Optional Enclosing DelimitersField 1 TERMINATED BY " , " Field 2 TERMINATED BY " , "OPTIONALLY ENCLOSED BY, ' " ' OPTIONALLY ENCLOSED BY ' " '" a a a a " , b b b b ,SQL*Loader Control File Reference 5-79Unlike the case when the previous field is TERMINATED BY WHITESPACE,this specification removes leading whitespace even when a starting positionis specified for the current field.Note: If enclosure delimiters are present, leading whitespace after theinitial enclosure delimiter is kept, but whitespace before this delimiter isdiscarded.See the first quote in FIELD1, Figure 5-6.Trailing WhitespaceTrailing whitespace is only trimmed from character-data fields that have apredetermined size.It is always trimmed from those fields.Enclosed FieldsIf a field is enclosed, or terminated and enclosed, like the first field shown inFigure 5-6, then any whitespace outside the enclosure delimiters is not part ofthe field.Any whitespace between the enclosure delimiters belongs to thefield, whether it is leading or trailing whitespace.Trimming Whitespace: SummaryTable 5-5 summarizes when and how whitespace is removed from input datafields when PRESERVE BLANKS is not specified.See the following section, Preserving Whitespace on page 5-81, for details on how to preventtrimming.5-80 Oracle8 Server UtilitiesTable 5-5: Trim TableSpecification Data Result Leading TrailingWhitespace WhitespacePresent(1) Present(1)Predetermined Size __aa__ __aa Y NTerminated __aa__, __aa__ YY(2)Enclosed  __aa__ __aa__ Y YTerminated and  __aa__ , __aa__ Y YEnclosedOptional Enclosure  __aa__ , __aa__ Y Y(present)Optional __aa__, aa__ N YEnclosure(absent)Previous Field __aa__ (3)aa(3) NTerminated byWhitespace(1)When an allow-blank field is trimmed, its value is null.(2) Except for fields that are TERMINATED BY WHITESPACE(3)Presence of trailing whitespace depends on the current field sspecification, as shown by the other entries in the table.Preserving WhitespaceTo prevent whitespace trimming in all CHAR, DATE, and NUMERICEXTERNAL fields, you specify PRESERVE BLANKS in the control file.Whitespace trimming is described in the previous section,  Trimming ofBlanks and Tabs on page 5-76.PRESERVE BLANKS KeywordPRESERVE BLANKS retains leading whitespace when optional enclosuredelimiters are not present.It also leaves trailing whitespace intact when fieldsare specified with a predetermined size.This keyword preserves tabs andblanks; for example, if the field__aa__,(where underscores represent blanks) is loaded with the following controlclause:TERMINATED BY  , OPTIONALLY ENCLOSED BY   SQL*Loader Control File Reference 5-81then both the leading whitespace and the trailing whitespace are retained ifPRESERVE BLANKS is specified.Otherwise, the leading whitespace istrimmed.Note: The word BLANKS is not optional.Both words must be specified.Terminated by WhitespaceWhen the previous field is terminated by whitespace, then PRESERVEBLANKS does not preserve the space at the beginning of the next field, unlessthat field is specified with a POSITION clause that includes some of thewhitespace.Otherwise, SQL*Loader scans past all whitespace at the end ofthe previous field until it finds a non-blank, non-tab character.Applying SQL Operators to FieldsA wide variety of SQL operators may be applied to field data with the SQLstring.This string may contain any combination of SQL expressions that arerecognized by Oracle as valid for the VALUES clause of an INSERT statement.In general, any SQL function that returns a single value may be used.See thesection  Expressions in the  Operators, Functions, Expressions, Conditionschapter in the Oracle8 Server SQL Reference.The column name and the name of the column in the SQL string must matchexactly, including the quotation marks, as in this example of specifying thecontrol file:LOAD DATAINFILE *APPEND INTO TABLE XXX( "LAST" position(1:7) char "UPPER(:\"LAST\)",FIRST position(8:15) char "UPPER(:FIRST)")BEGINDATAThomas AlbertSerge MatlovskyThe SQL string must be enclosed in double quotation marks.In the exampleabove, LAST must be in quotation marks because it is a SQL*Loaderkeyword.FIRST is not a SQL*Loader keyword and therefore does not requirequotation marks.To quote the column name in the SQL string, you mustescape it.5-82 Oracle8 Server UtilitiesThe SQL string appears after any other specifications for a given column.It isevaluated after any NULLIF or DEFAULTIF clauses, but before a DATE mask.It may not be used on RECNUM, SEQUENCE, CONSTANT, or SYSDATEfields.If the RDBMS does not recognize the string, the load terminates inerror.If the string is recognized, but causes a database error, the row thatcaused the error is rejected.Referencing FieldsTo refer to fields in the record, precede the field name with a colon (:) [ Pobierz całość w formacie PDF ]