Home » RDBMS Server » Server Utilities » Sql Loader - oracle
Sql Loader - oracle [message #669367] Tue, 17 April 2018 21:52 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
gopinathan wrote on Tue, 17 April 2018 19:52
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

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 Go to previous message
Barbara Boehmer
Messages: 8919
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

Previous Topic: Moving entire DB
Next Topic: Bulk insert with SQL*Loader fails to abort inserts on index errors
Goto Forum:
  


Current Time: Sun Oct 20 16:23:08 CDT 2019