When you are determining field positions, be alert for tabs in the data file.

Suppose you use the SQL*Loader advanced SQL string capabilities to load data from a formatted report. You would probably first look at a printed copy of the report, carefully measure all character positions, and then create your control file. In such a situation, it is highly likely that when you attempt to load the data, the load will fail with multiple "invalid number" and "missing field" errors.

These kinds of errors occur when the data contains tabs. When printed, each tab expands to consume several columns on the paper. In the data file, however, each tab is still only one character. As a result, when SQL*Loader reads the data file, the POSITION specifications are wrong.

To fix the problem, inspect the data file for tabs and adjust the POSITION specifications, or else use delimited fields.

This section describes using POSITION with multiple table loads.

In a multiple table load, you specify multiple INTO TABLE clauses. When you specify POSITION(*) for the first column of the first table, the position is calculated relative to the beginning of the logical record. When you specify POSITION(*) for the first column of subsequent tables, the position is calculated relative to the last column of the last table loaded.

Thus, when a subsequent INTO TABLE clause begins, the position is not set to the beginning of the logical record automatically. This allows multiple INTO TABLE clauses to process different parts of the same physical record. For an example, see Extracting Multiple Logical Records.

A logical record might contain data for one of two tables, but not both. In this case, you would reset POSITION. Instead of omitting the position specification or using POSITION(*+n) for the first field in the INTO TABLE clause, use POSITION(1) or POSITION(n).

This section shows examples using POSITION.

siteid  POSITION (*) SMALLINT 
siteloc POSITION (*) INTEGER 

If these were the first two column specifications, then siteid would begin in column 1, and siteloc would begin in the column immediately following.

ename  POSITION (1:20)  CHAR 
empno  POSITION (22-26) INTEGER EXTERNAL 
allow  POSITION (*+2)   INTEGER EXTERNAL TERMINATED BY "/" 

Column ename is character data in positions 1 through 20, followed by column empno, which is presumably numeric data in columns 22 through 26. Column allow is offset from the next position (27) after the end of empno by +2, so it starts in column 29 and continues until a slash is encountered.

A filler field, specified by BOUNDFILLER or FILLER is a data file mapped field that does not correspond to a database column.

Filler fields are assigned values from the data fields to which they are mapped.

Keep the following in mind regarding filler fields:

• The syntax for a filler field is same as that for a column-based field, except that a filler field's name is followed by FILLER.

• Filler fields have names, but they are not loaded into the table.

• Filler fields can be used as arguments to init_specs (for example, NULLIF and DEFAULTIF).

• Filler fields can be used as arguments to directives (for example, SID, OID, REF, and BFILE).

  To avoid ambiguity, if a Filler field is referenced in a directive, such as BFILE, and that field is declared in the control file inside of a column object, then the field name must be qualified with the name of the column object. This is illustrated in the following example:

  LOAD DATA 
  INFILE * 
  INTO TABLE BFILE1O_TBL REPLACE 
  FIELDS TERMINATED BY ',' 
  ( 
     emp_number char, 
     emp_info_b column object 
     ( 
     bfile_name FILLER char(12), 
     emp_b BFILE(constant "SQLOP_DIR", emp_info_b.bfile_name) NULLIF 
    emp_info_b.bfile_name = 'NULL' 
     ) 
  ) 
  BEGINDATA 
  00001,bfile1.dat, 
  00002,bfile2.dat, 
  00003,bfile3.dat, 
  

• Filler fields can be used in field condition specifications in NULLIF, DEFAULTIF, and WHEN clauses. However, they cannot be used in SQL strings.

• Filler field specifications cannot contain a NULLIF or DEFAULTIF clause.

• Filler fields are initialized to NULL if TRAILING NULLCOLS is specified and applicable. If another field references a nullified filler field, then an error is generated.

• Filler fields can occur anyplace in the data file, including inside the field list for an object or inside the definition of a VARRAY.

