Path: text.usenetserver.com!out04b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!news1.google.com!news.glorb.com!tudelft.nl!txtfeed1.tudelft.nl!feeder.news-service.com!newsfeed.xs4all.nl!newsfeed2.news.xs4all.nl!newsgate.cistron.nl!xs4all!transit4.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
From: "Shakespeare" <whatsin@xs4all.nl>
Newsgroups: comp.databases.oracle.misc
References: <8d94c19f-ddf2-4b3b-92b9-bb0f4e747bd8@s50g2000hsb.googlegroups.com> <132260f0-7ad9-4fe6-b9b1-e4c4904f366e@24g2000hsh.googlegroups.com>
Subject: Re: SELECT A CLOB - GROUP BY
Date: Fri, 4 Apr 2008 17:21:20 +0200
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
Lines: 55
Message-ID: <47f64770$0$14351$e4fe514c@news.xs4all.nl>
NNTP-Posting-Host: 82.95.215.210
X-Trace: 1207322480 news.xs4all.nl 14351 [::ffff:82.95.215.210]:56354
X-Complaints-To: abuse@xs4all.nl
Xref: usenetserver.com comp.databases.oracle.misc:252352
X-Received-Date: Fri, 04 Apr 2008 10:21:20 EST (text.usenetserver.com)


<trpost@gmail.com> schreef in bericht 
news:132260f0-7ad9-4fe6-b9b1-e4c4904f366e@24g2000hsh.googlegroups.com...
On Apr 3, 3:56 pm, trp...@gmail.com wrote:
> Hi,
>
> I am having trouble selecting a CLOB (CASE_LIST), here is what
> happens:
>
> SELECT PRODUCT_NAME AS "Product Name", SUM(CASES_CREATED) AS "Cases
> Created",
> CASE_LIST
> FROM PEOPLESOFTBUCUSTPROD
> GROUP BY PRODUCT_NAME, CASE_LIST
> ORDER BY PRODUCT_NAME
>
> I get this error: ORA-00932: inconsistent datatypes: expected - got
> CLOB
>
> Then I did this, using TO_CHAR:
>
> SELECT PRODUCT_NAME AS "Product Name", SUM(CASES_CREATED) AS "Cases
> Created",
> TO_CHAR(CASE_LIST)
> FROM PEOPLESOFTBUCUSTPROD
> GROUP BY PRODUCT_NAME, TO_CHAR(CASE_LIST)
> ORDER BY PRODUCT_NAME
>
> I get this error: ORA-22835: Buffer too small for CLOB to CHAR or BLOB
> to RAW conversion (actual: 4997, maximum: 4000)
>
> So lastly I did this, using DBMS_LOB.SUBSTR:
>
> SELECT PRODUCT_NAME AS "Product Name", SUM(CASES_CREATED) AS "Cases
> Created",
> DBMS_LOB.SUBSTR(CASE_LIST, 4000, 1) CASE_LIST
> FROM PEOPLESOFTBUCUSTPROD
> GROUP BY PRODUCT_NAME, DBMS_LOB.SUBSTR(CASE_LIST, 4000, 1)
> ORDER BY PRODUCT_NAME
>
> I am now able to get results, but my data is truncated at 4000
> characters, which will not work
>
> Any ideas on how to get the full clob results in one query?

> Is it possible to Select a CLOB in a GROUP BY? Is there a work around
> for when TO_CHAR exceeds 4000 characters?

Don't put your CLOB in the group by clause, just in the select and only 
group by product_name. I don't expect you really want to group by a CLOB, do 
you?

Shakespeare