The CHAR clause is used to indicate that a field is a character data type. The length (len) for CHAR fields specifies the largest number of bytes or characters in the field. The len is in bytes or characters, depending on the setting of the STRING SIZES ARE IN clause.
If no length is specified for a field of data type CHAR, then the size of the field is assumed to be 1, unless the field is delimited:
For a delimited CHAR field, if a length is specified, then that length is used as a maximum.
For a delimited CHAR field for which no length is specified, the default is 255 bytes.
For a delimited CHAR field that is greater than 255 bytes, you must specify a maximum length. Otherwise you will receive an error stating that the field in the data file exceeds maximum length.
The following example shows the use of the CHAR clause.
SQL> CREATE TABLE emp_load
2 (employee_number CHAR(5),
3 employee_dob CHAR(20),
4 employee_last_name CHAR(20),
5 employee_first_name CHAR(15),
6 employee_middle_name CHAR(15),
7 employee_hire_date DATE)
8 ORGANIZATION EXTERNAL
9 (TYPE ORACLE_LOADER
10 DEFAULT DIRECTORY def_dir1
11 ACCESS PARAMETERS
12 (RECORDS DELIMITED BY NEWLINE
13 FIELDS (employee_number CHAR(2),
14 employee_dob CHAR(20),
15 employee_last_name CHAR(18),
16 employee_first_name CHAR(11),
17 employee_middle_name CHAR(11),
18 employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy"
19 )
20 )
21 LOCATION ('info.dat')
22 );
Table created.