Load Data using sqlldr: Substr() on CLOB variable on Control file
966028Oct 3 2012 — edited Oct 3 2012Hi all,
I am trying to substr data from a CLOB variable so that I can limit it to
4000 Bytes. Below is a CLOB variable "TEST_DEVICE" that I want to substr()
as I have done for the Reg_ID field which of VARCHAR2() datatype. The
reason for doing so is that we have been getting large data that at times
has gone over the *250000 bytes* as I have listed below. Is there a way
I can substr() instead of having to increase its size ? Any help would be much
appreciated.
Database: Oracle 11.2.0
-- Table
CREATE TABLE TEST_CLOB
(
REG_ID VARCHAR2(100 BYTE),
TEST_DEVICE CLOB
);
-- Control File
OPTIONS (SKIP=1)
Load DATA
TRUNCATE
INTO TABLE TEST_CLOB
FIELDS TERMINATED BY "|"
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
reg _id "trim(substr(:reg_id,1,100))",
test_device *char(250000)*
)