| Sql Loader - oracle [message #669367] |
Tue, 17 April 2018 21:52  |
 |
gopinathan
Messages: 1
Registered: April 2018
|
Junior Member |
|
|
My table column type is clob. Field value contain double quotes
id,text_clob,date
1,"\"data1\",0,1,1,1,\"data2\"","2018-03-03 09:08:45"
can you kindly help loader query. i cant use replace in clob field . Its a 20GB data so i cant do any manual replace
|
|
|
|
| Re: Sql Loader - oracle [message #669368 is a reply to message #669367] |
Tue, 17 April 2018 22:25  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
gopinathan wrote on Tue, 17 April 2018 19:52My table column type is clob. Field value contain double quotes
id,text_clob,date
1,"\"data1\",0,1,1,1,\"data2\"","2018-03-03 09:08:45"
can you kindly help loader query. i cant use replace in clob field . Its a 20GB data so i cant do any manual replace
Welcome to this forum
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
you need to write PL/SQL to process the LOB a chunk (less than 4000 characters) at a time to replace the double quote marks
|
|
|
|
| Re: Sql Loader - oracle [message #669381 is a reply to message #669367] |
Wed, 18 April 2018 18:31 |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You cannot expect SQL*Loader to be able to tell where the clob data ends when it contains both the delimiter character of comma and enclosure characters of double quotes. You need to find a way to get the data in another format with a different field delimiter.
If you can't get the data in another format and there is just the id, clob, and date, in that order, then you could load everything after the id into the clob, then parse out the date in an update statement after loading. This might require that you load into a staging table, then do the update, then insert from the staging table into the target table.
[Updated on: Wed, 18 April 2018 18:34] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
