将Oracle存储过程从使用DBMS_XMLSave转换为DBMS_XMLStore

Question

我继承了一个在后端使用Oracle数据库的应用程序。数据库最初使用的是Oracle的完整版本，但我们已经将其移到了Oracle XE。Oracle似乎不支持Oracle实用程序(其中包括DBMS_XMLSave)，或者至少我无法知道如何安装它。

根据这个链接(http://ellebaek.wordpress.com/2011/01/27/converting-between-oracle-data-and-xml/)：

与DBMS_XMLQUERY一样，DBMS_XMLSAVE是用Java实现的，因此版不支持它。

数据库中的存储过程使用DBMS_XMLSave，但从此链接(https://forums.oracle.com/forums/thread.jspa?threadID=530048)看来，DBMS_XMLSave已被DBMS_XMLStore替换：

DBMS_XMLSTORE PL/SQL包是在OracleDatabase10g第1版中引入的。该包根据一个XML的内容对数据库中的关系表或对象表执行DML操作。 注意，在OracleDatabase10g之前，此功能存在于另一个名为DBMS_XMLSAVE的PL/SQL包中。

我在链接中尝试了解决方案：

CREATE OR REPLACE PUBLIC SYNONYM DBMS_XMLSAVE FOR DBMS_XMLSTORE;
GRANT EXECUTE ON DBMS_XMLSAVE TO PUBLIC;

这确实修复了很大一部分错误，但是留下了一些问题行：

DBMS_XMLSave.setDateFormat(v_insCtx, 'MM/dd/yyyy');
DBMS_XMLSave.setBatchSize(v_updCtx, -1);
DBMS_XMLSave.setDateFormat(v_updCtx, 'MM/dd/yyyy');

DBMS_XMLStore似乎不支持这些方法。我想我找到了一种绕过setDateFormat的方法。根据此链接(http://ellebaek.wordpress.com/2011/01/27/converting-between-oracle-data-and-xml/)，DBMS_XMLStore对日期/时间值使用NLS设置。此链接(http://www.tiplib.com/231/nls-parameter-session-logon-trigger)指出，可以在这样的存储过程中更改NLS设置：

BEGIN
    DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','YYYYMMDD');
    COMMIT;
END;

因此，我希望我可以替换如下的setDateFormat行：

-DBMS_XMLSave.setDateFormat(v_insCtx, 'MM/dd/yyyy');  -- remove
+DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','MM/dd/yyyy');  -- insert

我找不到setBatchSize的替代品。

所以我的问题是：

1. 我是以正确的方式处理这个问题，还是有更好的方法？

1. DBMS_XMLSave.setDateFormat(v_insCtx，'MM/dd/yyyy') -> DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT'，'MM/dd/yyyy')会改变工作吗？

1. 有setBatchSize的替代品吗？有必要替换setBatchSize吗？

通常，如果有人问了像上面的问题2这样的问题，我会告诉他们尝试并告诉我，但我目前无法获得代码编译，我没有一个已知的工作基线。我可以删除问题线，它可能会产生一些结果，但我不清楚结果是否正确。从这里开始，删除问题线并尝试检查结果将是我的方法。

原代码如下：

    procedure insert_xml(p_xmlDoc in clob, p_tableName in varchar2) is
        v_insCtx DBMS_XMLSave.ctxType;
        v_rows number;
    begin
      v_insCtx := DBMS_XMLSave.newContext(p_tableName); -- get the context handle
      DBMS_XMLSave.setDateFormat(v_insCtx, 'MM/dd/yyyy'); -- set date format
      v_rows := DBMS_XMLSave.insertXML(v_insCtx, p_xmlDoc); -- this inserts the document
      DBMS_XMLSave.closeContext(v_insCtx); -- this closes the handle
    exception
      when OTHERS then
        raise_application_error(-20001,'An error was encountered. - '||SQLCODE||' -ERROR- '||SQLERRM);
    end insert_xml;

    procedure update_xml(p_xmlDoc in clob, p_tableName in varchar2, p_key in varchar2) is
            v_updCtx DBMS_XMLSave.ctxType;
            v_rows number;
        begin
            v_updCtx := DBMS_XMLSave.newContext(p_tableName); -- get the context
            DBMS_XMLSave.setBatchSize(v_updCtx, -1);
            DBMS_XMLSave.setDateFormat(v_updCtx, 'MM/dd/yyyy'); -- set date format
            DBMS_XMLSave.clearUpdateColumnList(v_updCtx); -- clear the update settings..
            DBMS_XMLSave.setKeyColumn(v_updCtx, p_key); -- set EMPNO as key column
            v_rows := DBMS_XMLSave.updateXML(v_updCtx, p_xmlDoc); -- update the table.
            DBMS_XMLSave.closeContext(v_updCtx); -- close the context..!
      exception
        when OTHERS then
          raise_application_error(-20001,'An error was encountered. - '||SQLCODE||' -ERROR- '||SQLERRM);
        end update_xml;

谢谢你的帮助。

Answer 1

我已经回答了这个问题。当我试着：

DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','MM/DD/YYYY');

我发现我犯了这些错误：

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00222: error received from SAX callback function
ORA-01843: not a valid month

我挖掘了一下之后发现：

EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY"';

这似乎没有得到日期处理的投诉，我想它已经解决了问题，但我不是100%肯定在这个阶段。

我没有替换DBMS_XMLSave.setBatchSize()；我不确定这是否会影响性能。

我没有直接使用公共同义词，而是直接修改了代码，它现在看起来如下所示：

    procedure insert_xml(p_xmlDoc in clob, p_tableName in varchar2) is
        v_insCtx DBMS_XMLStore.ctxType;
        v_rows number;
    begin
        v_insCtx := DBMS_XMLStore.newContext(p_tableName); -- get the context handle
        EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY"';
        v_rows := DBMS_XMLStore.insertXML(v_insCtx, p_xmlDoc); -- this inserts the document
        DBMS_XMLStore.closeContext(v_insCtx); -- this closes the handle
    exception
        when OTHERS then
          raise_application_error(-20001,'An error was encountered. - '||SQLCODE||' -ERROR- '||SQLERRM);
    end insert_xml;
    procedure update_xml(p_xmlDoc in clob, p_tableName in varchar2, p_key in varchar2) is
        v_updCtx DBMS_XMLStore.ctxType;
        v_rows number;
    begin
        v_updCtx := DBMS_XMLStore.newContext(p_tableName); -- get the context
        EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY"';
        DBMS_XMLStore.clearUpdateColumnList(v_updCtx); -- clear the update settings..
        DBMS_XMLStore.setKeyColumn(v_updCtx, p_key); -- set EMPNO as key column
        v_rows := DBMS_XMLStore.updateXML(v_updCtx, p_xmlDoc); -- update the table.
        DBMS_XMLStore.closeContext(v_updCtx); -- close the context..!
    exception
        when OTHERS then
          raise_application_error(-20001,'An error was encountered. - '||SQLCODE||' -ERROR- '||SQLERRM);
        end update_xml;