Oracle 12c (release 1) has increased the maximum length of character-based columns to 32K bytes – don’t get too excited, they’re stored out of line (so similar in overheads to LOBs) and need some modification to the parameter file and data dictionary (including a step that starts the database in upgrade mode) before you can use them.
Richard Foote has a pair of articles on indexing such columns:
Be cautious about enabling this option and test carefully – there are going to be a number of side effects and some of them may require a significant investment in time to resolve. The first one that came to my mind was that if you’ve created a function-based index on a pl/sql function that returns a varchar2() type and haven’t explicitly created the index on a substr() of the return value then the data type of the function’s return value will change from the current default of varchar2(4000) to varchar2(32767) – which means the index will become invalid and can’t be rebuilt or recreated.
Obviously you can redefine the index to include an explicit substr() call – but then you have to find all the code that was supposed to use the index and modify it accordingly.
Footnote: don’t forget that everything may change with the arrival of 12.2.