SQL*Loader加载CSV至Oracle报错及字段映射问题求助

一、核心报错处理 #

1. 无效绑定变量错误（SQL*Loader-291） #

报错原因是控制文件中绑定变量写法错误：

• 绑定变量前的冒号后不能加单引号，正确格式为:字段名
• 字段映射逻辑错误，需明确数据库列名与CSV字段名的对应关系

修正示例：

• 原错误写法：OD_FLOW_DT "TO_DATE(:'Flow_date', 'YYYYMMDD')"
• 正确写法：OD_FLOW_DT Flow_date "TO_DATE(:Flow_date, 'YYYYMMDD')"
• 同理，SIGNIFICANT_PD字段修正为：SIGNIFICANT_PD Significant_period_indicator "CASE WHEN :Significant_period_indicator = 'TRUE' THEN 1 ELSE 0 END"

2. ROWS参数变更提示 #

value used for ROWS parameter changed from 64 to 22是SQL*Loader根据文件大小自动调整批量提交行数的提示，属于正常信息，不影响导入，无需处理。

二、CSV字段与数据库字段映射修正 #

控制文件原写法完全错误，正确规则如下：

• 格式：数据库列名 [CSV字段名] ["SQL转换表达式"]
• 若数据库列名与CSV字段名一致，可省略CSV字段名
• 固定值/序列/SYSDATE等非CSV来源数据，需用双引号包裹表达式

修正后的完整控制文件 #

OPTIONS (SKIP=1)
LOAD DATA
INFILE 'main_cluster_file.csv'
APPEND INTO TABLE YMODAPP.TCLUSTER
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    ROW_ID "YMODAPP.TCLUSTER_SEQ.NEXTVAL",
    CLUSTER_ID,
    FORECAST_ID,
    CLS_CD Selling_class,
    OD_ORIG_ARP_CD Origin,
    OD_DEST_ARP_CD Destination,
    OD_FLOW_DT Flow_date "TO_DATE(:Flow_date, 'YYYYMMDD')",
    CMPT_CD Cabin,
    POC_CTRY POC,
    FARE_FAMILY_CD Fare_Family,
    ARP_PATH Airport_path,
    OP_AIRLINE_PATH Operating_airline_path,
    OP_FLT_PATH Operating_flight_number_path,
    OP_FLT_SUFFIX Operating_flight_suffix_path,
    TRVL_PURPOSE Travel_purpose,
    BKG_THRESHOLD Booking_Threshold,
    CREATE_DT_TM "SYSDATE",
    CREATED_BY "'U'"
)

INTO TABLE YMODAPP.TCLUSTER_SUMMARY
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    ROW_ID "YMODAPP.TCLUSTER_SUMMARY_SEQ.NEXTVAL",
    CLUSTER_ID,
    FORECAST_ID,
    CLS_CD Selling_class,
    PNR_SIZE_1,
    PNR_SIZE_2,
    PNR_SIZE_3,
    PNR_SIZE_4,
    PNR_SIZE_5,
    PNR_SIZE_6,
    PNR_SIZE_7,
    PNR_SIZE_8,
    PNR_SIZE_9,
    FCST_ACCURACY Forecast_Accuracy,
    ADJ_FINAL_DEMAND Adjusted_Final_Demand,
    PICKUP_CURVE Pick_Up_Curve_Demand_Today,
    USER_MANUAL_REL_FINAL_DMD User_Manual_adjustment_relative_final_demand_forecast,
    USER_MANUAL_ABS_FINAL_DMD User_Manual_adjustment_absolute_final_demand_forecast,
    USER_MANUAL_OVR_FINAL_DMD User_Manual_adjustment_override_final_demand_forecast,
    USER_MANUAL_ABS_GOING_CLS User_Manual_adjustment_absolute_going_class,
    ROBOTIC_REL_FINAL_DMD Robotic_Manual_adjustment_relative_final_demand_forecast,
    ROBOTIC_ABS_FINAL_DMD Robotic_Manual_adjustment_absolute_final_demand_forecast,
    ROBOTIC_OVR_FINAL_DMD Robotic_Manual_adjustment_override_final_demand_forecast,
    ROBOTIC_ABS_GOING_CLS Robotic_Manual_adjustment_absolute_going_class,
    SIGNIFICANT_PD Significant_period_indicator "CASE WHEN :Significant_period_indicator = 'TRUE' THEN 1 ELSE 0 END",
    SNAPSHOT_DT_TM "SYSDATE",
    CREATE_DT_TM "SYSDATE"
)

三、带空格CSV字段的处理 #

控制文件中OPTIONALLY ENCLOSED BY '"'配置已自动处理带空格的字段，只要CSV中带空格的字段用双引号包裹，SQL*Loader即可正确识别读取。

内容的提问来源于stack exchange，提问作者user29106165