0
oracle@tdc2mdb07> cat rsm.ctl
**************************************************
LOAD DATA
infile '/tmp/20181012_bkp.txt'
REPLACE INTO TABLE ASSET_ODS.ROADSIDE_MESSAGE
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
RM_ID                                     NOT NULL NUMBER(12),
RM_COMMS_DATE                                      TIMESTAMP(3),
RM_DETECTION_DATE                                  TIMESTAMP(3),
RM_TSMC_ID                                NOT NULL NUMBER(3),
RM_CHANNEL                                NOT NULL NUMBER(1),
RM_SEQUENCE_NO                                     NUMBER(10),
RM_MESSAGE_CLASS                                   NUMBER(3),
RM_MESSAGE_TYPE                                    NUMBER(5),
RM_RAW_DATA                                        RAW(1200),
RM_STATUS                                 NOT NULL NUMBER(1),
RM_SEQUENCE_NUMBER_STATUS                          VARCHAR2(2),
RM_TOLLING_SEGMENT_ID                              NUMBER(3),
RM_ASSET_ID                               NOT NULL VARCHAR2(4)
)

oracle@tdc2mdb07> sqlldr \'/ as sysdba\' rsm.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Oct 15 10:04:06 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-350: Syntax error at line 7.
Expecting "," or ")", found "NOT".
RM_ID                                     NOT NULL NUMBER(12),
                                          ^
4
  • fixed by editing the control file as below. Now a new error, – user161696 Oct 15 '18 at 0:16
  • LOAD DATA infile '/tmp/rsm_dat_20181012_bkp.txt' APPEND INTO TABLE ASSET_ODS.ROADSIDE_MESSAGE FIELDS TERMINATED BY '|' TRAILING NULLCOLS ( RM_ID, RM_COMMS_DATE, RM_DETECTION_DATE, RM_TSMC_ID, RM_CHANNEL, RM_SEQUENCE_NO, RM_MESSAGE_CLASS, RM_MESSAGE_TYPE, RM_RAW_DATA RAW(1200), RM_STATUS, RM_SEQUENCE_NUMBER_STATUS, RM_TOLLING_SEGMENT_ID, RM_ASSET_ID ) – user161696 Oct 15 '18 at 0:17
  • oracle@tdc2mdb07> sqlldr \'/ as sysdba\' rsm_bkp.ctl SQLLoader: Release 12.1.0.2.0 - Production on Mon Oct 15 11:08:29 2018 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. SQLLoader-261: illegal use of TERMINATED BY for RAW field – user161696 Oct 15 '18 at 0:17
  • 2
    If you have an update to your code edit your question (by clicking on the edit link below it), do not post code or additional important information as comments. To format the text select it, then click on the {} button when you edit your question. – a_horse_with_no_name Oct 15 '18 at 7:31
1

SQL*Loader-00100 to SQL*Loader-03120

SQL*Loader-00261: illegal use of TERMINATED BY for RAW field

Cause: The TERMINATED BY option cannot be used for loading data of type RAW from the data file. The only exception is when loading RAW data from LOBFILES. In that case, you may specify the TERMINATED BY EOF option.

Action: Remove the TERMINATED BY option from the RAW field in the SQL*Loader control file.

0