XMLAGG-ORA-00932:不一致的数据类型：CLOB上预期的-得到的CLOB

Question

    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

更新1

DDL和示例数据

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;

Answer 1

问题是distinct和order by。Oracle不允许在CLOB上执行这些操作。您使用的是group by，因此不需要distinct。

如果你不介意描述的顺序，下面的内容将会奏效。

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;

如果必须按其排序，则可以将clob强制转换为varchar2并按其排序：

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