SQL*Loader Control File Reference. SQL*Loader supports different character encoding schemes (called character sets, or code pages). SQL*Loader uses features of Oracle's globalization support technology to handle the various single- byte and multibyte character encoding schemes available today. The following sections provide a brief introduction to some of the supported character encoding schemes.
Multibyte (Asian) Character Sets. Multibyte character sets support Asian languages.
Control File Contents. The SQL*Loader control file is a text file that contains data definition language (DDL) instructions. DDL is used to control the following. Consumer credit counseling service wa. Christian credit counseling programs. Consolidation counseling credit debt massachusetts.
- Get the Complete Oracle Utility Information The landmark book "Advanced Oracle Utilities The Definitive Reference" contains over 600 pages of filled with.
- 9 Field List Reference. This chapter describes the field-list portion of the SQL*Loader control file. The following topics are included: Field List Contents.
Data can be loaded in multibyte format, and database object names (fields, tables, and so on) can be specified with multibyte characters. In the control file, comments and object names can also use multibyte characters. Unicode Character Sets. SQL*Loader supports loading data that is in a Unicode character set. Unicode is a universal encoded character set that supports storage of information from most languages in a single character set. Unicode provides a unique code value for every character, regardless of the platform, program, or language. There are two different encodings for Unicode, UTF- 1.
UTF- 8. Note. In this manual, you will see the terms UTF- 1. UTF1. 6 both used. Downloads Flight Simulator 2007 Full Version Pc on this page. The term UTF- 1. 6 is a general reference to UTF- 1. Unicode. The term UTF1. CHARACTERSET parameter when you want to use UTF- 1. This also applies to UTF- 8 and UTF8. The UTF- 1. 6 Unicode encoding is a fixed- width multibyte encoding in which the character codes 0x.
F have the same meaning as the single- byte ASCII codes 0x. F. The UTF- 8 Unicode encoding is a variable- width multibyte encoding in which the character codes 0x. F have the same meaning as ASCII. A character in UTF- 8 can be 1 byte, 2 bytes, or 3 bytes long. Database Character Sets. The Oracle database uses the database character set for data stored in SQL CHAR datatypes (CHAR,VARCHAR2,CLOB, and LONG), for identifiers such as table names, and for SQL statements and PL/SQL source code. Only single- byte character sets and varying- width character sets that include either ASCII or EBCDIC characters are supported as database character sets.
Multibyte fixed- width character sets (for example, AL1. UTF1. 6) are not supported as the database character set. An alternative character set can be used in the database for data stored in SQL NCHAR datatypes (NCHAR,NVARCHAR2, and NCLOB). This alternative character set is called the database national character set. Only Unicode character sets are supported as the database national character set. Data File Character Sets. By default, the data file is in the character set defined by the NLS.
The data file character sets supported with NLS. SQL*Loader supports all Oracle- supported character sets in the data file (even those not supported as database character sets). For example, SQL*Loader supports multibyte fixed- width character sets (such as AL1. UTF1. 6 and JA1. 6EUCFIXED) in the data file. SQL*Loader also supports UTF- 1. However, the Oracle database supports only UTF- 1. AL1. 6UTF1. 6) and only as a database national character set, not as a database character set.
The character set of the data file can be set up by using the NLS. The character set used in input data files can be specified with the CHARACTERSET parameter. SQL*Loader can automatically convert data from the data file character set to the database character set or the database national character set, when they differ. When data character set conversion is required, the target character set should be a superset of the source data file character set. Otherwise, characters that have no equivalent in the target character set are converted to replacement characters, often a default character such as a question mark (?).
This causes loss of data. The sizes of the database character types CHAR and VARCHAR2 can be specified in bytes (byte- length semantics) or in characters (character- length semantics). If they are specified in bytes, and data character set conversion is required, then the converted values may take more bytes than the source values if the target character set uses more bytes than the source character set for any character that is converted. This will result in the following error message being reported if the larger target value exceeds the size of the database column. ORA- 0. 14. 01: inserted value too large for column. You can avoid this problem by specifying the database column size in characters and also by 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. Considerations When Loading Data into VARRAYs or Primary- Key- Based REFs.
If you use SQL*Loader conventional path or the Oracle Call Interface (OCI) to load data into VARRAYs or into primary- key- based REFs, and the data being loaded is in a different character set than the database character set, then problems such as the following might occur: Rows might be rejected because a field is too large for the database column, but in reality the field is not too large. A load might be abnormally terminated without any rows being loaded, when only the field that really was too large should have been rejected. Rows might be reported as loaded correctly, but the primary- key- based REF columns are returned as blank when they are selected with SQL*Plus. To avoid these problems, set the client character set (using the NLS. The default character set for all data files, if the CHARACTERSET parameter is not specified, is the session character set defined by the NLS.
Only character data (fields in the SQL*Loader datatypes CHAR,VARCHAR,VARCHARC, numeric EXTERNAL, and the datetime and interval datatypes) is affected by the character set of the data file. The CHARACTERSET syntax is as follows. CHARACTERSET char. Normally, the specified name must be the name of an Oracle- supported character set. For UTF- 1. 6 Unicode encoding, use the name UTF1. AL1. 6UTF1. 6. AL1. UTF1. 6, which is the supported Oracle character set name for UTF- 1.
UTF- 1. 6 data that is in big- endian byte order. However, because you are allowed to set up data using the byte order of the system where you create the data file, the data in the data file can be either big- endian or little- endian. Therefore, a different character set name (UTF1.
The character set name AL1. UTF1. 6 is also supported.
But if you specify AL1. UTF1. 6 for a data file that has little- endian byte order, then SQL*Loader issues a warning message and processes the data file as big- endian. The CHARACTERSET parameter can be specified for primary data files and also for LOBFILEs and SDFs. All primary data files are assumed to be in the same character set. A CHARACTERSET parameter specified before the INFILE parameter applies to the entire list of primary data files. If the CHARACTERSET parameter is specified for primary data files, then the specified value will also be used as the default for LOBFILEs and SDFs.
This default setting can be overridden by specifying the CHARACTERSET parameter with the LOBFILE or SDF specification. The character set specified with the CHARACTERSET parameter does not apply to data in the control file (specified with INFILE). To load data in a character set other than the one specified for your session by the NLS. If the control file character set is different from the data file character set, then keep the following issue in mind. Delimiters and comparison clause values specified in the SQL*Loader control file as character strings are converted from the control file character set to the data file character set before any comparisons are made.
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 data file in the UTF- 1. Unicode encoding, then the byte order is different on a big- endian versus a little- endian system. For example, . On a little- endian system it is X'2c.