用oracle的sqlload导入文件到数据库

一、将csv文件和input.ctl文件放到一个文件夹下
1）数据源文件input.csv （utf-8编码）

17,后天去哪里呀，本来这件事情就让京东快递快,2021-04-01 17:37:25,user121

2）执行控制文件input.clt

load data
infile 'input.csv'
insert into table NOTE
truncate
fields terminated by ","
(note_id,
content,
note_time DATE 'YYYY-MM-DD HH24:MI:SS',
user_id)

二、cmd进入这个文件夹，执行以下命令
sqlldr user/123456@localhost:1521/qrcoded control=input.ctl

三、执行完成后会在同一目录下生成input.log文件

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Apr 2 11:37:46 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   input.ctl
Data File:      input.csv
  Bad File:     input.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table NOTE, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
NOTE_ID                             FIRST     *   ,       CHARACTER            
CONTENT                              NEXT     *   ,       CHARACTER            
NOTE_TIME                            NEXT     *   ,       DATE YYYY-MM-DD HH24:MI:SS
USER_ID                              NEXT     *   ,       CHARACTER            


Table NOTE:
  1 Row successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  66048 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             1
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri Apr 02 11:37:46 2021
Run ended on Fri Apr 02 11:37:46 2021

Elapsed time was:     00:00:00.15
CPU time was:         00:00:00.03

四、遇到的问题，时间格式错误、中文乱码
1）时间格式
DATE ‘YYYY-MM-DD HH24:MI:SS’
2)中文乱码
执行以下命令
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

改一下clt文件，

load data
CHARACTERSET AL32UTF8
infile 'input.csv'
insert into table NOTE
truncate
fields terminated by ","
(note_id,
content,
note_time DATE 'YYYY-MM-DD HH24:MI:SS',
user_id)

但发现，加不加CHARACTERSET AL32UTF8，只要终端执行了
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
命令都不会出现乱码