ORA-22806:不是对象或REF:生成XML输出时出错 [英] ORA-22806: not an object or REF : error while generating XML output

问题描述

我正在尝试通过创建一个过程来创建XML输出文件，并将该过程用于并发请求.请找到以下包装程序代码.

I'm trying to create an XML output file by creating a procedure and use that procedure for a concurrent request. Please find the below package-procedure code.

并发程序错误

原因:由于ORA-19202而导致FDPSTP失败:XML处理ORA-22806中发生了错误:不是对象或REF."

"Cause: FDPSTP failed due to ORA-19202: Error occurred in XML processing ORA-22806: not an object or REF".

CREATE OR REPLACE PACKAGE BODY xx_outbound_xml1
AS
   PROCEDURE xx_insert_outbound_xml1 (
      errbuff   OUT   VARCHAR2,
      errcode   OUT   NUMBER
   )
   IS
      ln_ctx        DBMS_XMLGEN.ctxhandle;
      lc_xml_clob   CLOB;
   BEGIN
      ln_ctx :=
         DBMS_XMLGEN.newcontext
            (   'SELECT XMLELEMENT("litleRequest",
                              XMLATTRIBUTES('
             || 'to_char(sysdate,''DDMMYYYHH24MI:SS'')'
             || ' AS "Id"                   ),
                (SELECT 
                    XMLAGG(
                        XMLELEMENT(
                        evalname(Decode((select order_type FROM my_orders WHERE order_id = mo.order_id),''CO'',''"Credit"'',''"Sale"'')).getstringval(),
                        XMLFOREST (
                        mo.order_id AS "orderId",
                        mo.order_type as "orderType"
                                    )
                                )
                        )
  FROM my_orders mo
                )
            ) xml
 FROM dual'
            );
      DBMS_XMLGEN.setrowsettag (ln_ctx, NULL);
      DBMS_XMLGEN.setrowtag (ln_ctx, NULL);
      --DBMS_XMLGEN.setNullHandling(ln_ctx,dbms_xmlgen.EMPTY_TAG);
      lc_xml_clob := DBMS_XMLGEN.getxml (ln_ctx);
      lc_xml_clob := REPLACE (lc_xml_clob, '<XML>', '');
      lc_xml_clob := REPLACE (lc_xml_clob, '</XML>', '');
      DBMS_XSLPROCESSOR.clob2file (lc_xml_clob,
                                   'INBOUNDFOLDER',
                                   'output.xml',
                                   0
                                  );
      DBMS_LOB.freetemporary (lc_xml_clob);
   END xx_insert_outbound_xml1;
END xx_outbound_xml1;

如果订单类型为CO，则标记应为Credit；如果order_type为SO，则标记应为Sales.

If order type is CO then tag should be Credit or if order_type is SO then tag should be Sales.

我的输出应为:

推荐答案

您不需要将DBMS_XMLGEN与XML函数一起使用，因为它已经是XML！错误也来自您试图在非XMLType对象上调用getstringval的事实.

you don't need to use DBMS_XMLGEN with the XML Functions, as its already XML! also the error is coming from the fact that your trying to call getstringval on a non XMLType object.

相反，只需执行以下操作:

instead just do this:

create or replace package body xx_outbound_xml1
as
   procedure xx_insert_outbound_xml1 (
      errbuff   out   varchar2,
      errcode   out   number
   )
   is
      ln_ctx        dbms_xmlgen.ctxhandle;
      lc_xml_clob   clob;
   begin
     select xmlserialize(document
              xmlelement(
                "litleRequest",
                xmlattributes(to_char(sysdate,'DDMMYYYHH24MI:SS')  AS "Id"),
                xmlagg(
                  xmlelement(
                    evalname(Decode(order_type, 'CO','Credit','Sale')),
                    xmlforest (
                      mo.order_id AS "orderId",
                      mo.order_type as "orderType"
                    )
                  )
                )
              )
            ) xml
     into lc_xml_clob
     from my_orders mo;

    dbms_xslprocessor.clob2file (lc_xml_clob,
                                 'INBOUNDFOLDER',
                                 'output.xml',
                                 0
                                );
   end xx_insert_outbound_xml1;
end xx_outbound_xml1;
/

例如，带有该代码(加上我在xmlserialize中添加了缩进"，以使其在此处更容易看到):

eg with that code (plus i added "indent" to the xmlserialize, to make it easier to see here):

SQL> var b varchar2(2000)
SQL> var c number
SQL> exec xx_outbound_xml1.xx_insert_outbound_xml1(:b, :c)

PL/SQL procedure successfully completed.

SQL> host cat /tmp/output.xml
<litleRequest Id="05030131727:20">
  <Sale>
    <orderId>1132339612</orderId>
    <orderType>SO</orderType>
  </Sale>
  <Credit>
    <orderId>1132339530</orderId>
    <orderType>CO</orderType>
  </Credit>
  <Sale>
    <orderId>1132339116</orderId>
    <orderType>SO</orderType>
  </Sale>
  <Credit>
    <orderId>1132338886</orderId>
    <orderType>CO</orderType>
  </Credit>
</litleRequest>

SQL> select * from my_orders;

  ORDER_ID OR
---------- --
1132339612 SO
1132339530 CO
1132339116 SO
1132338886 CO

SQL> 

这篇关于ORA-22806:不是对象或REF:生成XML输出时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！