SQLLDR - CLOB Data Load
--Reference Table structure

SQL> desc cms_config_detail
 Name                          Null?    Type
 ----------------------------- -------- -----------------  
 CMS_ID                        NOT NULL NUMBER
 CONTENT_TYPE                  NOT NULL VARCHAR2(50)
 CONTENT_CLOB                  CLOB
 CONTENT_CLOB                  BLOB
 CREATED_DATE                  NOT NULL DATE

SQL> 

-- Note: The cms_id column is populated by a trigger and 
-- sequence as showb below

CREATE SEQUENCE cms_id_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER cms_config_detail_bi_trig 
BEFORE INSERT ON cms_config_detail
FOR EACH ROW

BEGIN
  SELECT cms_id_seq.NEXTVAL
  INTO   :NEW.cms_id
  FROM   DUAL;
END cms_config_detail_bi_trig;
/
SQLLDR - CLOB Data Control File (loadclob.ctl)
-- loadclob.ctl
LOAD DATA
INFILE *
APPEND
INTO TABLE cms_config_detail
fields terminated by ','
(
 content_type   CHAR(10),
 lob_idx        FILLER char,
 content_clob   LOBFILE(lob_idx) TERMINATED BY EOF
)
begindata
XSD,C:\11g\odata\payment.xsd
XML,C:\11g\odata\ccPayment.xml
XML,C:\11g\odata\chkPayment.xml
SQLLDR Execution File (windows batch file loadclob.bat)
-- password input will be prompted

C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlldr webusr1@xe 
 control=loadclob.ctl
 log=loadclob.log
 bad=loadclob.bad

SQLLDR CLOB Data Load

-- Query CLOB data using DBMS_LOB

SELECT cms_id, content_type, DBMS_LOB.substr(content_clob,4000,1)  content_clob
FROM cms_config_detail;

-- CLOB Data
CLOB data


Oracle registered trademark of Oracle Corporation.

Last Revised On: September 15th, 2014

  190