Re: SELECT A CLOB - GROUP BY

On Apr 3, 3:56 pm, trp..._at_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? Received on Fri Apr 04 2008 - 09:47:39 CDT