An Important Oracle Limitation

The relevant column was defined as VARCHAR2(4000). No problem, I thought, just redefine the column to be 8000 or 16000. As soon as I tried to create the new column, though, I ran into my second error:

 ORA-00910: specified length too long for its datatype

Uh-oh. Problem. Yes, it turns out that Oracle does absolutely limit VARCHAR2 to 4000 characters in SQL. (For what it's worth, it seems the limit is higher for PL/SQL variables. In my case, it wasn't worth much.) I should have paid more attention to the first error message: the literal itself that was too long, independent of what kind of column it would eventually end up in.

I could fix the database error by changing the column to a CLOB. (The maximum length would then be 4 gigabytes.) I would then have to re-write the ColdFusion portion of the code to call a stored procedure with appropriate use of cfprocparam. This second part of the job would be a lot of work; unfortunately, there's not just one column like this, there are many. The number of places I where would have to make changes and re-test is large.

We're going to live with the 4000 character limitation.

There are a number of possible lessons.

know the fundamental limitations of your tools

I should not have been caught out. I'm fairly sure I've run into the 4000 character limit for VARCHAR2 before. [ This is one reason I keep a logbook of sorts here: it helps me to remember what the issues are and how they come up. ]

requirements gathering and setting expectations

One person will agree to a limit but other people are affected by it. It's a small detail but when someone runs into it in the form of a crashing web page it is ugly.

error handling and reporting

At least it didn't fail silently (for example by truncating the input) but a big ugly error page is not what I want. It is unfortunate that the textarea tag does not support an attribute to limit the length of input. Nevertheless, awkward as it is, any code that is taking input from a textarea should be checking for length and reporting an error if necessary. Long input text should be a standard test case.

development trade offs

In this case, we decided almost by default that the cost to implement the obvious fix was too high. There's a discontinuity at 4000 characters: below that, everything is easy. Above that, it gets harder, particularly when you have to retrofit existing code. In part, I think the problem comes down to poor design: a change in column size should not cascade through the whole application like that. On the other hand, it is cheaper to do everything the simple way and leave error handling to the lower layers, even if the result looks ugly.