[ Pobierz całość w formacie PDF ]
.DEFAULTIF field_condition5-74 Oracle8 Server Utilities A column may have both a NULLIF clause and a DEFAULTIF clause,although this often would be redundant.Note: The same effects can be achieved with the SQL string and theDECODE function.See Applying SQL Operators to Fields onpage 5-82NULLIF KeywordUse the NULLIF keyword after the datatype and optional delimiterspecification, followed by a condition.The condition has the same format asthat specified for a WHEN clause.The column s value is set to null if thecondition is true.Otherwise, the value remains unchanged.NULLIF field_conditionThe NULLIF clause may refer to the column that contains it, as in thefollowing example:COLUMN1 POSITION(11:17) CHAR NULLIF (COLUMN1 = unknown )This specification may be useful if you want certain data values to be replacedby nulls.The value for a column is first determined from the datafile.It is thenset to null just before the insert takes place.Case 6 on page 4-20 includes moreexamples of the NULLIF clause.Note: The same effect can be achieved with the SQL string and the NVLfunction.See Applying SQL Operators to Fields on page 5-82.Null Columns at the End of a RecordWhen the control file specifies more fields for a record than are present in therecord, SQL*Loader must determine whether the remaining (specified)columns should be considered null or whether an error should be generated.The TRAILING NULLCOLS clause, described on page 5-39, tells SQL*Loaderhow to proceed in this case.SQL*Loader Control File Reference 5-75 Loading All-Blank FieldsTotally blank fields for numeric or DATE fields cause the record to be rejected.To load one of these fields as null, use the NULLIF clause with the BLANKSkeyword, as described in the section Comparing Fields to BLANKS onpage 5-42.Case 6 on page 4-20 shows how to load all-blank fields as null withthe NULLIF clause.If an all-blank CHAR field is surrounded by enclosure delimiters, then theblanks within the enclosures are loaded.Otherwise, the field is loaded as null.More details on whitespace trimming in character fields are presented in thefollowing section.Trimming of Blanks and TabsBlanks and tabs constitute whitespace.Depending on how the field is specified,whitespace at the start of a field (leading whitespace) and at the end of a field(trailing whitespace) may, or may not be, included when the field is insertedinto the database.This section describes the way character data fields arerecognized, and how they are loaded.In particular, it describes the conditionsunder which whitespace is trimmed from fields.Note: Specifying PRESERVE BLANKS changes this behavior.Read Preserving Whitespace on page 5-81 for more information.DatatypesThe information in this section applies only to fields specified with one of thecharacter-data datatypes:" CHAR datatype" DATE datatype" numeric EXTERNAL datatypes:- INTEGER EXTERNAL- FLOAT EXTERNAL- (packed) DECIMAL EXTERNAL- ZONED (decimal) EXTERNAL5-76 Oracle8 Server Utilities VARCHAR FieldsAlthough VARCHAR fields also contain character data, these fields are nevertrimmed.A VARCHAR field includes all whitespace that is part of the field inthe datafile.Field Length SpecificationsThere are two ways to specify field length.If a field has a constant length thatis defined in the control file, then it has a predetermined size.If a field s length isnot known in advance, but depends on indicators in the record, then the fieldis delimited.Predetermined Size FieldsFields that have a predetermined size are specified with a starting positionand ending position, or with a length, as in the following examples:loc POSITION(19:31)loc CHAR(14)In the second case, even though the field s exact position is not specified, thefield s length is predetermined.Delimited FieldsDelimiters are characters that demarcate field boundaries.Enclosuredelimiters surround a field, like the quotes in: __aa__ where __ represents blanks or tabs.Termination delimiters signal the end ofa field, like the comma in:__aa__,Delimiters are specified with the control clauses TERMINATED BY andENCLOSED BY, as shown in the following examples:loc POSITION(19) TERMINATED BY , loc POSITION(19) ENCLOSED BY loc TERMINATED BY . OPTIONALLY ENCLOSED BY |Combining Delimiters with Predetermined SizeIf predetermined size is specified for a delimited field, and the delimiter is notfound within the boundaries indicated by the size specification; then an erroris generated.For example, if you specify:loc POSITION(19:31) CHAR TERMINATED BY , SQL*Loader Control File Reference 5-77 and no comma is found between positions 19 and 31 of the input record, thenthe record is rejected.If a comma is found, then it delimits the field.Relative Positioning of FieldsWhen a starting position is not specified for a field, it begins immediatelyafter the end of the previous field.Figure 5-2 illustrates this situation whenthe previous field has a predetermined size.Figure 5-2: Relative Positioning After a Fixed FieldField 1 CHAR(9) Field 2 TERMINATED BY ","a a a a b b b b ,If the previous field is terminated by a delimiter, then the next field beginsimmediately after the delimiter, as shown in Figure 5-3.Figure 5-3: Relative Positioning After a Delimited FieldField 1 TERMINATED BY "," Field 2 TERMINATED BY ","a a a a , b b b b ,When a field is specified both with enclosure delimiters and a terminationdelimiter, then the next field starts after the termination delimiter, as shown inFigure 5-4.If a non-whitespace character is found after the enclosuredelimiter, but before the terminator, then SQL*Loader generates an error.Figure 5-4: Relative Positioning After Enclosure DelimitersField 1 TERMINATED BY ","ENCLOSED BY ' " ' Field 2 TERMINATED BY ","" a a a a " , b b b b ,5-78 Oracle8 Server Utilities Leading WhitespaceIn Figure 5-4, both fields are stored with leading whitespace [ Pobierz całość w formacie PDF ]