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
Notes About This Example:
In the following line,
:field1 is not enclosed in single quotation marks and is therefore interpreted as a bind variable:
field1 POSITION(1:6) CHAR "LOWER(:field1)"
In the following line,
':1' are enclosed in single quotation marks and are therefore treated as text literals and passed unchanged to the
field3 CHAR(7) "TRANSLATE(:field3, ':field1', ':1')"
For more information about the use of quotation marks inside quoted strings, see "Specifying File Names and Object Names".
For each input record read, the value of the field referenced by the bind variable will be substituted for the bind variable. For example, the value
ABCDEF in the first record is mapped to the first field
:field1. This value is then passed as an argument to the
A bind variable in a SQL string need not reference the current field. In the preceding example, the bind variable in the SQL string for the
field4.attr1 field references the
field4.attr3 field. The
field4.attr1 field is still mapped to the values 500 and NULL (because the
NULLIF field4.attr2='ZZ' clause is
TRUE for the second record) in the input records, but the final values stored in its corresponding columns are ABC and GHL.
field4.attr3 field is mapped to the values ABC and GHL in the input records, but the final values stored in its corresponding columns are 500 + 1 = 501 and NULL because the SQL expression references
field4.attr1. (Adding 1 to a NULL field still results in a NULL field.)
field5 field is not mapped to any field in the input record. The value that is stored in the target column is the result of executing the
MYFUNC PL/SQL function, which takes two arguments. The use of the
EXPRESSION parameter requires that a SQL string be used to compute the final value of the column because no input data is mapped to the field.