Solved

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

Posted on 2009-07-06
5
527 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

760 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now