?
Solved

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

Posted on 2009-07-06
5
Medium Priority
?
531 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

770 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