• SQL strings cannot be specified as part of a filler field specification, because no space is allocated for fillers in the bind array.

  Note:

  The information in this section also applies to specifying bound fillers by using BOUNDFILLER. The only exception is that with bound fillers, SQL strings can be specified as part of the field, because space is allocated for them in the bind array.

 field_1_count FILLER char,
 field_1 varray count(field_1_count)
 (
    filler_field1  char(2),
    field_1  column object
    (
      attr1 char(2),
      filler_field2  char(2),
      attr2 char(2),
    )
    filler_field3  char(3),
 )
 filler_field4 char(6)

The data type specification of a field tells SQL*Loader how to interpret the data in the field.

For example, a data type of INTEGER specifies binary data, while INTEGER EXTERNAL specifies character data that represents a number. A CHAR field can contain any character data.

Only one data type can be specified for each field; if a data type is not specified, then CHAR is assumed.

Before you specify the data type, you must specify the position of the field.

To find out how SQL*Loader data types are converted into Oracle data types, and obtain detailed information about each SQL*Loader data type, refer to "SQL*Loader Data Types."

Use this reference to understand how to use the nonportable data types with SQL*Loader.

• Categories of Nonportable Data Types
  Nonportable data types are grouped into two categories: value data types, and length-value data types.
• INTEGER(n)
  The SQL*Loader nonportable value data type INTEGER(n) is a length-specific integer field.
• SMALLINT
  The SQL*Loader nonportable value data type SMALLINT is a half-word binary integer.
• FLOAT
  The SQL*Loader nonportable value data type FLOAT is a single-precision, floating-point, binary number
• DOUBLE
  The SQL*Loader nonportable value data type DOUBLE is a double-precision floating-point binary number.
• BYTEINT
  The SQL*Loader nonportable value data type BYTEINT loads the decimal value of the binary representation of the byte.
• ZONED
  The SQL*Loader nonportable value data type ZONED is in zoned decimal format.
• DECIMAL
  
• VARGRAPHIC
  The SQL*Loader nonportable length-value data type VARGRAPHIC is a varying-length, double-byte character set (DBCS).
• VARCHAR
  The SQL*Loader nonportable length-value data type VARCHAR is a binary length subfield followed by a character string of the specified length.
• VARRAW
  The SQL*Loader nonportable length-value data type VARROW is a 2-byte binary length subfield, and a RAW string value subfield.
• LONG VARRAW
  The SQL*Loader nonportable length-value data type LONG VARRAW is a VARRAW with a 4-byte length subfield.

Use this reference to understand how to use the portable data types with SQL*Loader.

• Categories of Portable Data Types
  Portable data types are grouped into value data types, length-value data types, and character data types.
• CHAR
  
• Datetime and Interval
  The SQL*Loader portable value datatime data types (datetime) and interval data types ( intervals) are fields that record dates and time intervals.
• GRAPHIC
  The SQL*Loader portable value data type GRAPHIC has the data in the form of a double-byte character set (DBCS).
• GRAPHIC EXTERNAL
  The SQL*Loader portable value data type GRAPHIC EXTERNAL specifies graphic data loaded from external tables.
• Numeric EXTERNAL
  The SQL*Loader portable value numeric EXTERNAL data types are human-readable, character form data.
• RAW
  The SQL*Loader portable value RAW specifies a load of raw binary data.
• VARCHARC
  The portable length-value data type VARCHARC specifies character string lengths and sizes
• VARRAWC
  The portable length-value data type VARRAWC consists of a RAW string value subfield.
• Conflicting Native Data Type Field Lengths
  If you are loading different data types, then learn what rules SQL*Loader follows to manage conflicts in field length specifications.
• Field Lengths for Length-Value Data Types
  The field lengths for length-value SQL*Loader portable data types such as VARCHAR, VARCHARC, VARGRAPHIC, VARRAW, and VARRAWC is in bytes or characters.

SQL*Loader can perform most data type conversions automatically, but to avoid errors, you need to understand conversion rules.

