Replace $ char with zero for data field using SQLLoader

Question

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.

Answer 1

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"