Having problems reading CLOB data > 32000 and thought maybe I can load the data into 3 separate columns less than 32000k each.
I tried to substring a part of it first to see if it worked.
DDL
CREATE TABLE "E406702"."DSR3"
(
"RECORDNO" CHAR(8 BYTE),
"DOCUMENTUNIVERSALID" CHAR(32 BYTE),
"CREATIONDATE" CHAR(30 BYTE),
"ETL_STATUS" VARCHAR2(250 BYTE),
"DSRDESCRIPTION" VARCHAR2(4000 BYTE),
"FORM" VARCHAR2(4000 BYTE),
"REQUESTID" VARCHAR2(4000 BYTE)
)
CONTROL FILE
LOAD DATA
INFILE 'DSR2DATA.txt' "str '\r'"
APPEND
INTO TABLE DSR3
FIELDS TERMINATED BY "<:s:>"
(RecordNo CHAR (8),
DocumentUniversalID CHAR (32),
CreationDate CHAR (30),
ETL_STATUS CHAR (250),
DSRDescription CHAR(32700) "substr(:DSRDescription,1,32)",
FORM CHAR (4000),
RequestId CHAR (4000))
Doing this I was able to load half of my records.
Check the log and saw
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Feb 19 09:40:40 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: c:\mytest7\DSR3CONTROL.txt
Data File: DSR2DATA.txt
File processing option string: "str '\r'"
Bad File: c:\mytest7\DSR3CONTROL.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table DSR3, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
RECORDNO FIRST 8 CHARACTER
Terminator string : '<:s:>'
DOCUMENTUNIVERSALID NEXT 32 CHARACTER
Terminator string : '<:s:>'
CREATIONDATE NEXT 30 CHARACTER
Terminator string : '<:s:>'
ETL_STATUS NEXT 250 CHARACTER
Terminator string : '<:s:>'
DSRDESCRIPTION NEXT 32700 CHARACTER
Terminator string : '<:s:>'
SQL string for column : "substr(:DSRDescription,1,32)"
FORM NEXT 4000 CHARACTER
Terminator string : '<:s:>'
REQUESTID NEXT 4000 CHARACTER
Terminator string : '<:s:>'
value used for ROWS parameter changed from 64 to 6
Record 79: Rejected - Error on table DSR3.
ORA-01461: can bind a LONG value only for insert into a LONG column
Record 82: Rejected - Error on table DSR3.
ORA-01461: can bind a LONG value only for insert into a LONG column
Record 94: Rejected - Error on table DSR3.
ORA-01461: can bind a LONG value only for insert into a LONG column
Record 160: Rejected - Error on table DSR3.
What is wrong this. My goal was for this to work and then try to do the following:
Change the ddl to
"DSRDESCRIPTION" CLOB,
"DSRDESCRIPTION1" CLOB,
"DSRDESCRIPTION2" CLOB,
Change the Control file to this.
DSRDescription CHAR(32700) "substr(:DSRDescription,1,32700)",
DSRDescription2 CHAR(32700) "substr(:DSRDescription,32701,65401)",
DSRDescription3 CHAR(15000) "substr(:DSRDescription,65402,80000)",
Is this wrong way to go about this?