I am trying to load data from a csv to oracle table but it doesn't seem to work correctly. I am having issue in data mapping with columns. Data is getting into the wrong columns in the table and due to this the whole load is not successful.
Thanks, Aditya
I tried to upload the sample data file with the shell script and the data loads into wrong columns and therefore fails due to data type mismatch.
Table details
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 script
#!/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
control file
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 '"'
)
error log
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
Data Set Below
I think you need to make this change to your control file:
Actually define all NUMBER columns decimal external.