A text file contains data like below.
041522$$$$$$$$$NAPTTALIE REVERE @1621500025 OLD ST FUNNRHILL MA1530 273 000000$$$$$$$03@$$$@@@@@@@@@@@@@@$$$$$$$$$$$$$$$$$$Z$$$$$$$$$$$$$$$$$$$$$$@@@$$$$$$$$$$$$$$$$$$$$$@@@@@$$$$$$$$$$$$$$$@$$$$$0$$$$$$$$$$$000000$$$$$$$$$$$$@$$@$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$@@$$$$$$$$$$$$000000$$$$$$$$$$$$$A@@@Y$$$$$$$$$$$$$1@@$$$$$$$$$$$$$$$$$$$@@02$$$$$$$$$$$$$@$$$$$$$$$$$$$$$$$$$$$$@@Y@@@@@@@$$$$@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Control FIle:
LOAD DATA
CHARACTERSET "UTF8"
INFILE 'C:\bendex\MA_File38\fileout.txt'
BADFILE 'C:\bendex\MA_File38\baddata.bad'
DISCARDFILE 'C:\bendex\MA_File38\discdata.dsc'
APPEND
INTO TABLE "TMP_DATA_1220"
TRAILING NULLCOLS
(
SOURCE CONSTANT "TEST",
FILE_DTE "TRUNC(SYSDATE)",
AU_REGION POSITION (1:2),
AU_OFFICE POSITION (3:5),
AU_PGM_CATEGORY POSITION (6) ,
GRANTEE_SSN POSITION (7:15),
GRANTEE_NAME POSITION (16:38),
CAT_ELIG_IND POSITION (39),
PHONE POSITION (40:47),
ADDRESS POSITION (48:70),
CITY POSITION (71:83),
STATE POSITION (84:85),
ZIP POSITION (86:90),
CAN_NUM POSITION (91:95),
NET_INC POSITION (96:101) "TO_NUMBER(:NET_INC)",
START_DTE POSITION (102:107) "CASE WHEN :START_DTE ='$$$$$$' THEN TO_CHAR(REPLACE(:START_DTE, '$', '0')) ELSE DATE 'rrmmdd'",
LAST_UPDT_UID_NAM CONSTANT "LOADF38",
LAST_UPDT_TS "SYSTIMESTAMP"
)
**Error:**
Record 1: Rejected - Error on table "TMP_DATA_1220", column START_DTE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
I have to read the data from the text file and load into table. I tried to replace '$' with '0' and convert to date field, position 102 to 107, but I am getting error. I tried using REPLACE, DECODE did not work.
Any help is much appreciated. Thank you.
NOTE: The text file has full length data but reading only first few data points using SQL Loader.
I believe you would want to make your start date NULL if it was invalid, no?
"CASE WHEN :START_DTE ='$$$$$$' THEN NULL ELSE to_date(:START_DTE, 'rrmmdd') END"