Solved

ORA-00932 when trying to conditionally SELECT CLOB in Oracle 9ii

Posted on 2009-07-06
5
530 Views
Last Modified: 2013-12-19
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!
0
Comment
Question by:arktech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 

Accepted Solution

by:
arktech earned 0 total points
ID: 24790188
Never mind -- just figured out that it was the DISTINCT killing me!  DECODE(SUPPRESS_DISPLAY, 'N', MESSAGE_BODY, NULL) works like a charm.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24790194
Try:

CASE WHEN SUPPRESS_DISPLAY = 'Y' THEN TO_CLOB('') ELSE MESSAGE_BODY END
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24790216
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
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24790457
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
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24790489
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question