Avoiding Varchar(4000)

I usually never use varchar(4000) unless there is a damn good reason to do so. Most people don't plan to use the 4000 character limit, but it does seem to be the default in APEX. If you are planning to use varchar(4000) you should probably use a clob instead. Why place an arbitrary limit on a column if you don't want one and you can avoid it?

Perhaps the need to query the column is outweighed by the risk that the user will want to enter more than 4000 characters of data, usually you don't need to directly query large blocks of text. If you don't ever need to search the block of text then just use a clob. Trust me, it will save you much grief in future.

Many times in the past I've peer reviewed data models, systematically replacing varchar(4000) with clob columns. But looks like I didn't follow my own advice on my current project. Now I'm smashing my head against the desk. The users of this shiny new system are all academics, so I should have known it was just a matter of time before someone tried to post a 10 page rant. Now I'm trying to convert the columns to clobs and APEX is being less than forgiving.

So far my approach was to create a temporary clob column, move the varchar data into the clob, drop the varchar and rename the clob. APEX then throws "ORA-01008: not all variables bound" exceptions when I visit the form that previously used the varchar version of the column. I've tried deleting the related page items and the exception went away. Creating new clob based page items keeps giving me that exception though. Gah! I'm this close to just rebuilding the form from scratch.

Take my advice, don't use varchar(4000)!

Update: I now know the trick to resolving ORA-01008 is usually to make a copy of the region and delete the original.