For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Is there a specific forum for Oracle 18c Express Edition? This forum seems to be for Oracle 11 express edition.
Well, since CLOB in general is multi-line text each clob needs to be stored as a separate file. So we prepare CTL file:
LOAD DATA INFILE 'C:\TEMP\TMP_LOAD_CLOB_DATA.TXT' INTO TABLE TMP_LOAD_CLOB_DATA FIELDS TERMINATED BY X'09' ( CUSTOMER_ID, FIRSTNAME, LASTNAME, CUSTOMER_DETAILS_CLOB_FILE FILLER, CUSTOMER_DETAILS LOBFILE(CUSTOMER_DETAILS_CLOB_FILE) TERMINATED BY EOF )
Data file C:\TEMP\TMP_LOAD_CLOB_DATA.TXT:
123 ABC XYZ C:\TEMP\TMP_LOAD_CLOB1.TXT 456 DEF ZZZ C:\TEMP\TMP_LOAD_CLOB2.TXT
Clob file C:\TEMP\TMP_LOAD_CLOB1.TXT:
Lot Of Information about customer ABC XYZ which should be loaded into a clob
Clob file C:\TEMP\TMP_LOAD_CLOB2.TXT:
Lot Of Information about customer DEF ZZZ which should be loaded into a clob
Now:
SQL> SELECT * 2 FROM TMP_LOAD_CLOB_DATA 3 / no rows selected SQL> host Microsoft Windows [Version 10.0.17763.1637] (c) 2018 Microsoft Corporation. All rights reserved. I:\>sqlldr scott@pdb1sol122/tiger control=C:\TEMP\TMP_LOAD_CLOB_DATA.ctl SQL*Loader: Release 12.2.0.1.0 - Production on Fri Feb 5 07:25:12 2021 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 2 Table TMP_LOAD_CLOB_DATA: 2 Rows successfully loaded. Check the log file: TMP_LOAD_CLOB_DATA.log for more information about the load. I:\>exit SQL> SELECT * 2 FROM TMP_LOAD_CLOB_DATA 3 / CUSTOMER_ID FIRSTNAME LASTNAME CUSTOMER_DETAILS ----------- --------- -------- ------------------------------ 123 ABC XYZ Lot Of Information about customer ABC XYZ which should be loaded into a clob 456 DEF ZZZ Lot Of Information about customer DEF ZZZ which should be loaded into a clob SQL>
SY.
Hi Solomon, Thank you for your response..
Hi, For above is there any way to load CLOB data without having separate files ??
To load lobs, use the insert, update, or merge statement.
What to do when a large text is part of a single file and needs to be inserted with an SQLLDR. eg: it is 498,780 characters long. ID | VALUE | DATA 1 | Dr4wQQ | it is 498,780 characters long...... 2 | WQWQ3 | it is 925,342 characters long...... 3 | DERRQQ | it is 478,998 characters long......
I'll assume CLOB doesn't start with new line. I will also assume, based on your data sample, vertical pipe can't appear in CLOB - otherwise you simply can't use it as field separator. If so, control file TMP_LOAD_CLOB_DATA.CTL:
LOAD DATA INFILE 'C:\MY_TEMP\TMP_LOAD_CLOB_DATA.TXT' "str X'7C0D0A'" INTO TABLE TMP_LOAD_CLOB_DATA FIELDS TERMINATED BY "|" ( ID, VALUE, DATA )
7C is hex ascii code for vertical pipe. 0D0A is new line (CRLF - carriage return, line feed). Now we need to add vertical pipe at the end of each line in data file TMP_LOAD_CLOB_DATA.TXT:
1 | Dr4wQQ | it is 498,780 characters long......| 2 | WQWQ3 | it is 925,342 characters long......| 3 | DERRQQ | it is 478,998 characters long line1 line2 many more lines|
Now we load:
I:\>sqlldr scott@xxx/yyy control=c:\my_temp\tmp_load_clob_data.ctl log=c:\my_temp\tmp_load_clob_data.log SQL*Loader: Release 12.2.0.1.0 - Production on Fri Aug 12 06:55:18 2022 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 2 Commit point reached - logical record count 3 Table TMP_LOAD_CLOB_DATA: 3 Rows successfully loaded. Check the log file: c:\my_temp\tmp_load_clob_data.log for more information about the load. I:\>
Now we check what was loaded:
SQL> select * from tmp_load_clob_data; ID VALUE DATA ---------- ---------- ---------------------------------------- 1 Dr4wQQ it is 498,780 characters long...... 2 WQWQ3 it is 925,342 characters long...... 3 DERRQQ it is 478,998 characters long line1 line2 many more lines SQL>
Hi Did you get a solid solution to this issue? It seems splitting CLOB field isn't that helpful since you still need a unique key to merger them back?