The data type specifications in the control file tell SQL*Loader how to interpret the information in the data file. The server defines the data types for the columns in the database. The link between these two is the column name specified in the control file.

SQL*Loader extracts data from a field in the input file, guided by the data type specification in the control file. SQL*Loader then sends the field to the server to be stored in the appropriate column (as part of an array of row inserts).

SQL*Loader or the server does any necessary data conversion to store the data in the proper internal format. This includes converting data from the data file character set to the database character set when they differ.

The data type of the data in the file does not need to be the same as the data type of the column in the Oracle Database table. Oracle Database automatically performs conversions. However, you need to ensure that the conversion makes sense, and does not generate errors. For instance, when a data file field with data type CHAR is loaded into a database column with data type NUMBER, you must ensure that the contents of the character field represent a valid number.

Learn which conversions between Oracle Database data types and SQL*Loader control file datetime and interval data types are supported, and which are not.

The boundaries of CHAR, datetime, interval, or numeric EXTERNAL fields can also be marked by delimiter characters contained in the input data record.

The delimiter characters are specified using various combinations of the TERMINATED BY, ENCLOSED BY, and OPTIONALLY ENCLOSED BY clauses (the TERMINATED BY clause, if used, must come first). The delimiter specification comes after the data type specification.

For a description of how data is processed when various combinations of delimiter clauses are used, see How Delimited Data Is Processed.

• Syntax for Termination and Enclosure Specification
  The syntax for termination and enclosure specifications is described here.
• Delimiter Marks in the Data
  Sometimes the punctuation mark that is a delimiter must also be included in the data.
• Maximum Length of Delimited Data
  Delimited fields can require significant amounts of storage for the bind array.
• Loading Trailing Blanks with Delimiters
  You can load trailing blanks by specifying PRESERVE BLANKS, or you can declare data fields with delimiters, and add delimiters to the data files.

To specify delimiters, field definitions can use various combinations of the TERMINATED BY, ENCLOSED BY, and OPTIONALLY ENCLOSED BY clauses.

Review these topics to understand how SQL*Loader processes each case of these field definitions.

• Fields Using Only TERMINATED BY
  Data fields that use only TERMINATED BY are affected by the location of the delimiter.
• Fields Using ENCLOSED BY Without TERMINATED BY
  When data fields use ENCLOSED BY without TERMINATED BY, there is a sequence of processing that SQL*Loader uses for those fields.
• Fields Using ENCLOSED BY With TERMINATED BY
  When data fields use ENCLOSED BY with TERMINATED BY, there is a sequence of processing that SQL*Loader uses for those fields.
• Fields Using OPTIONALLY ENCLOSED BY With TERMINATED BY
  When data fields use OPTIONALLY ENCLOSED BY with TERMINATED BY, there is a sequence of processing that SQL*Loader uses for those fields.

A control file can specify multiple lengths for the character-data fields CHAR, DATE, and numeric EXTERNAL.

If conflicting lengths are specified, then one of the lengths takes precedence. A warning is also issued when a conflict exists. This section explains which length is used.

• Predetermined Size Fields
  With predetermined size fields, the lengths of fields are determined by the values you specify. If there is a conflict in specifications, then the field length specification is used.
• Delimited Fields
  With delimited fields, the lengths of fields are determined by field semantics and position specifications.
• Date Field Masks
  The length of DATE data type fields depends on the format pattern specified in the mask, but can be overridden by position specifications or length specifications.

The BLANKS parameter makes it possible to determine if a field of unknown length is blank.

For example, use the following clause to load a blank field as null:

full_fieldname ... NULLIF column_name=BLANKS 

The BLANKS parameter recognizes only blanks, not tabs. It can be used in place of a literal string in any field comparison. The condition is true whenever the column is entirely blank.

The BLANKS parameter also works for fixed-length fields. Using it is the same as specifying an appropriately sized literal string of blanks. For example, the following specifications are equivalent:

