CLOBbered

I've spent a few hours looking through all my Oracle books and scanning some web sites and I can't find a simple, straightforward answer to what seems to me to be a simple, very common task. I want to insert the contents of a plain text file (some files are up to 320 KB) into a CLOB column which I need to add to an existing table. I thought for sure there would be a built-in function so I could issue something like:

UPDATE news
SET article = InsertTextFile('C:\articles\article1.txt') WHERE title = 'Article One';

All of my books talk about how CLOBs are better than LONG, but not a single example about how to put data into a CLOB. Then on the web, I ran across people not being able to put more than 4000 bytes in at a time, then initializing an empty CLOB and writing a script to work with it. Finally giving up and just making a table with varchar2()'s and breaking up the text file into several rows and reassembling it when needed.

I looked at SQL*Loader, but I think I'll need to modify the text files and add a [BOF] and [EOF] because the text files have all sorts of English dialog in them with lots of commas, semi-colons, apostrophes, etc.

I hope some of you have conquered this problem and can tell me:

1. Which tool should I use to get text files into CLOBs?
2. Are there any nasty pitfalls I should be aware of?

Thanks! -Paul Murphy Received on Mon Apr 14 2003 - 18:47:12 CDT