This is really geeky, and mostly a note to people like myself who are having difficulty with ColdFusion MX and Oracle CLOBs. It may be really bloody obvious to some people, but it took me all damn day to figure it out, so I thought I write about it. If you're not interested in CLOBs or ColdFusion MX, sorry :)

****

Today I was trying to fix a ColdFusion problem. My curriculum tracking application users were encountering the error "String literal too long" when trying to add text on a form. The field in the database (Oracle) was set to varchar2(4000), and some users were trying to enter more like 4500 characters. No big deal, we'll just let 'em put in more characters, I thought.

Knowing that Oracle has a limitation on the size of varchar datatypes (and that limitation for Oracle 9.2 is 4K) I thought we'd just switch to a CLOB (character long object) datatype. Oracle CLOBs can store up to 4GB, so that would suit us just fine.

So our DBA, Anita, changed the datatype and I tested it out in our test database. I was still getting the same error: ORA-01704 string literal too long. I tested inserting more than 4000 characters through TOAD, and that worked, so I wasn't sure what was wrong. So I searched the ColdFusion "knowledge base" and couldn't really come up with much.

Anita and I searched for a while trying to come up with something. I read something that led me to believe it was the Oracle driver I was using - apparently the Oracle native driver has some sort of limitation on inserting with SQL. I didn't really understand the nuances of the problem, but it seemed from what I'd read online that if we switched to another driver, that would take care of the problem. Now the problem was that our web guy was out today and wouldn't be back for another week, and I really wanted to get this off my plate sooner than next week.

Anita found some stuff on using an Oracle package DBMS_LOB which has all these nifty functions for manipulating LOBs (CLOBs and BLOBs) and is, I gather, delivered with Oracle. So I started pulling documentation on DBMS_LOB to see what if anything I could make it do for me.

So now it's mid afternoon and I'm still trying to figure out how I'm going to make my application take more than 4000 characters and stuff them into a CLOB. I decided to write a little test page to try using the DBMS_LOB.write function, and had spent about 20 minutes doing so before I realized that the function takes four parameters: loc_lob (location of the LOB), amount (how much you're going to stuff into it), offset (where you want to start stuffing it), and buffer (what you're going to stuff into it). What this package doesn't allow you to specify is what row (or a WHERE clause). Doh.

But then I remembered that at some point during the day, I'd read something about a ColdFusion tag attribute called "cf_sql_clob". Don't know where I read it, in what context or anything, so I just googled: cf_sql_clob. A littel more surfing around took me back to the Macromedia ColdFusion livedocs page about the tag cfqueryparam. Read all the way to the bottom, and some nice user added a comment about how to use the cfqueryparam to do EXACTLY what I want to do.

Long story short, it's much easier to dump lots of text into a CLOB. You don't have to use any special drivers (Oracle native works just fine) and you don't have to write PL/SQL to handle the insert. Just use the cfqueryparam tag in the insert/update SQL (within a cfquery tag), like so:

<cfquery name="example_insert" datasource="datasourcename">
update MyTable
set MyCLOBColumn = <cfqueryparam value="#MyLongTextField#" cfsqltype="cf_sql_clob">
where TableKey = #KeyValue#...
</cfquery>