SQL*Loader error-ORA-01722: invalid number and date format picture ends before converting entire input string

Question

I wish to import 100k+ rows from pipe seperated file into Oracle Database. but SQL*Loader throwing errors.

Can you assist me in guiding what is wrong in Control file ?

CPARTY_ID|TYPE|NAME|SHORT_NAME|ULT_PARENT_CPARTY_ID|CPARTY_GRP_ID|OP_COUNTRY_CODE|INC_COUNTRY_CODE|BIC_ID|CREATED_DATE|CHANGED_DATE|LOGICALLY_DELETED|OP_LOCATION_ID|INC_LOCATION_ID|SIC_ID
10000129|L|Coral Stadia Limited|Coral Stadia Ltd|40987565|8211147|GB|GB|9261|1998-10-14 23:20:15.94|2014-10-28 12:06:54.15|N|286|1832|7941

Below is the Control File

LOAD DATA
INFILE '\\ServerName\Big_File\Shared_Path\CP EXTRACT\cparty_extract.dat'
BADFILE '\\ServerName\Big_File\Shared_Path\CP EXTRACT\sample.bad'
DISCARDFILE '\\ServerName\Big_File\Shared_Path\CP EXTRACT\sample.dsc'
APPEND
INTO TABLE sds_cparty_import
fields terminated "|" optionally enclosed by '"' TRAILING NULLCOLS
(CPARTY_ID,TYPE,NAME,SHORT_NAME,ULT_PARENT_CPARTY_ID,CPARTY_GRP_ID,OP_COUNTRY_CODE,INC_COUNTRY_CODE,BIC_ID,CREATED_DATE DATE 'YYYY-MM-DD HH24:MI:SS',CHANGED_DATE DATE 'YYYY-MM-DD HH24:MI:SS',LOGICALLY_DELETED,OP_LOCATION_ID,INC_LOCATION_ID,SIC_ID)

Error Log is as below:

Record 1: Rejected - Error on table SDS_CPARTY_IMPORT, column CPARTY_ID.
ORA-01722: invalid number

Record 2: Rejected - Error on table SDS_CPARTY_IMPORT, column CREATED_DATE.
ORA-01830: date format picture ends before converting entire input string

Record 3: Rejected - Error on table SDS_CPARTY_IMPORT, column CREATED_DATE.
ORA-01830: date format picture ends before converting entire input string

Question 1: Why there is error for column 1: CPARTY_ID? The datatype for CPARTY_ID is NUMBER(8).

Question 2: CREATE_DATE format error? If fractional seconds are not supported. Can we skip importing this column ?

Answer 1

ORA-01830 Date Format Picture

Your dates include fractional seconds, which is not supported by oracle DATE types.

 1998-10-14   23:20:15.94
'YYYY-MM-DD HH24:MI:SS'

I have run into this issue importing from SQL server, which will output three digits of fractional seconds (millisecond resolution).

For the fix, I had to use undocumented entries in the format string to discard/ignore the fractional seconds, though I don't have a deep understanding of why.

SELECT TO_DATE('1998-10-14 23:20:15.94', 'YYYY-MM-DD HH24:MI:SS') from DUAL;
--Fails: ORA-01830: date format picture ends before converting entire input string
SELECT TO_DATE('1998-10-14 23:20:15.94', 'YYYY-MM-DD HH24:MI:SS.##') from DUAL;
--Works: '14-OCT-98'

For more detail, reference this question: Skip Characters in Oracle TO_DATE function

ORA-01722 Invalid Number

For the number error, I don't see why this is happening.

SET SERVEROUTPUT ON
DECLARE
  var NUMBER(8);
BEGIN
  var := TO_NUMBER('10000129');
  DBMS_OUTPUT.put_line('var is now ' || var);
END;

Works just fine for me. Is it perhaps trying to load the header line and cast 'CPARTY_ID' (first row) to a NUMBER(8)?

I suspect this is the case, so use:

OPTIONS (SKIP=1) --Don't Load Header Row
LOAD DATA
INFILE '\\ServerName\Big_File\Shared_Path\CP EXTRACT\cparty_extract.dat'
BADFILE '\\ServerName\Big_File\Shared_Path\CP EXTRACT\sample.bad'
DISCARDFILE '\\ServerName\Big_File\Shared_Path\CP EXTRACT\sample.dsc'
APPEND
INTO TABLE sds_cparty_import
fields terminated "|" optionally enclosed by '"' TRAILING NULLCOLS
(CPARTY_ID,TYPE,NAME,SHORT_NAME,ULT_PARENT_CPARTY_ID,CPARTY_GRP_ID,OP_COUNTRY_CODE,INC_COUNTRY_CODE,BIC_ID,CREATED_DATE DATE 'YYYY-MM-DD HH24:MI:SS.##',CHANGED_DATE DATE 'YYYY-MM-DD HH24:MI:SS.##',LOGICALLY_DELETED,OP_LOCATION_ID,INC_LOCATION_ID,SIC_ID)