3) Input Datafiles:
- The data to be loaded is contained in one or more datafiles if it is not contained in the control file.
- The data in the datafile can be in the fixed length format, variable length format, or in the stream record format.
a) Fixed Length Format:
A file is in the fixed record format when all the records in the datafile have the same byte length. This format is not flexible but offers very good performance.then the syntax for the INFILE command is – INFILE student.dat “fix 15?
The syntax for letting SQL*Loader know that the data is in the fixed length format is:
INFILE datafile_name “fix n”
Here INFILE datafile_name refers to the file that contains the data to be loaded. “fix n” implies that each record in the datafile has a fixed byte length of n.
For example if the name of the following datafile is student.dat and the byte length of a record is 15 bytes
0001, —–Rina, 0002, —-Harry, 0003,—–Sudha
b) Variable Length Format:
A file is in the variable record format when the length of each record varies. The length of each record is included at the beginning of the record in the datafile. This format provides some added flexibility over the fixed record format and a performance advantage over the stream record format.
For example, you can specify a datafile that is to be interpreted as being in variable record format as follows:
INFILE “datafile_name” “var n”
Here n specifies the number of bytes in the record length field. If n is not specified, SQL*Loader assumes a length of 5 bytes. If n is specified larger than 40 it results in an error. The following datafile is random.dat and the value for n is 3.
009hello,cd,010world,im,
012my,name is,
SQL*Loader reads the first 3 bytes to gather the length of the record. Here the first record is 9 bytes long. After SQL*Loader has read 9 bytes, it reads the next 3 bytes to find the size of this record which is 10 bytes long. It reads the next 10 bytes of the record and then finds the third record is 12 bytes long and so on.
c) Stream Record Format:
A file is in the stream record format when the records are not specified by size; instead SQL*Loader forms records by scanning for the record terminator. Stream record format is the most flexible format, but there can be a negative effect on performance.
The syntax for specifying the stream record format is as follows:
INFILE datafile_name ["str terminator_string"]
The terminator_string can be a ‘char_string’ which is a string of characters enclosed in single or double quotation marks or a ‘hex_string’ which is a byte string in hexadecimal format.
4) The Log File:
The log file is a record of SQL*Loader’s activities during a load session. It contains information such as the following:
- The names of the control file, log file, bad file, discard file, and data file
- The values of several command-line parameters
- A detailed breakdown of the fields and datatypes in the data file that was loaded
- Error messages for records that cause errors
- Messages indicating when records have been discarded
- A summary of the load that includes the number of logical records read from the data file, the number of rows rejected because of errors, the number of rows discarded because of selection criteria, and the elapsed time of the load
5) The Bad File:
Whenever you insert data into a database, you run the risk of that insert failing because of some types of error. Integrity constraint violations undoubtedly represent the most common type of error. However, other problems, such as the lack of free space in a tablespace, can also cause insert operations to fail. Whenever SQL*Loader encounters a database error while trying to load a record, it writes that record to a file known as the bad file.
- If one or more records are rejected, the bad file is created and the rejected records are logged.
- If no records are rejected, then the bad file is not created.
While SQL*Loader is being executed it creates a discard file for records that do not meet any of the loading criteria. 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. A discarded record is never inserted into the Oracle table.
A discard file is created according to the following rules:
- You have specified a discard filename and one or more records fail to satisfy all of the WHEN clauses specified in the control file. (If the discard file is created, it overwrites any existing file with the same name, so be sure that you do not overwrite any files that you want to retain.)
- If no records are discarded, then a discard file is not created.
No comments:
Post a Comment