Use notes for sqlldr

Requirement: Import TXT data to Oracle

Solution: Use the SQL * loader tool.

Problems:

(1) Field in data file exceeds maximum length

(2) ORA-01861: literal does not match Format String

Recently, we need to import and export data between different databases, which is called a porter. As a mineral water advertisement said: we do not produce data, but we only work as data porters. Back to the question, the IBM datastage tool was used for data conversion during official production and operation. However, some data may be exported for development and use. In this case, using DS is a bit like a heavy weapon, which is not cost-effective. At this time, the script was launched by Lili.

The speed uses the sqlldr command and Its Key Parameters

Userid -- username/password of Oracle [@ servicename]
Control -- control file, which may contain table data
Bytes -------------------------------------------------------------------------------------------------------
Log-records the log file during import. The default value is control file (remove Extension). log.
Bad-bad data file, which is the control file (remove extension) by default. Bad
Data-data file, which is generally specified in the control file. It is more suitable for automatic operations if no data file is specified in the parameter control file.
Errors -- number of allowed error records, which can be used to control a record.
Rows-the number of records submitted once. The default value is 64.
Skip-the number of skipped lines. For example, the first few lines of the exported data file are headers or other descriptions.

Usage: [oralce @ dboracle] $ sqlldr user/passwd @ orcl control = control. CTL

Among them, the preparation of control files is the focus.

The content of control. CTL is as follows:

Load data
Infile 'bank.txt '-- specifies the external data file
Append into table test. sqlldr_test -- Operation Type
Fields terminated by "," -- use "," to separate records in each row of data

Optionally enclosed by '"' -- each field in the data is separated '"'.

Trailing nullcols -- the table field can be empty if it does not have a corresponding value
(Block_id,
Block_data_begin_date,
Block_data_end_date,
Period_id,
Create_date,
Rec_state,
State_date)

Note:

(1) The field type can be specified, otherwise it is considered as character type.
(2) table definition

 

Create Table Test. sqlldr_test
(
Block_id integer not null,
Block_data_begin_date integer,
Block_data_end_date integer,
Period_id varchar2 (8 ),
Create_date date,
Rec_state varchar2 (3 ),
State_date date
)
In this case, the ORA-01861: literal does not match format string error occurs when you use the above control file, the solution is to write the date type, that is

Create_date date "YYYY-MM-DD"

State_date date "YYYY-MM-DD"

During data import, the field in data file exceeds maximum length error is also encountered. After moving out of the valley, there are the following:Article:

 

 

Field exceeds

Symptoms

Loading long columns using sqlldr, the following error is reported:

"Record 1: rejected-error on table crm_atic_header_dif, column long_desc.
Field in data file exceeds maximum length"

Cause

In the control file, char (n) had to be specified for all the "varchar2" and "long" columns that were there in the table definition

Fix

1. Check if there are any long or varchar2 columns in the table where the data has to be loaded

2. Check the control file to make sure whether char (n) has been encoded ded for those corresponding columns.

Taking the example of the above control file, if these changes are made:
3. Changing the control file by including char (n) will resolve the issue

We can see that you only need to specify n data in the control file of the character type char (N), such:

 

Period_id char (8)

-The end-