Hi All,
I am on 11.2.0.3 linux 5.7
I am testing loading with 20 records and I have column for Last_date with datatype date.Out of 20 records 9 records of this column are empty i have "--" value in csv file.While loading records 11 rows are gettting inserted but 9 rows for which i have -- value in date it is not getting inserted.I am using TRAILING NULLCOLS even though 9 rows are not procesing.
Here is my table:
Name Null? Type
----------------------------------------- -------- ----------------------------
ACCOUNT_ID VARCHAR2(250)
EMP_ID VARCHAR2(250)
FIRST_NAME VARCHAR2(250)
LAST_NAME VARCHAR2(250)
MAIL VARCHAR2(250)
STATUS VARCHAR2(250)
LAST_DATE DATE
Here is my control file:
LOAD DATA
INFILE '/home/oracle/psrdata.csv'
BADFILE '/home/oracle/emp.bad'
DISCARDFILE '/home/oracle/emp.dsc'
INSERT INTO TABLE TEST
fields terminated by "," TRAILING NULLCOLS
(ACCOUNT_ID,EMP_ID,FIRST_NAME,LAST_NAME,MAIL,STATUS,LAST_DATE date "mm/dd/yyyy hh24:mi")
sample data
| Header 1 | Header 2 | Header 3 | Header 4 | Header 5 | Header 6 | Header 7 |
|---|
Account ID | Employee ID | Account First Name | Account Last Name | Account Mail | status | Last_Date |
| A019689 | 19689 | . | test | test1@test.com | Disabled | 08-07-2014 06:34 |
| A019689 | 19689 | . | test1 | test1@test.com | Disabled | 1/30/2015 8:48 |
| A019689 | 19689 | . | test2 | test1@test.com | Disabled | 08-07-2014 06:34 |
| A019689 | -- | -- | test3 | | Disabled | -- |
| T047760 | -- | -- | test4 | | Disabled | -- |
| T047760 | -- | -- | test5 | | enabled | -- |
| T047760 | T047943 | dwar | test7 | test1@test.com | enabled | 01-10-2014 02:16 |
| EXT_APPT_C033 | T047943 | dwar | test8 | test1@test.com | enabled | 1/29/2015 4:17 |
| EXT_APPT_C033 | T047943 | dwar | test9 | test1@test.com | enabled | -- |
| EXT_APPT_C033 | T047943 | dwar | test10 | test1@test.com | enabled | 1/29/2015 4:17 |
Getting error from log:
Record 1: Rejected - Error on table TEST, column LAST_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected
Thanks