There are no strings that long in the cee.description columns of my test database - only 100 characters or so, at the most.
I have never tested the query with a description type of varchar, but I would imagine everything would've worked fine doing so. The error when using length() and substr() occured, despite only having strings shorter than 4K, just because the column type is CLOB ... I *want* to go with CLOB because, as you say, it has advantages over varchar for big chunks of text
The LOB functions made the error go away, and the query returned the proper number of rows but, unfortunately, dbms_lob.instr() does not return the substring, while substr() does ... on varchars, that is, not clobs.
I've not had time to study dbms_lob.instr() and clobs any further yet, but even if I don't find a solution I still have the possibility to do the truncation of the description in Tcl, although that seens like a worse way to do it.
I felt like clarifying, but I'm afraid I ended up talking in circles. Oh well.