You can avoid this problem by specifying the database column size in characters and by also using character sizes in the control file to describe the data. Another way to avoid this problem is to ensure that the maximum column size is large enough, in bytes, to hold the converted value. 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 Notify me of followup comments via e-mail. All rights reserved Terms of Service. If you are using Oracle database, at some point you might have to deal with uploading data to the tables from a text file.
Nice June 25, , am. Thanks for the article. Great Post.. Prasath June 25, , pm. This is Nice…. Lin Thein Naing July 13, , am. Really awesome!!!! Appreciate …. Manjula July 23, , pm. Prithviraj July 30, , am. Manjula: Ramesh has explained answer to your question in example 8. Rohit K August 5, , am. Thanks in advance! Regards, Rohit. Prithviraj August 6, , am. Rohit K August 6, , am.
Thank You Prithviraj. We can do it using a control file this way. Thank You once again. Regards, Rohit K. SantoshCA September 4, , am. Jurgen October 26, , am. Kind regards, Jurgen. Mahes Tripathi November 6, , am. Hi All, I have a flatfile notepad , which has data not in order, fields separated by space, that too not orderly separated.
Thanks souji. Kenneth Y January 10, , pm. Dhawal Limbuwala January 24, , am. Imteyaz March 14, , pm. Naveen March 29, , pm. Great Explanation , simple and clear. Naresh April 5, , am. Can anyone tell me how to load it…. Ashok May 13, , pm. Praveen Kumar July 23, , pm. The article is very good and easily understandable. Nice explanation…, thank you so much!
Muhd Islam August 24, , pm. Gauthama P August 28, , am. Shivanand September 11, , am. Vivek V September 27, , am. Aabid October 17, , am. CRP October 23, , pm. Greate article, thank you for sharing. Prasad October 29, , am. Satya October 31, , am. Very Nice!!!!!! BUT how to load default value to a field. The specification of fields and datatypes is described in later sections.
The table must already exist. If the table is not in the user's schema, then the user must either use a synonym to reference the table or include the schema name as part of the table name for example, scott. That method overrides the global table-loading method.
The following sections discuss using these options to load data into empty and nonempty tables. It requires the table to be empty before loading. Case study 1, Loading Variable-Length Data, provides an example. If data does not already exist, the new rows are simply loaded.
Case study 4, Loading Combined Physical Records, provides an example. The row deletes cause any delete triggers defined on the table to fire. For more information about cascaded deletes, see the information about data integrity in Oracle Database Concepts. To update existing rows, use the following procedure:. It is valid only for a parallel load. You can choose to load or discard a logical record by using the WHEN clause to test a condition in the record.
The WHEN clause appears after the table name and is followed by one or more field conditions. For example, the following clause indicates that any record with the value "q" in the fifth column position should be loaded:.
Parentheses are optional, but should be used for clarity with multiple comparisons joined by AND, for example:. If all data fields are terminated similarly in the datafile, you can use the FIELDS clause to indicate the default delimiters.
Description of the illustration terminat. Description of the illustration enclose. You can override the delimiter for any given column by specifying it after the column name.
Specifying Delimiters for a complete description of the syntax. Assume that the preceding data is read with the following control file and the record ends after dname:. In this case, the remaining loc field is set to null. This option inserts each index entry directly into the index, one record at a time. Instead, index entries are put into a separate, temporary storage area and merged with the original index at the end of the load.
This method achieves better performance and produces an optimal index, but it requires extra storage space. During the merge operation, the original index, the new index, and the space for new entries all simultaneously occupy storage space. The resulting index may not be as optimal as a freshly sorted one, but it takes less space to produce.
It also takes more time because additional UNDO information is generated for each index insert. This option is suggested for use when either of the following situations exists:. The number of records to be loaded is small compared to the size of the table a ratio of or less is recommended.
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. Consider the following data, in which emp and dept records are intermixed:. 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:.
It causes field scanning to start over at column 1 when checking for data that matches the second format. A single datafile may contain records made up of row objects inherited from the same base row object type.
For example, consider the following simple object type and object table definitions, in which a nonfinal base object type is defined along with two object subtypes that inherit their row objects from the base type:. The following input datafile contains a mixture of these row objects subtypes. A type ID field distinguishes between the three subtypes. See case study 5, Loading Data into Multiple Tables, for an example.
Multiple rows are read at one time and stored in the bind array. It does not apply to the direct path load method because a direct path load uses the direct path API, rather than Oracle's SQL interface. The bind array must be large enough to contain a single row. Otherwise, the bind array contains as many rows as can fit within it, up to the limit set by the value of the ROWS parameter.
Although the entire bind array need not be in contiguous memory, the buffer for each field in the bind array must occupy contiguous memory. Large bind arrays minimize the number of calls to the Oracle database and maximize performance. In general, you gain large improvements in performance with each increase in the bind array size up to rows. Increasing the bind array size to be greater than rows generally delivers more modest improvements in performance. The size in bytes of rows is typically a good value to use.
It is not usually necessary to perform the detailed calculations described in this section. Read this section when you need maximum performance or an explanation of memory usage. The bind array never exceeds that maximum. If that size is too large to fit within the specified maximum, the load terminates with an error.
The bind array's size is equivalent to the number of rows it contains times the maximum length of each row. The maximum length of a row is equal to the sum of the maximum field lengths, plus overhead, as follows:. Many fields do not vary in size. These fixed-length fields are the same for each loaded row. There is no overhead for these fields. The maximum lengths describe the number of bytes that the fields can occupy in the input data record.
That length also describes the amount of storage that each field occupies in the bind array, but the bind array includes additional overhead for fields that can vary in size.
When specified without delimiters, the size in the record is fixed, but the size of the inserted field may still vary, due to whitespace trimming. So internally, these datatypes are always treated as varying-length fields—even when they are fixed-length fields. A length indicator is included for each of these fields in the bind array.
The space reserved for the field in the bind array is large enough to hold the longest possible value of the field. The length indicator gives the actual length of the field for each row. On most systems, the size of the length indicator is 2 bytes.
On a few systems, it is 3 bytes. To determine its size, use the following control file:. This control file loads a 1-byte CHAR using a 1-row bind array. In this example, no data is actually loaded because a conversion error occurs when the character a is loaded into a numeric column deptno.
The bind array size shown in the log file, minus one the length of the character field is the value of the length indicator. Table through Table summarize the memory requirements for each datatype. They can consume enormous amounts of memory—especially when multiplied by the number of rows in the bind array. It is best to specify the smallest possible maximum length for these fields.
Consider the following example:. This can make a considerable difference in the number of rows that fit into the bind array. Imagine all of the fields listed in the control file as one, long data structure—that is, the format of a single row in the bind array.
It is especially important to minimize the buffer allocations for such fields. In general, the control file has three main sections, in the following order: Sessionwide information Table and field-list information Input data optional section Example shows a sample control file.
Comments in the Control File Comments can appear anywhere in the command section of the file, but they should not appear within the data. Precede any comment with two hyphens, for example: --This is a comment All text to the right of the double hyphen is ignored, until the end of the line.
See "Loading Records Based on a Condition". The remainder of the control file contains the field list, which provides information about column formats in the table being loaded. See Chapter 10 for information about that section of the control file. Comments can appear anywhere in the command section of the file, but they should not appear within the data.
Precede any comment with two hyphens, for example:. This can be useful when you typically invoke a control file with the same set of options. These parameters are described in greater detail in Chapter 8. The information in this section discusses the following topics:.
You must specify SQL strings within double quotation marks. The following sections discuss situations in which your course of action may depend on the operating system you are using. If you encounter problems when trying to specify a complete path name, it may be due to an operating system-specific incompatibility caused by special characters in the specification.
In many cases, specifying the path name within single quotation marks prevents errors. The same rule applies when single quotation marks are required in a string delimited by single quotation marks. Preceding the double quotation mark with a backslash indicates that the double quotation mark is to be taken literally:. When you convert to a different operating system, you will probably need to modify these strings. If your operating system uses the backslash character to separate directories in a path name, and if the release of the Oracle database running on your operating system implements the backslash escape character for file names and other nonportable strings, then you must specify double backslashes in your path names and use single quotation marks.
The release of the Oracle database running on your operating system may not implement the escape character for nonportable strings. When the escape character is disallowed, a backslash is treated as a normal character, rather than as an escape character although it is still usable in all other strings.
Then path names such as the following can be specified normally:. Because the backslash is not recognized as an escape character, strings within single quotation marks cannot be embedded inside another string delimited by single quotation marks. This rule also holds for double quotation marks. A string within double quotation marks cannot be embedded inside another string delimited by double quotation marks.
The XML schema definition is as follows. Either direct path or conventional mode can be used to load the data into the table. To specify a data file that contains the data to be loaded, use the INFILE keyword, followed by the file name and optional file processing options string. If no file name is specified, then the file name defaults to the control file name with an extension or file type of.
Any spaces or punctuation marks in the file name must be enclosed in single quotation marks. If your data is in the control file itself, then use an asterisk instead of the file name.
If you have data in the control file and in data files, then you must specify the asterisk first in order for the data to be read. This is the file-processing options string.
It specifies the data file format. It also optimizes data file reads. The syntax used for this string is specific to your operating system. Data contained in a file named sample with a default extension of. Data contained in a file named datafile. Data files need not have the same file processing options, although the layout of the records must be identical. For example, two files could be specified with completely different file processing options strings, and a third could consist of data in the control file.
You can also specify a separate discard file and bad file for each data file. In such a case, the separate bad files and discard files must be declared immediately after each data file name.
For example, the following excerpt from a control file specifies four data files with separate bad and discard files:. For mydat1. Therefore both files are created, as needed. For mydat2. Therefore, only the bad file is created, as needed.
If created, the bad file has the default file name and extension mydat2. The discard file is not created, even if rows are discarded. For mydat3. A discard file with the specified name mydat3. For mydat4. If the data is included in the control file itself, then the INFILE clause is followed by an asterisk rather than a file name.
The actual data is placed in the control file after the load configuration specifications. The syntax is:. To declare a file named mydata. If you have specified that a bad file is to be created, then the following applies:.
If one or more records are rejected, then the bad file is created and the rejected records are logged. If the bad file is created, then it overwrites any existing file with the same name; ensure that you do not overwrite a file you want to retain.
If you do not specify a name for the bad file, then the name defaults to the name of the data file with an extension or file type of. You can also specify the bad file from the command line with the BAD parameter described in "Command-Line Parameters".
A file name specified on the command line is associated with the first INFILE clause in the control file, overriding any bad file that may have been specified as part of that clause. The bad file is created in the same record and file format as the data file so that you can reload the data after you correct it.
For data files in stream record format, the record terminator that is found in the data file is also used in the bad file. The filename parameter specifies a valid file name specification for your platform. To specify a bad file with file name sample and default file extension or file type of.
To specify a bad file with file name bad and file extension or file type of. If there is an error loading a LOB, then the row is not rejected. Rather, the LOB column is left empty not null with a length of zero 0 bytes.
If the data can be evaluated according to the WHEN clause criteria even with unbalanced delimiters , then it is either inserted or rejected. Neither a conventional path nor a direct path load will write a row to any table if it is rejected because of reason number 2 in the previous list. A conventional path load will not write a row to any tables if reason number 1 or 3 in the previous list is violated for any one table.
The row is rejected for that table and written to the reject file. In a conventional path load, if the data file has a record that is being loaded into multiple tables and that record is rejected from at least one of the tables, then that record is not loaded into any of the tables.
The log file indicates the Oracle error for each rejected record. Case study 4 demonstrates rejected records. The records contained in this file are called discarded records. Discarded records do not satisfy any of the WHEN clauses specified in the control file. These records differ from rejected records. Discarded records do not necessarily have any bad data. No insert is attempted on a discarded record.
You have specified a discard file name and one or more records fail to satisfy all of the WHEN clauses specified in the control file. Be aware that if the discard file is created, then it overwrites any existing file with the same name. You can specify the discard file directly by specifying its name, or indirectly by specifying the maximum number of discards. The discard file is created in the same record and file format as the data file.
For data files in stream record format, the same record terminator that is found in the data file is also used in the discard file.
The default file name is the name of the data file, and the default file extension or file type is. A discard file name specified on the command line overrides one specified in the control file. If a discard file with that name already exists, then it is either overwritten or a new version is created, depending on your operating system.
A file name specified on the command line overrides any discard file that you may have specified in the control file. The following list shows different ways you can specify a name for the discard file from within the control file:. To specify a discard file with file name circular and default file extension or file type of. To specify a discard file named notappl with the file extension or file type of.
To specify a full path to the discard file forget. An attempt is made to insert every record into such a table. Therefore, records may be rejected, but none are discarded.
0コメント