我试图加载数据从csv到oracle表,但它似乎不正常工作。我在列的数据Map中遇到了问题。数据进入表中错误的列,因此整个加载不成功。
谢谢,阿迪亚
我试图用shell脚本上传示例数据文件,但数据加载到错误的列中,因此由于数据类型不匹配而失败。
表格详情
CATEGORY VARCHAR2(50)
START_DATE DATE
USER_ID NUMBER
FIRST_NAME VARCHAR2(50)
LAST_NAME VARCHAR2(50)
FULL_NAME VARCHAR2(100)
USERNAME VARCHAR2(30)
BUSINESS_TITLE VARCHAR2(1000)
MANAGER_ID VARCHAR2(30)
MANAGER VARCHAR2(100)
USER_LOCATION VARCHAR2(100)
ROLE_ID NUMBER
ROLE_NAME VARCHAR2(100)
REG_TEMP VARCHAR2(50)
REG_REGION VARCHAR2(100)
TG VARCHAR2(100)
SUBTEAM VARCHAR2(100)
FTE NUMBER(8)
EMPLID VARCHAR2(30)
EMPL_STATUS VARCHAR2(30)
DEPTNAME VARCHAR2(100)
HIRE_DT DATE
TERMINATION_DT DATE
SKILL_NAME VARCHAR2(1000)
PROJECT_ID VARCHAR2(50)
CREATED_BY_USER_ID VARCHAR2(30)
DEMAND_START DATE
DEMAND_END DATE
PROJECT_TITLE VARCHAR2(500)
PROJECT_TYPE VARCHAR2(200)
PROJECT_STATUS VARCHAR2(100)
PROJECT_KEY VARCHAR2(100)
EMPLID1 VARCHAR2(30)
MANAGER_NAME VARCHAR2(100)
MANAGER_ID1 VARCHAR2(30)
LVL NUMBER
JIRA_DOMAIN VARCHAR2(200)
DEPTID VARCHAR2(25)
JOB_TITLE VARCHAR2(200)
USER_ROLE VARCHAR2(100)
PREFERRED_NAME VARCHAR2(100)
shell 脚本
#!/bin/bash
# Set the Oracle environment variables
export ORACLE_HOME=/usr/software/test/oracle
export PATH=$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
# Set the Oracle connection details
#export ORACLE_SID=<sid>
export ORACLE_USER=<uname>
export ORACLE_PASSWORD=<pwd>
export ORACLE_HOST=<host>
export ORACLE_PORT=1521
export ORACLE_SERVICE_NAME=<sname>
#log files
ldlogfile=rm_data_load.log
ldprcs_fl=rm_data_log.log
# Set the CSV file path and table name
CSV_FILE=/u/mmanogya/baz.csv
TABLE_NAME=<tablename>
# Create the control file for SQL*Loader
CONTROL_FILE=sqlldr_control.ctl
# Run SQL*Loader to load the CSV file
echo sqlldr $ORACLE_USER/$ORACLE_PASSWORD@$ORACLE_HOST:$ORACLE_PORT/$ORACLE_SERVICE_NAME control=$CONTROL_FILE
sqlldr $ORACLE_USER/$ORACLE_PASSWORD@$ORACLE_HOST:$ORACLE_PORT/$ORACLE_SERVICE_NAME control=$CONTROL_FILE log=$ldlogfile >$ldprcs_fl 2>&1
控制文件
OPTIONS (DIRECT=TRUE, SILENT=(HEADER,FEEDBACK,DISCARDS,PARTITIONS))
LOAD DATA
INFILE 'baz.csv'
INSERT
INTO TABLE tablename
FIELDS TERMINATED BY ','
optionally enclosed by '"'
TRAILING NULLCOLS
(
CATEGORY char OPTIONALLY ENCLOSED BY '"',
START_DATE DATE "YYYY-MM-DD",
USER_ID,
FIRST_NAME char OPTIONALLY ENCLOSED BY '"',
LAST_NAME char OPTIONALLY ENCLOSED BY '"',
FULL_NAME char OPTIONALLY ENCLOSED BY '"',
USERNAME char,
BUSINESS_TITLE char OPTIONALLY ENCLOSED BY '"',
MANAGER_ID char,
MANAGER char OPTIONALLY ENCLOSED BY '"',
USER_LOCATION char OPTIONALLY ENCLOSED BY '"',
ROLE_ID,
ROLE_NAME char OPTIONALLY ENCLOSED BY '"',
REG_TEMP char OPTIONALLY ENCLOSED BY '"',
REG_REGION char OPTIONALLY ENCLOSED BY '"',
TG char OPTIONALLY ENCLOSED BY '"',
SUBTEAM char,
FTE,
EMPLID char,
EMPL_STATUS char OPTIONALLY ENCLOSED BY '"',
DEPTNAME char OPTIONALLY ENCLOSED BY '"',
Y FILLER,
Z FILLER,
SKILL_NAME char OPTIONALLY ENCLOSED BY '"',
PROJECT_ID char OPTIONALLY ENCLOSED BY '"',
CREATED_BY_USER_ID char,
A FILLER,
B FILLER,
PROJECT_TITLE char OPTIONALLY ENCLOSED BY '"',
PROJECT_TYPE char OPTIONALLY ENCLOSED BY '"',
PROJECT_STATUS char OPTIONALLY ENCLOSED BY '"',
PROJECT_KEY char OPTIONALLY ENCLOSED BY '"',
EMPLID1 char,
MANAGER_NAME char,
MANAGER_ID1 char,
LVL,
JIRA_DOMAIN char OPTIONALLY ENCLOSED BY '"',
DEPTID char,
JOB_TITLE char OPTIONALLY ENCLOSED BY '"',
USER_ROLE char OPTIONALLY ENCLOSED BY '"',
PREFERRED_NAME char OPTIONALLY ENCLOSED BY '"'
)
错误记录
SQL*Loader: Release 11.2.0.3.0 - Production on Wed May 17 23:38:00 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Control File: sqlldr_control.ctl
Data File: baz.csv
Bad File: baz.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct
Silent options: FEEDBACK and DISCARDS
Table PPM_RM_REPORTING_TMP, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CATEGORY FIRST * , O(") CHARACTER
START_DATE NEXT * , O(") DATE YYYY-MM-DD
USER_ID NEXT * , O(") CHARACTER
FIRST_NAME NEXT * , O(") CHARACTER
LAST_NAME NEXT * , O(") CHARACTER
FULL_NAME NEXT * , O(") CHARACTER
USERNAME NEXT * , O(") CHARACTER
BUSINESS_TITLE NEXT * , O(") CHARACTER
MANAGER_ID NEXT * , O(") CHARACTER
MANAGER NEXT * , O(") CHARACTER
USER_LOCATION NEXT * , O(") CHARACTER
ROLE_ID NEXT 4 INTEGER
ROLE_NAME NEXT * , O(") CHARACTER
REG_TEMP NEXT * , O(") CHARACTER
REG_REGION NEXT * , O(") CHARACTER
TG NEXT * , O(") CHARACTER
SUBTEAM NEXT * , O(") CHARACTER
FTE NEXT 4 PACKED DECIMAL (7, 2)
EMPLID NEXT * , O(") CHARACTER
EMPL_STATUS NEXT * , O(") CHARACTER
DEPTNAME NEXT * , O(") CHARACTER
Y NEXT * , O(") CHARACTER
(FILLER FIELD)
Z NEXT * , O(") CHARACTER
(FILLER FIELD)
SKILL_NAME NEXT * , O(") CHARACTER
PROJECT_ID NEXT * , O(") CHARACTER
CREATED_BY_USER_ID NEXT * , O(") CHARACTER
A NEXT * , O(") CHARACTER
(FILLER FIELD)
B NEXT * , O(") CHARACTER
(FILLER FIELD)
PROJECT_TITLE NEXT * , O(") CHARACTER
PROJECT_TYPE NEXT * , O(") CHARACTER
PROJECT_STATUS NEXT * , O(") CHARACTER
PROJECT_KEY NEXT * , O(") CHARACTER
EMPLID1 NEXT * , O(") CHARACTER
MANAGER_NAME NEXT * , O(") CHARACTER
MANAGER_ID1 NEXT * , O(") CHARACTER
LVL NEXT 4 INTEGER
JIRA_DOMAIN NEXT * , O(") CHARACTER
DEPTID NEXT * , O(") CHARACTER
JOB_TITLE NEXT * , O(") CHARACTER
USER_ROLE NEXT * , O(") CHARACTER
PREFERRED_NAME NEXT * , O(") CHARACTER
Record 1: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 2: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 3: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 4: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 5: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 6: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 7: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 8: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 9: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 10: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 11: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 12: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 13: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 14: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 15: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 16: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 17: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 18: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 19: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 20: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 21: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 22: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 23: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 24: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 25: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 26: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 27: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 28: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 29: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 30: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 31: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 32: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 33: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 34: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 35: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 36: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 37: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 38: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 39: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 40: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 41: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 42: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 43: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 44: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 45: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 46: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 47: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 48: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 49: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 50: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
Record 51: Rejected - Error on table PPM_RM_REPORTING_TMP, column FTE.
ORA-01460: unimplemented or unreasonable conversion requested
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table PPM_RM_REPORTING_TMP:
0 Rows successfully loaded.
51 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.
Date cache:
Max Size: 1000
Entries : 3
Hits : 49
Misses : 0
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records rejected: 51
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 1
Total stream buffers loaded by SQL*Loader load thread: 1
Run began on Wed May 17 23:38:00 2023
Run ended on Wed May 17 23:38:37 2023
Elapsed time was: 00:00:37.49
CPU time was: 00:00:00.16
数据集如下
1条答案
按热度按时间kzipqqlq1#
我认为你需要对你的控制文件做这样的修改:
实际上定义了所有的小数外部的NUMBER列。