Oracle – XMLAGG – ORA-00932: inconsistent datatypes: expected – got CLOB on CLOB

cloboracleoracle11g

I have the following SQL query:

    SELECT DISTINCT
         prod_no,
         prod_text,
         RTRIM (
            XMLAGG (XMLELEMENT (e, prod_desc, ',').EXTRACT (
                       '//text()') ORDER BY prod_desc).getclobval (),
            ',')
    FROM mytable
   WHERE prod_no = 'XCY'
GROUP BY prod_no,
         prod_text

When I execute I am getting

ORA-00932: inconsistent datatypes: expected – got CLOB

Update 1

DDL and sample data

CREATE TABLE mytable
(
   prod_no     VARCHAR2 (30 BYTE) NOT NULL,
   prod_text   VARCHAR2 (30 BYTE) NOT NULL,
   prod_desc   CLOB
);

SET DEFINE OFF;

INSERT INTO mytable (prod_no, prod_text, prod_desc)
     VALUES ('XCY', 'DECKS', 'THIS IS TEST');

INSERT INTO mytable (prod_no, prod_text, prod_desc)
     VALUES ('ABC', 'DECKS', 'THIS IS TEST 2');

COMMIT;

Best Answer

Issue is with DISTINCT and ORDER BY. Oracle doesn't allow these operation on CLOB. You are using group by, so you don't need DISTINCT anyway.

The below will work, if you don't mind the order of description.

SELECT 
         prod_no,
         prod_text,
         RTRIM (
            XMLAGG (XMLELEMENT (e, prod_desc, ',') ).EXTRACT (
                       '//text()').getclobval (),
            ',')
    FROM mytable
   WHERE prod_no = 'XCY'
GROUP BY prod_no,
         prod_text;

If you must order by it, you can cast the CLOB to varchar2 and order by it:

SELECT
         prod_no,
         prod_text,
         RTRIM (
            XMLAGG (XMLELEMENT (e, prod_desc, ',')
                ORDER BY cast(prod_desc as varchar2(4000))).EXTRACT (
                       '//text()').getclobval (),
            ',')
    FROM mytable
   WHERE prod_no = 'XCY'
GROUP BY prod_no,
         prod_text
Related Topic