|
|
This post was updated on .
Oracle Database 12.2.0
RHEL 7.4
Hi Erman,
I have a table structure as shown below
CREATE TABLE MOBILE_SMS(
"EVTTIMESTAMP" DATE,
"MSISDN" VARCHAR2(32 BYTE),
"TYPE" VARCHAR2(20 BYTE),
"POSTPRE" VARCHAR2(3 BYTE),
"WAY" VARCHAR2(7 BYTE),
"NE" VARCHAR2(10 BYTE),
"MSISDN_SMSC" VARCHAR2(32 BYTE),
"CELLID" VARCHAR2(10 BYTE)
)
TABLESPACE MOBILE_SMS
PARTITION BY RANGE ("EVTTIMESTAMP") INTERVAL (NUMTOYMINTERVAL('1','MONTH'))
(PARTITION "P1" VALUES LES THAN (TO_DATE('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')));
commit;
The entry in flat file is shown below
22-SEP-2018|00:38:29|23058363107|mobile_sms|pre|IncomingSMS|SMSC|000000000000002302500005|
OPTIONS(SKIP=0)
LOAD DATA
infile '/home/data/toprocess/msc_sms_rep_20180922.dat'
APPEND
INTO TABLE mobile_sms
-- Definition du separateur de champs
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
-- Definition des colonnes
(
EvtTimeStamp CHAR(21) "TO_DATE(replace(:EvtTimeStamp,'|',''),'DD-MON-YYYYHH24:MM:SS')", -- YYYYMMDDHHMISS
Type , -- "mobile_sms"
Way , -- "oc" : Outgoing sms
-- "ic" : Incoming sms
Postpre , -- "pre" for prepaid
-- "pos" for postpaid
MSISDN , --
MSISDN_SMSC , -- SMSC Number
NE --
)
I am getting the output below
Record 1: Rejected - Error on table MOBILE_SMS, column EVTTIMESTAMP.
ORA-01830: date format picture ends before converting entire input string
Record 2: Rejected - Error on table MOBILE_SMS, column EVTTIMESTAMP.
ORA-01830: date format picture ends before converting entire input string
Record 3: Rejected - Error on table MOBILE_SMS, column EVTTIMESTAMP.
ORA-01830: date format picture ends before converting entire input string
Record 4: Rejected - Error on table MOBILE_SMS, column EVTTIMESTAMP.
ORA-01830: date format picture ends before converting entire input string
Record 5: Rejected - Error on table MOBILE_SMS, column EVTTIMESTAMP.
ORA-01830: date format picture ends before converting entire input st
Please advise.
|
Locked
