We have a application in which e-mail messages are stored in an Oracle 9i database. Some users have restricted access, and can only see the subject line, from, to and date for the e-mail, while other users get to see the entire thing. This is handled with a simple CASE WHEN SUPPRESS_DISPLAY = 'Y' THEN '' ELSE MESSAGE_BODY END in the SELECT statement. We've just converted MESSAGE_BODY from a VARCHAR2(4000) to a CLOB, and this expression is no longer working as intended. It truncates the CLOB to 4k bytes in order to match the implicit type of '' (VARCHAR2). While I can easily hide the message on the Java side, I hate having all of this data needlessly transferred across the network connection. Interestingly, CASE WHEN SUPPRESS_DISPLAY = 'Y' THEN MESSAGE_BODY ELSE MESSAGE_BODY END fails as well with an ORA-00932, as does DECODE(SUPPRESS_DISPLAY, 'Y', MESSAGE_BODY, MESSAGE_BODY)!
Any suggestions or ideas? Thanks!