arktech
asked on
ORA-00932 when trying to conditionally SELECT CLOB in Oracle 9ii
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!
Any suggestions or ideas? Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well, your own solution was not really the answer to the code you posted. You fixed it by changing the '' to NULL, which changes the return type of the conditional (whether using CASE or DECODE). So its fixed, but not if you wanted to receive empty string from the conditional.
You could have done the same thing with the CASE statement.
CASE WHEN SUPPRESS_DISPLAY = 'Y' THEN NULL ELSE MESSAGE_BODY END
Good luck.
You could have done the same thing with the CASE statement.
CASE WHEN SUPPRESS_DISPLAY = 'Y' THEN NULL ELSE MESSAGE_BODY END
Good luck.
Hi, I tested the statements on larger than 4000 length clobs and the DECODE() you used will fail with ORA-22835
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6528, maximum: 4000)
You should change this to use the TO_CLOB(null) so the DECODE() use CLOB as its conversion type.
I also would like to reopen the question, since your solution is not correct for clobs longer than 4000 in length (which is why we use clob in the first place).
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 6528, maximum: 4000)
You should change this to use the TO_CLOB(null) so the DECODE() use CLOB as its conversion type.
I also would like to reopen the question, since your solution is not correct for clobs longer than 4000 in length (which is why we use clob in the first place).
I am sorry for the spam, but this is the key that must be clear for the question.
These work because MESSAGE_BODY is first in the expression:
DECODE(SUPPRESS_DISPLAY, 'N', MESSAGE_BODY, NULL)
DECODE(SUPPRESS_DISPLAY, 'N', MESSAGE_BODY, '')
It will convert to CLOB because TEXT is the 1st expression for a return value, so Oracle chooses that type for the whole DECODE()
But if you used the following order:
DECODE(SUPPRESS_DISPLAY, 'Y', '', MESSAGE_BODY)
Will cause the expression to be converted to a VARCHAR, and an ORA-22835 will result. So in this case I think it would be safest to explicitly provide the conversion so the ordering is not left up to Oracle.
DECODE(SUPPRESS_DISPLAY, 'Y', TO_CLOB(''), MESSAGE_BODY)
Thanks, I learned something and I hope you did too. :)
These work because MESSAGE_BODY is first in the expression:
DECODE(SUPPRESS_DISPLAY, 'N', MESSAGE_BODY, NULL)
DECODE(SUPPRESS_DISPLAY, 'N', MESSAGE_BODY, '')
It will convert to CLOB because TEXT is the 1st expression for a return value, so Oracle chooses that type for the whole DECODE()
But if you used the following order:
DECODE(SUPPRESS_DISPLAY, 'Y', '', MESSAGE_BODY)
Will cause the expression to be converted to a VARCHAR, and an ORA-22835 will result. So in this case I think it would be safest to explicitly provide the conversion so the ordering is not left up to Oracle.
DECODE(SUPPRESS_DISPLAY, 'Y', TO_CLOB(''), MESSAGE_BODY)
Thanks, I learned something and I hope you did too. :)
CASE WHEN SUPPRESS_DISPLAY = 'Y' THEN TO_CLOB('') ELSE MESSAGE_BODY END