fixed_field CHAR(2) NULLIF fixed_field=BLANKS 
fixed_field CHAR(2) NULLIF fixed_field="  " 

There can be more than one blank in a multibyte character set. It is a good idea to use the BLANKS parameter with these character sets instead of specifying a string of blank characters.

The character string will match only a specific sequence of blank characters, while the BLANKS parameter will match combinations of different blank characters. For more information about multibyte character sets, see Multibyte (Asian) Character Sets.

Data fields that are compared to literal strings can have blank padding to the string.

When a data field is compared to a literal string that is shorter than the data field, the string is padded. Character strings are padded with blanks. For example:

NULLIF (1:4)=" " 

This example compares the data in position 1:4 with 4 blanks. If position 1:4 contains 4 blanks, then the clause evaluates as true.

Hexadecimal strings are padded with hexadecimal zeros, as in the following clause:

NULLIF (1:4)=X'FF' 

This clause compares position 1:4 to hexadecimal 'FF000000'.

The information in this section applies only to fields specified with one of the character-data data types.

• CHAR data type

• Datetime and interval data types

• Numeric EXTERNAL data types:

  • INTEGER EXTERNAL

  • FLOAT EXTERNAL

  • (packed) DECIMAL EXTERNAL

  • ZONED (decimal) EXTERNAL

    Note:

    Although VARCHAR and VARCHARC fields also contain character data, these fields are never trimmed. These fields include all whitespace that is part of the field in the data file.

This section describes specifying field length.

There are two ways to specify field length. If a field has a constant length that is defined in the control file with a position specification or the data type and length, then it has a predetermined size. If a field's length is not known in advance, but depends on indicators in the record, then the field is delimited, using either enclosure or termination delimiters.

If a position specification with start and end values is defined for a field that also has enclosure or termination delimiters defined, then only the position specification has any effect. The enclosure and termination delimiters are ignored.

• Predetermined Size Fields
  Fields that have a predetermined size are specified with a starting position and ending position, or with a length.
• Delimited Fields
  Delimiters are characters that demarcate field boundaries.

This section describes the relative positioning of fields.

SQL*Loader determines the starting position of a field in the following situations:

• No Start Position Specified for a Field
  When a starting position is not specified for a field, it begins immediately after the end of the previous field.
• Previous Field Terminated by a Delimiter
  If the previous field (Field 1) is terminated by a delimiter, then the next field begins immediately after the delimiter.
• Previous Field Has Both Enclosure and Termination Delimiters
  When a field is specified with both enclosure delimiters and a termination delimiter, then the next field starts after the termination delimiter.

This section describes leading whitespace.

In Figure 10-4, both fields are stored with leading whitespace. Fields do not include leading whitespace in the following cases:

• When the previous field is terminated by whitespace, and no starting position is specified for the current field

• When optional enclosure delimiters are specified for the field, and the enclosure delimiters are not present

These cases are illustrated in the following sections.

• Previous Field Terminated by Whitespace 
  If the previous field is TERMINATED BY WHITESPACE, then all whitespace after the field acts as the delimiter.
• Optional Enclosure Delimiters
  Leading whitespace is also removed from a field when optional enclosure delimiters are specified but not present.

Trailing whitespace is always trimmed from character-data fields that have a predetermined size.

These are the only fields for which trailing whitespace is always trimmed.

This section describes trimming enclosed fields.

If a field is enclosed, or terminated and enclosed, like the first field shown in Figure 10-6, then any whitespace outside the enclosure delimiters is not part of the field. Any whitespace between the enclosure delimiters belongs to the field, whether it is leading or trailing whitespace.

To refer to fields in the record, precede the field name with a colon (:).

Field values from the current record are substituted. A field name preceded by a colon (:) in a SQL string is also referred to as a bind variable. Note that bind variables enclosed in single quotation marks are treated as text literals, not as bind variables.

The following example illustrates how a reference is made to both the current field and to other fields in the control file. It also illustrates how enclosing bind variables in single quotation marks causes them to be treated as text literals. Be sure to read the notes following this example to help you fully understand the concepts it illustrates.

