If the column's value is read from the datafile, the data field that contains the column's value is specified. In this case, the column specification includes a column name that identifies a column in the database table, and a field specification that describes a field in a data record.
The field specification includes position, datatype, null restrictions, and defaults. It is not necessary to specify all attributes when loading column objects. Any missing attributes will be set to NULL. Filler fields have names but they are not loaded into the table. Also, filler fields can occur anyplace in the data file. A CHAR field, however, can contain any character data. You may specify one datatype for each field; if unspecified, CHAR is assumed. The position may either be stated explicitly or relative to the preceding field.
The first character position in a logical record is 1. If you omit end, the length of the field is derived from the datatype in the datafile. Note that CHAR data specified without start or end is assumed to be length 1. If it is impossible to derive a length from the datatype, an error message is issued. Therefore, it starts in column 28 and continues until a slash is encountered. When you are determining field positions, be alert for TABs in the datafile.
The load then fails 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 datafile, however, each Tab is still only one character.
The use of delimiters to specify relative positioning of fields is discussed in detail in Specifying Delimiters. For an example, see the second example in Extracting Multiple Logical Records. A logical record may contain data for one of two tables, but not both. The remainder of this section details important ways to make use of that behavior.
Some data storage and transfer media have fixed-length physical records. When the data records are short, more than one can be stored in a single, physical record to use the storage space efficiently. For example, assume the data is as follows:.
The same record could be loaded with a different specification. The following control file uses relative positioning instead of fixed positioning.
Instead, scanning continues where it left off. A single datafile might contain records in a variety of formats. A record ID field distinguishes between the two formats. Department records have a "1" in the first column, while employee records have a "2". The following control file uses exact positioning to load this data:. The records in the previous example could also be loaded as delimited data.
The following control file could be used:. This keyword causes field scanning to start over at column 1 when checking for data that matches the second format. The following functions are described:. The LOAD keyword is required in this situation. The SKIP keyword is not permitted. In addition, no memory is required for a bind array. This is the simplest form of generated data. It does not vary during the load, and it does not vary between loads.
It is converted, as necessary, to the database column type. You may enclose the value within quotation marks, and you must do so if it contains white space or reserved words. Be sure to specify a legal value for the target column. If the value is bad, every record is rejected. To set a column to null, do not specify that column at all.
Oracle automatically sets that column to null when loading the record. Use the RECNUM keyword after a column name to set that column to the number of the logical record from which that record was loaded. Records are counted sequentially from the beginning of the first datafile, starting with record 1.
Thus it increments for records that are discarded, skipped, rejected, or loaded. If the column is of type CHAR, then the date is loaded in the form ' dd-mon-yy. If the system date is loaded into a DATE column, then it can be accessed in a variety of forms that include the time and the date. It does not increment for records that are discarded or skipped.
MAX The sequence starts with the current maximum value for the column plus the increment. If a record is rejected that is, it has a format error or causes an Oracle error , the generated sequence numbers are not reshuffled to mask this.
If four rows are assigned sequence numbers 10, 12, 14, and 16 in a particular column, and the row with 12 is rejected; the three rows inserted are numbered 10, 14, and 16, not 10, 12, This allows the sequence of inserts to be preserved despite data errors.
When you correct the rejected data and reinsert it, you can manually set the columns to agree with the sequence. Because a unique sequence number is generated for each logical input record, rather than for each table insert, the same sequence number can be used when inserting data into multiple tables.
This is frequently useful behavior. For example, your data format might define three logical records in every input record. To generate sequence numbers for these records, you must generate unique numbers for each of the three inserts.
There is a simple technique to do so. Use the number of table-inserts per record as the sequence increment and start the sequence numbers for each insert with successive numbers. Suppose you want to load the following department names into the DEPT table.
Each input record contains three department names, and you want to generate the department numbers automatically.
You could use the following control file entries to generate unique department numbers:. They all use 3 as the sequence increment the number of department names in each record. This control file loads Accounting as department number 1, Personnel as 2, and Manufacturing as 3.
The sequence numbers are then incremented for the next record, so Shipping loads as 4, Purchasing as 5, and so on. These datatypes are grouped into portable and nonportable datatypes. Within each of these two groups, the datatypes are subgrouped into length-value datatypes and value datatypes.
The main grouping, portable versus nonportable, refers to the platform dependency of the datatype. This issue arises due to a number of platform specifics such as differences in the byte ordering schemes of different platforms big-endian versus little-endian , differences in how many bits a particular platform is bit, bit, bit , differences in signed number representation schemes 2's complement versus 1's complement , and so on.
Note that not all of these problems apply to all nonportable datatypes. VALUE datatypes assume that a datafield has a single part. The length subfield specifies the length of the value subfield. The data is a full-word binary integer unsigned. The length of the field is the length of a full-word integer on your system.
This length cannot be overridden in the control file. The data is a half-word binary integer unsigned. The length of the field is the length of a half-word integer on your system. One way to determine its length is to make a small control file with no data and look at the resulting log file. See your Oracle operating system-specific documentation for details. The data is a single-precision, floating-point, binary number. The length of the field is the length of a single-precision, floating-point binary number on your system.
The data is a double-precision, floating-point binary number. The length of the field is the length of a double-precision, floating-point binary number on your system. The decimal value of the binary representation of the byte is loaded. For example, the input character x"1C" is loaded as ZONED data is in zoned decimal format: a string of decimal digits, one per byte, with the sign included in the last byte. The length of this field is equal to the precision number of digits that you specify.
In this syntax, precision is the number of digits in the number, and scale if given is the number of digits to the right of the implied decimal point.
The following example specifies an 8-digit integer starting at position DECIMAL data is in packed decimal format: two digits per byte, except for the last byte, which contains a digit and sign. The default is zero indicating an integer.
Scale can be greater than the number of digits but cannot be negative. In the data record, this field would take up 4 bytes. The data is a varying-length, double-byte character string.
It consists of a length subfield followed by a string of double-byte characters DBCS. The length of the current field is given in the first 2 bytes. This length is a count of graphic double-byte characters. Therefore, it is multiplied by 2 to determine the number of bytes to read. The maximum length specifies the number of graphic double-byte characters.
It is multiplied by 2 to determine the maximum length of the field in bytes. It is a good idea to specify a maximum length for such fields whenever possible, to minimize memory requirements. See Determining the Size of the Bind Array for more details. Both start and end identify single-character byte positions in the file. See Position Specification for more information about position specifications. It consists of a binary length subfield followed by a character string of the specified length.
A maximum length specified in the control file does not include the size of the length subfield. The default buffer size is 4 Kb. These fields can be delimited and can have lengths or maximum lengths specified in the control file. The data field contains character data. If no length is given, CHAR data is assumed to have a length of 1. A field of datatype CHAR may also be variable-length that is delimited or enclosed.
See Specifying Delimiters. This guarantees that a large enough buffer is allocated for the value and is necessary even if the data is delimited or enclosed. The data field contains character data that should be converted to an Oracle date using the specified date mask. Attention: Whitespace is ignored and dates are parsed from left to right unless delimiters are present.
The length specification is optional, unless a varying-length date mask is specified. In the preceding example, the date mask specifies a fixed-length date format of 11 characters. But, with a specification such as.
In this case, a length must be specified. Similarly, a length is required for any Julian dates date mask "J". A field length is required any time the length of the date string could exceed the length of the mask that is, the count of characters in the mask. It is a good idea to specify the length whenever you use a mask, unless you are absolutely sure that the length of the data is less than, or equal to, the length of the mask.
Either of these overrides the length derived from the mask. The mask may be any valid Oracle date mask. If you omit the mask, the default Oracle date mask of "dd-mon-yy" is used. The length must be enclosed in parentheses and the mask in quotation marks. A field of datatype DATE may also be specified with delimiters.
For more information, see Loading All-Blank Fields. The data is a string of double-byte characters DBCS. That value is multiplied by 2 to find the length of the field in bytes. The syntax for this datatype is:. For example, let [ ] represent shift-in and shift-out characters, and let represent any double-byte character. These datatypes are the human-readable, character form of numeric data. Length is optional, but if specified, overrides position.
Note: The data is a number in character form, not binary representation. Both "5. When raw, binary data is loaded "as is" into a RAW database column, it is not converted by the Oracle database server.
It cannot be loaded into a DATE or number column. The length of this field is the number of bytes specified in the control file. This length is limited only by the length of the target column in the database and by memory resources.
RAW datafields can not be delimited. If multiple lengths are specified and they conflict, then one of the lengths takes precedence. A warning is issued when a conflict exists. The following rules determine which field length is used:. In this case, the log file shows the actual length used under the heading "Len" in the column table:. The server defines the datatypes for the columns in the database. The link between these two is the column name specified in the control file.
The server does any necessary data conversion to store the data in the proper internal format. It does not do datatype conversion when loading nested tables as a separate table from the parent. The datatype of the data in the file does not necessarily need to be the same as the datatype of the column in the Oracle table.
Oracle automatically performs conversions, but you need to ensure that the conversion makes sense and does not generate errors. For instance, when a datafile field with datatype CHAR is loaded into a database column with datatype NUMBER, you must make sure that the contents of the character field represent a valid number. You indicate how the field is delimited by using a delimiter specification after specifying the datatype. If the terminator delimiter is found in the first column position, the field is null.
Then the current position is advanced until no more adjacent whitespace characters are found. This allows field values to be delimited by varying amounts of whitespace.
Enclosed fields are read by skipping whitespace until a nonwhitespace character is encountered. If that character is the delimiter, then data is read up to the second delimiter. Any other character causes an error. If two delimiter characters are encountered next to each other, a single occurrence of the delimiter character is used in the data value.
However, if the field consists of just two delimiter characters, its value is null. The syntax for delimiter specifications is:. BY An optional keyword for readability. If the data is not enclosed, the data is read as a terminated field. X'hexstr' The delimiter is a string that has the value specified by X'hexstr' in the character encoding scheme, such as X'1F' equivalent to 31 decimal.
AND This keyword specifies a trailing enclosure delimiter that may be different from the initial enclosure delimiter. If the AND clause is not present, then the initial and trailing delimiters are assumed to be the same.
Only valid when loading data from a LOB file. Sometimes the same punctuation mark that is a delimiter also needs to be included in the data. To make that possible, two adjacent delimiter characters are interpreted as a single occurrence of the character, and this character is included in the data.
For example, this data:. For this reason, problems can arise when adjacent fields use the same delimiters. For example, with the following specification:. The default maximum length of delimited data is bytes. Therefore, delimited fields can require significant amounts of storage for the bind array. A good policy is to specify the smallest possible maximum value. See Determining the Size of the Bind Array. Trailing blanks can only be loaded with delimited datatypes.
If conflicting lengths are specified, one of the lengths takes precedence. A warning is also issued when a conflict exists.
This section explains which length is used. If you specify a starting position and ending position for one of these fields, then the length of the field is determined by these specifications. If you specify a length as part of the datatype and do not give an ending position, the field has the given length.
Log File contains a detailed summary of the load, including a description of any errors that occurred during the load. It uses an implicit truncate of the table to replace existing data. It does not look at specific rows but rather removes all rows and inserts new ones, even if the new data is the same as the original data. In Scripts. In 12c Rac. Load data from multiple data files during the same load session. Load data into multiple tables during the same load session.
Specify the character set of the data. Add a comment. Active Oldest Votes. A simple NVL function call does the job. All rights reserved. Improve this answer. Littlefoot Littlefoot Hi Littlefoot, Thanks for details, I tried the suggested option it worked correctly. I am able to insert all in 9 mins. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password.
Normally, the specified name must be the name of an Oracle-supported character set. However, because you are allowed to set up data using the byte order of the system where you create the datafile, the data in the datafile can be either big endian or little endian. Therefore, a different character set name UTF16 is used. It is possible to specify different character sets for different input datafiles. If the control file character set is different from the datafile character set, keep the following issue in mind.
To ensure that the specifications are correct, you may prefer to specify hexadecimal strings, rather than character string values. If hexadecimal strings are used with a datafile in the UTF Unicode encoding, the byte order is different on a big endian versus a little endian system.
For example, "," comma in UTF on a big endian system is X'c'. On a little endian system it is X'2c00'. This allows the same syntax to be used in the control file on both a big endian and a little endian system. For example, the specification CHAR 10 in the control file can mean 10 bytes or 10 characters.
These are equivalent if the datafile uses a single-byte character set. However, they are often different if the datafile uses a multibyte character set. To avoid insertion errors caused by expansion of character strings during character set conversion, use character-length semantics in both the datafile and the target database columns. Byte-length semantics are the default for all datafiles except those that use the UTF16 character set which uses character-length semantics by default.
It is possible to specify different length semantics for different input datafiles. The following datatypes use byte-length semantics even if character-length semantics are being used for the datafile, because the data is binary, or is in a special binary-encoded form in the case of ZONED and DECIMAL:.
This is necessary to handle datafiles that have a mix of data of different datatypes, some of which use character-length semantics, and some of which use byte-length semantics. The SMALLINT length field takes up a certain number of bytes depending on the system usually 2 bytes , but its value indicates the length of the character string in characters. Character-length semantics in the datafile can be used independent of whether or not character-length semantics are used for the database columns.
Therefore, the datafile and the database columns can use either the same or different length semantics. Loads are interrupted and discontinued for a number of reasons. Additionally, when an interrupted load is continued, the use and value of the SKIP parameter can vary depending on the particular case. The following sections explain the possible scenarios. In a conventional path load, data is committed after all data in the bind array is loaded into all tables. If the load is discontinued, only the rows that were processed up to the time of the last commit operation are loaded.
There is no partial commit of data. In a direct path load, the behavior of a discontinued load varies depending on the reason the load was discontinued. This means that when you continue the load, the value you specify for the SKIP parameter may be different for different tables. If a fatal error is encountered, the load is stopped and no data is saved unless ROWS was specified at the beginning of the load. In that case, all data that was previously committed is saved.
This means that the value of the SKIP parameter will be the same for all tables. When a load is discontinued, any data already loaded remains in the tables, and the tables are left in a valid state. If the conventional path is used, all indexes are left in a valid state.
If the direct path load method is used, any indexes that run out of space are left in an unusable state. You must drop these indexes before the load can continue. You can re-create the indexes either before continuing or after the load completes.
Other indexes are valid if no other errors occurred. See Indexes Left in an Unusable State for other reasons why an index might be left in an unusable state. Use this information to resume the load where it left off.
To continue the discontinued load, use the SKIP parameter to specify the number of logical records that have already been processed by the previous load. At the time the load is discontinued, the value for SKIP is written to the log file in a message similar to the following:. This message specifying the value of the SKIP parameter is preceded by a message indicating why the load was discontinued.
Note that for multiple-table loads, the value of the SKIP parameter is displayed only if it is the same for all tables. However, there may still be situations in which you may want to do so. At some point, when you want to combine those multiple physical records back into one logical record, you can use one of the following clauses, depending on your data:. In the following example, integer specifies the number of physical records to combine.
For example, two records might be combined if a pound sign were in byte position 80 of the first record. If any other character were there, the second record would not be added to the first. If the condition is true in the current record, then the next physical record is read and concatenated to the current physical record, continuing until the condition is false.
If the condition is false, then the current physical record becomes the last physical record of the current logical record. THIS is the default. If the condition is true in the next record, then the current physical record is concatenated to the current logical record, continuing until the condition is false. For the equal operator, the field and comparison string must match exactly for the condition to be true. For the not equal operator, they may differ in any character.
This test is similar to THIS, but the test is always against the last nonblank character. If the last nonblank character in the current physical record meets the test, then the next physical record is read and concatenated to the current physical record, continuing until the condition is false.
If the condition is false in the current record, then the current physical record is the last physical record of the current logical record. Specifies the starting and ending column numbers in the physical record. Column numbers start with 1. Either a hyphen or a colon is acceptable start - end or start : end. If you omit end, the length of the continuation field is the length of the byte string or character string. If you use end, and the length of the resulting continuation field is not the same as that of the byte string or the character string, the shorter one is padded.
Character strings are padded with blanks, hexadecimal strings with zeros. A string of characters to be compared to the continuation field defined by start and end, according to the operator. The string must be enclosed in double or single quotation marks. The comparison is made character by character, blank padding on the right if necessary. A string of bytes in hexadecimal format used in the same way as str.
X'1FB would represent the three bytes with values 1F, B0, and 33 hexadecimal. The default is to exclude them. This is the only time you refer to positions in physical records. All other references are to logical records. That is, data values are allowed to span the records with no extra characters continuation characters in the middle.
Assume that you have physical records 14 bytes long and that a period represents a space:. Assume that you have the same physical records as in Example Note that columns 1 and 2 are not removed from the physical records when the logical records are assembled.
Therefore, the logical records are assembled as follows the same results as for Example It defines the relationship between records in the datafile and tables in the database. The specification of fields and datatypes is described in later sections.
0コメント