ORA-02287: 在Oracle XML创建中不允许使用序列号,XMLAGG

huangapple go评论61阅读模式
英文:

ORA-02287: sequence number not allowed here in Oracle Xml creation with XMLAGG

问题

I am trying to create an XML document in Oracle. It should have a sequence value in it at the top level, and some line level items. The query is working without the line items, as an example

WITH j AS (
  SELECT
    'abc' test
  FROM
    dual
  GROUP BY
    1
)
SELECT
  XMLSERIALIZE(DOCUMENT XMLELEMENT(
    "WhseSnapshot",
    XMLELEMENT(
      "mdDC",
      MD_EXTRACT_ID_SEQ.NEXTVAL
    )
  ))
FROM
  j;

produces the output

<WhseSnapshot><mdDC>157</mdDC></WhseSnapshot>

Adding the line items to the query as follows

WITH j AS (
  SELECT
    'abc' test
  FROM
    dual
  GROUP BY
    1
)
SELECT
  XMLSERIALIZE(DOCUMENT XMLELEMENT(
    "WhseSnapshot",
    XMLELEMENT(
      "mdDC",
      MD_EXTRACT_ID_SEQ.NEXTVAL
    ),
    XMLAGG(XMLELEMENT(
      "line",
           XMLFOREST(j.test AS "test")
    ))
  ))
FROM
  j;

results in the following error

ORA-02287: sequence number not allowed here
02287. 00000 -  "sequence number not allowed here"
*Cause:    The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
           here in the statement.
*Action:   Remove the sequence number.
Error at Line: 84 Column: 25

Has anyone been able to achieve a similar goal? I am hoping to avoid a PL/SQL block or function and stick to regular SQL if possible.

英文:

I am trying to create an XML document in Oracle. It should have a sequence value in it at the top level, and some line level items. The query is working without the line items, as an example

WITH j AS (
  SELECT
    &#39;abc&#39; test
  FROM
    dual
  GROUP BY
    1
)
SELECT
  XMLSERIALIZE(DOCUMENT XMLELEMENT(
    &quot;WhseSnapshot&quot;,
    XMLELEMENT(
      &quot;mdDC&quot;,
      MD_EXTRACT_ID_SEQ.NEXTVAL
    )
  ))
FROM
  j;

produces the output

&lt;WhseSnapshot&gt;&lt;mdDC&gt;157&lt;/mdDC&gt;&lt;/WhseSnapshot&gt;

Adding the line items to the query as follows

WITH j AS (
  SELECT
    &#39;abc&#39; test
  FROM
    dual
  GROUP BY
    1
)
SELECT
  XMLSERIALIZE(DOCUMENT XMLELEMENT(
    &quot;WhseSnapshot&quot;,
    XMLELEMENT(
      &quot;mdDC&quot;,
      MD_EXTRACT_ID_SEQ.NEXTVAL
    ),
    XMLAGG(XMLELEMENT(
      &quot;line&quot;,
           XMLFOREST(j.test AS &quot;test&quot;)
    ))
  ))
FROM
  j;

results in the following error

ORA-02287: sequence number not allowed here
02287. 00000 -  &quot;sequence number not allowed here&quot;
*Cause:    The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
           here in the statement.
*Action:   Remove the sequence number.
Error at Line: 84 Column: 25

Has anyone been able to achieve a similar goal? I am hoping to avoid a PL/SQL block or function and stick to regular SQL if possible.

答案1

得分: 3

你可以在子查询中聚合表格:

WITH j (test) AS (
  SELECT 'abc'
  FROM   dual
)
SELECT XMLSERIALIZE(
         DOCUMENT
         XMLELEMENT(
           "WhseSnapshot",
           XMLELEMENT(
             "mdDC",
             MD_EXTRACT_ID_SEQ.NEXTVAL
           ),
           ( SELECT XMLAGG(
                      XMLELEMENT(
                        "line",
                        XMLFOREST(
                          j.test AS "test"
                        )
                      )
                    )
             FROM   j
           )
         )
       ) AS xml
FROM   DUAL;

输出结果如下:

XML
1abc

fiddle

英文:

You can aggregate the table in a sub-query:

WITH j (test) AS (
  SELECT &#39;abc&#39;
  FROM   dual
)
SELECT XMLSERIALIZE(
         DOCUMENT
         XMLELEMENT(
           &quot;WhseSnapshot&quot;,
           XMLELEMENT(
             &quot;mdDC&quot;,
             MD_EXTRACT_ID_SEQ.NEXTVAL
           ),
           ( SELECT XMLAGG(
                      XMLELEMENT(
                        &quot;line&quot;,
                        XMLFOREST(
                          j.test AS &quot;test&quot;
                        )
                      )
                    )
             FROM   j
           )
         )
       ) AS xml
FROM   DUAL;

Which outputs:

XML
&lt;WhseSnapshot>&lt;mdDC>1&lt;/mdDC>&lt;line>&lt;test>abc&lt;/test>&lt;/line>&lt;/WhseSnapshot>

fiddle

huangapple
  • 本文由 发表于 2023年5月25日 05:09:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/76327420.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定