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!
arktechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arktechAuthor Commented:
Never mind -- just figured out that it was the DISTINCT killing me!  DECODE(SUPPRESS_DISPLAY, 'N', MESSAGE_BODY, NULL) works like a charm.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrjoltcolaCommented:
Try:

CASE WHEN SUPPRESS_DISPLAY = 'Y' THEN TO_CLOB('') ELSE MESSAGE_BODY END
0
mrjoltcolaCommented:
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.

0
mrjoltcolaCommented:
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).
0
mrjoltcolaCommented:
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. :)

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.