LOAD DATA
INFILE *
APPEND INTO TABLE YYY
(
 field1  POSITION(1:6) CHAR "LOWER(:field1)"
 field2  CHAR TERMINATED BY ','
         NULLIF ((1) = 'a') DEFAULTIF ((1)= 'b')
         "RTRIM(:field2)",
 field3  CHAR(7) "TRANSLATE(:field3, ':field1', ':1')",
 field4  COLUMN OBJECT
 (
  attr1  CHAR(3) NULLIF field4.attr2='ZZ' "UPPER(:field4.attr3)",
  attr2  CHAR(2),
  attr3  CHAR(3)  ":field4.attr1 + 1"
 ),
 field5  EXPRESSION "MYFUNC(:FIELD4, SYSDATE)"
)
BEGINDATA
ABCDEF1234511  ,:field1500YYabc
abcDEF67890    ,:field2600ZZghl

This section describes the common uses of SQL operators in field specifications.

SQL operators are commonly used for the following tasks:

• Loading external data with an implied decimal point:

     field1 POSITION(1:9) DECIMAL EXTERNAL(8) ":field1/1000"

• Truncating fields that could be too long:

     field1 CHAR TERMINATED BY "," "SUBSTR(:field1, 1, 10)"

This section describes combining SQL operators.

Multiple operators can also be combined, as in the following examples:

field1 POSITION(*+3) INTEGER EXTERNAL
       "TRUNC(RPAD(:field1,6,'0'), -2)"
field1 POSITION(1:8) INTEGER EXTERNAL
       "TRANSLATE(RTRIM(:field1),'N/A', '0')"
field1 CHAR(10)
       "NVL( LTRIM(RTRIM(:field1)), 'unknown' )"

When a SQL string is used with a date mask, the date mask is evaluated after the SQL string.

Consider a field specified as follows:

field1 DATE "dd-mon-yy" "RTRIM(:field1)"

SQL*Loader internally generates and inserts the following:

TO_DATE(RTRIM(<field1_value>), 'dd-mon-yyyy')

Note that when using the DATE field data type with a SQL string, a date mask is required. This is because SQL*Loader assumes that the first quoted string it finds after the DATE parameter is a date mask. For instance, the following field specification would result in an error (ORA-01821: date format not recognized):

field1 DATE "RTRIM(TO_DATE(:field1, 'dd-mon-yyyy'))"

In this case, a simple workaround is to use the CHAR data type.

It is possible to use the TO_CHAR operator to store formatted dates and numbers.

For example:

field1 ... "TO_CHAR(:field1, '$09999.99')"

This example could store numeric input data in formatted form, where field1 is a character column in the database. This field would be stored with the formatting characters (dollar sign, period, and so on) already in place.

You have even more flexibility, however, if you store such values as numeric quantities or dates. You can then apply arithmetic functions to the values in the database, and still select formatted values for your reports.

An example of using the SQL string to load data from a formatted report is shown in case study 7, Extracting Data from a Formatted Report. (See SQL*Loader Case Studies for information on how to access case studies.)

The ANYDATA database type can contain data of different types.

To load the ANYDATA type using SQL*loader, it must be explicitly constructed by using a function call. The function is called using support for SQL strings as has been described in this section.

For example, suppose you have a table with a column named miscellaneous which is of type ANYDATA. You could load the column by doing the following, which would create an ANYDATA type containing a number.

LOAD DATA
INFILE *
APPEND INTO TABLE  ORDERS
(
miscellaneous CHAR "SYS.ANYDATA.CONVERTNUMBER(:miscellaneous)"
)
BEGINDATA
4

There can also be more complex situations in which you create an ANYDATA type that contains a different type depending upon the values in the record. To do this, you could write your own PL/SQL function that would determine what type should be in the ANYDATA type, based on the value in the record, and then call the appropriate ANYDATA.Convert*() function to create it.