更新时间:2024-06-03 GMT+08:00

DBE_XMLGEN

接口介绍

DBE_XMLGEN系统包将SQL查询的结果转换为规范的XML格式,并将结果返回。支持的所有接口参考表2 DBE_XMLGEN

表1 DBE_XMLGEN数据类型

类型名称

描述

DBE_XMLGEN.CTXHANDLE

用于存储XML输出状态的数据类型。

  1. 在同一个session中context handle最多只允许存在65535个。关闭context handle并不会回收这个数量。
  2. 输出的xml中表字段、类型与用户创建的表字段与类型大小写一致,如果需要大写字段与类型名需要在创建时用双引号包裹,显示指定。
  3. NEWCONTEXTFROMHIERARCHY初始化时,使用SETNULLHANDLING、USENULLATTRIBUTEINDICATOR、SETCONVERTSPECIALCHARS方法设置但是不生效。
表2 DBE_XMLGEN

接口名称

描述

DBE_XMLGEN.CONVERT

将输入的字符串进行xml编码或解码操作。

DBE_XMLGEN.NEWCONTEXT

初始化普通context handle。

DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY

初始化带有递归元素的context handle。

DBE_XMLGEN.SETCONVERTSPECIALCHARS

设置输出的xml是否需要xml编码。

DBE_XMLGEN.SETNULLHANDLING

设置xml中null值如何展示。

DBE_XMLGEN.SETROWSETTAG

设置xml根节点名称。

DBE_XMLGEN.SETROWTAG

设置xml中每一行数据的tag名。

DBE_XMLGEN.USENULLATTRIBUTEINDICATOR

对xml中的null值所在的元素添加xsi:nil="true"属性。

DBE_XMLGEN.USEITEMTAGSFORCOLL

对数组类型变量所在的元素中添加'_item'后缀。

DBE_XMLGEN.GETNUMROWSPROCESSED

查看上一次getxml或者getxmltype返回的数据行数。

DBE_XMLGEN.SETMAXROWS

设置getxml最大的返回行数。

DBE_XMLGEN.SETSKIPROWS

设置跳过sql行数。

DBE_XMLGEN.RESTARTQUERY

重启sql。

DBE_XMLGEN.GETXMLTYPE

返回XMLTYPE类型的xml文本。

DBE_XMLGEN.GETXML

返回CLOB类型的xml文本。

DBE_XMLGEN.CLOSECONTEXT

关闭context handle。

  • DBE_XMLGEN.CONVERT

    将输入的字符串进行xml编码或解码操作。

    会按以下规则进行转换

    表3 XML编码规则

    原始值

    编码值

    &

    &

    <

    &lt;

    >

    &gt;

    "

    &quot;

    '

    &apos;

    函数原型:

    DBE_XMLGEN.CONVERT(XMLSTR IN VARCHAR2, FLAG IN NUMBER := 0) RETURNS VARCHAR2;
    DBE_XMLGEN.CONVERT(XMLCLOB IN CLOB, FLAG IN NUMBER := 0) RETURNS CLOB;

    参数说明:

    表4 DBE_XMLGEN.CONVERT接口参数说明

    参数

    描述

    XMLSTR

    需要转换的XML字符串,VARCHAR2类型。

    XMLCLOB

    需要转换的XML字符串, CLOB类型。

    FLAG

    转码或解码字符串。

    0:编码操作。

    1:解码操作。

    示例:

    -- xml解码
    SELECT DBE_XMLGEN.CONVERT('<foo/>', 1);
     convert 
    ---------
     <foo/>
    (1 row)
    -- xml编码
    SELECT DBE_XMLGEN.CONVERT('<foo><qwe</foo>', 0);
                convert             
    --------------------------------
     &lt;foo&gt;&lt;qwe&lt;/foo&gt;
    (1 row)
  • DBE_XMLGEN.NEWCONTEXT

    初始化普通context handle。

    函数原型:

    DBE_XMLGEN.NEWCONTEXT(QUERYSTRING IN VARCHAR2) RETURNS DBE_XMLGEN.CTXHANDLE;
    DBE_XMLGEN.NEWCONTEXT(QUERYSTRING IN SYS_REFCURSOR) RETURNS DBE_XMLGEN.CTXHANDLE;

    参数说明:

    表5 DBE_XMLGEN.NEWCONTEXT接口参数说明

    参数

    描述

    QUERYSTRING

    用于生成XML的查询SQL语句或SYS_REFCURSOR。

    示例:

    -- 预置数据。
    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
    INSERT INTO department VALUES(10, 'administrator', 200, 1700);
    INSERT INTO department VALUES(11, 'aaa', 200, 1700);
    INSERT INTO department VALUES(12, 'bbb', 300, 1600);
    INSERT INTO department VALUES(13, 'ccc', 400, 1600);
    INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
    INSERT INTO department VALUES(15, NULL, 500,1600);
    INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
    -- 初始化普通context handle。
    DECLARE
        qryctx DBE_XMLGEN.CTXHANDLE;
        result CLOB;
    BEGIN
     qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * FROM DEPARTMENT ORDER BY DEPARTMENT_ID');
     result:=DBE_XMLGEN.GETXML(qryctx);
     DBE_XMLGEN.CLOSECONTEXT(qryctx);
     DBE_OUTPUT.PUT_LINE(result);
    END;
    /
    
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>10</department_id>
      <department_name>administrator</department_name>
      <manager>200</manager>
      <location>1700</location>
    </row>
    <row>
      <department_id>11</department_id>
      <department_name>aaa</department_name>
      <manager>200</manager>
      <location>1700</location>
    </row>
    <row>
      <department_id>12</department_id>
      <department_name>bbb</department_name>
      <manager>300</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>13</department_id>
      <department_name>ccc</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>14</department_id>
      <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>15</department_id>
      <manager>500</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>16</department_id>
      <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>
    
    -- 初始化普通context handle。
    DECLARE
        lr SYS_REFCURSOR;
        qryctx DBE_XMLGEN.CTXHANDLE;
        result XMLTYPE;
    BEGIN
        OPEN lr FOR SELECT department_id, department_name FROM DEPARTMENT ORDER BY DEPARTMENT_ID;
        qryctx:=DBE_XMLGEN.NEWCONTEXT(lr);
     result:=DBE_XMLGEN.GETXMLTYPE(qryctx);
     DBE_XMLGEN.CLOSECONTEXT(qryctx);
     DBE_OUTPUT.PUT_LINE(result.getclobval);
    END;
    /
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>10</department_id>
      <department_name>administrator</department_name>
    </row>
    <row>
      <department_id>11</department_id>
      <department_name>aaa</department_name>
    </row>
    <row>
      <department_id>12</department_id>
      <department_name>bbb</department_name>
    </row>
    <row>
      <department_id>13</department_id>
      <department_name>ccc</department_name>
    </row>
    <row>
      <department_id>14</department_id>
      <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
    </row>
    <row>
      <department_id>15</department_id>
    </row>
    <row>
      <department_id>16</department_id>
      <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
    </row>
    </rowset>
  • DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY

    初始化带有递归元素的context handle。

    数据格式要求为两列,第一列为numeric类型,第二列为xml或XMLTYPE类型。通常情况下由connect by语句生成,第一列指定生成level。

    生成的xml层级嵌套不允许超过5000万层。

    函数原型:

    DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY(QUERYSTRING IN VARCHAR2);

    参数说明:

    表6 DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY接口参数说明

    参数

    描述

    QUERYSTRING

    需要转换的XML字符串,VARCHAR2类型。

    示例:

    -- 预置数据。
    CREATE TABLE tree_list(id NUMBER, name VARCHAR2(30), fid NUMBER);
    INSERT INTO tree_list VALUES(1, 'a', NULL);
    INSERT INTO tree_list VALUES(6, 'a-1-2', 2);
    INSERT INTO tree_list VALUES(2, 'a-1', 1);
    INSERT INTO tree_list VALUES(3, 'a-2', 1);
    INSERT INTO tree_list VALUES(4, 'a-3', 1);
    INSERT INTO tree_list VALUES(5, 'a-1-1', 2);
    INSERT INTO tree_list VALUES(7, 'a-2-1', 3);
    INSERT INTO tree_list VALUES(8, 'a-2-2', 3);
    INSERT INTO tree_list VALUES(9, 'a-3-1', 4);
    INSERT INTO tree_list VALUES(10, 'a-3-2', 4);
    INSERT INTO tree_list VALUES(11, 'a-3-2-1', 10);
    INSERT INTO tree_list VALUES(12, 'a-3-2-1-1', 11);
    INSERT INTO tree_list VALUES(13, 'a-3-2-1-1-1', 12);
    INSERT INTO tree_list VALUES(14, 'a-3-2-1-1-1-1', 13);
    INSERT INTO tree_list VALUES(15, 'a-3-2-1-1-1-1-1', 14);
    INSERT INTO tree_list VALUES(16, NULL, 14);
    INSERT INTO tree_list VALUES(17, '<?q>', 14);
    -- 递归xml生成。
    DECLARE
    qryctx DBE_XMLGEN.CTXHANDLE;
    result CLOB;
    BEGIN
     qryctx := DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY('SELECT level, xmlelement("children", xmlelement("node_name", name)) ss from tree_list start with id=1 connect by prior id=fid');
     DBE_XMLGEN.USENULLATTRIBUTEINDICATOR(qryctx, true);
     result:=DBE_XMLGEN.GETXML(qryctx);
     DBE_XMLGEN.CLOSECONTEXT(qryctx);
     DBE_OUTPUT.PUT_LINE(result);
    END;
    /
    <?xml version="1.0" encoding="utf-8"?>
    <children>
      <node_name>a</node_name>
      <children>
        <node_name>a-3</node_name>
        <children>
          <node_name>a-3-2</node_name>
          <children>
            <node_name>a-3-2-1</node_name>
            <children>
              <node_name>a-3-2-1-1</node_name>
              <children>
                <node_name>a-3-2-1-1-1</node_name>
                <children>
                  <node_name>a-3-2-1-1-1-1</node_name>
                  <children>
                    <node_name>&lt;?q&gt;</node_name>
                  </children>
                  <children>
                    <node_name/>
                  </children>
                  <children>
                    <node_name>a-3-2-1-1-1-1-1</node_name>
                  </children>
                </children>
              </children>
            </children>
          </children>
        </children>
        <children>
          <node_name>a-3-1</node_name>
        </children>
      </children>
      <children>
        <node_name>a-2</node_name>
        <children>
          <node_name>a-2-2</node_name>
        </children>
        <children>
          <node_name>a-2-1</node_name>
        </children>
      </children>
      <children>
        <node_name>a-1</node_name>
        <children>
          <node_name>a-1-1</node_name>
        </children>
        <children>
          <node_name>a-1-2</node_name>
        </children>
      </children>
    </children>
  • DBE_XMLGEN.SETCONVERTSPECIALCHARS

    设置输出的xml是否需要xml编码。取消xml编码可能会存在xml注入问题,如果出于性能考虑且可以保证xml是安全的情况下可以不进行xml编码。

    函数原型:

    DBE_XMLGEN.SETCONVERTSPECIALCHARS(CTX IN DBE_XMLGEN.CTXHANDLE, CONV IN BOOLEAN);

    参数说明:

    表7 DBE_XMLGEN.SETCONVERTSPECIALCHARS接口参数说明

    参数

    描述

    CTX

    context handle。

    CONV

    是否需要对输出的xml进行编码。

    • true:编码。
    • false:不编码。

    示例:

    -- 预置数据。
    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
    INSERT INTO department VALUES(10, 'administrator', 200, 1700);
    INSERT INTO department VALUES(11, 'aaa', 200, 1700);
    INSERT INTO department VALUES(12, 'bbb', 300, 1600);
    INSERT INTO department VALUES(13, 'ccc', 400, 1600);
    INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
    INSERT INTO department VALUES(15, NULL, 500,1600);
    INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
    -- xml编码。
    DECLARE
        qryctx DBE_XMLGEN.CTXHANDLE;
        result CLOB;
    BEGIN
     qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=14');
     DBE_XMLGEN.SETCONVERTSPECIALCHARS(qryctx, true);
     result:=DBE_XMLGEN.GETXML(qryctx);
     DBE_XMLGEN.CLOSECONTEXT(qryctx);
     DBE_OUTPUT.PUT_LINE(result);
    END;
    /
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>14</department_id>
      <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>
    -- 不进行编码。
    DECLARE
        qryctx DBE_XMLGEN.CTXHANDLE;
        result CLOB;
    BEGIN
    	qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=14');
    	DBE_XMLGEN.SETCONVERTSPECIALCHARS(qryctx, false);
    	result:=DBE_XMLGEN.GETXML(qryctx);
    	DBE_XMLGEN.CLOSECONTEXT(qryctx);
    	DBE_OUTPUT.PUT_LINE(result);
    END;
    /
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>14</department_id>
      <department_name>aaa</row><a>asd</a><row></department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>
    -- 对其余非xml的特殊字符不进行编码。
    DECLARE
        qryctx DBE_XMLGEN.CTXHANDLE;
        result CLOB;
    BEGIN
    	qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=16');
    	DBE_XMLGEN.SETCONVERTSPECIALCHARS(qryctx, true);
    	result:=DBE_XMLGEN.GETXML(qryctx);
    	DBE_XMLGEN.CLOSECONTEXT(qryctx);
    	DBE_OUTPUT.PUT_LINE(result);
    END;
    /
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>16</department_id>
      <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>
  • DBE_XMLGEN.SETNULLHANDLING

    设置xml中null值如何展示。

    函数原型:

    DBE_XMLGEN.SETNULLHANDLING(CTX IN DBE_XMLGEN.CTXHANDLE, FLAG IN NUMBER := 0);

    参数说明:

    表8 DBE_XMLGEN.SETNULLHANDLING接口参数说明

    参数

    描述

    CTX

    context handle。

    FLAG

    NULL值展示格式。

    0:不展示元素。

    1:元素上添加 xsi:nil="true" 属性。

    2:展示自闭合元素

    示例:

    -- 预置数据。
    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
    INSERT INTO department VALUES(10, 'administrator', 200, 1700);
    INSERT INTO department VALUES(11, 'aaa', 200, 1700);
    INSERT INTO department VALUES(12, 'bbb', 300, 1600);
    INSERT INTO department VALUES(13, 'ccc', 400, 1600);
    INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
    INSERT INTO department VALUES(15, NULL, 500,1600);
    INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
    
    -- 不用nullhandling的默认值。
    DECLARE
        result CLOB;
        qryctx DBE_XMLGEN.CTXHANDLE;
    BEGIN
    	qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=15');
    	result:=DBE_XMLGEN.GETXML(qryctx);
    	DBE_OUTPUT.PUT_LINE(result);
    	DBE_XMLGEN.CLOSECONTEXT(qryctx);
    END;
    /
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>15</department_id>
      <manager>500</manager>
      <location>1600</location>
    </row>
    </rowset>
    -- nullhandling为0
    DECLARE
        result CLOB;
        qryctx DBE_XMLGEN.CTXHANDLE;
    BEGIN
    	qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=15');
    	DBE_XMLGEN.SETNULLHANDLING(qryctx, 0);
    	result:=DBE_XMLGEN.GETXML(qryctx);
    	DBE_OUTPUT.PUT_LINE(result);
    	DBE_XMLGEN.CLOSECONTEXT(qryctx);
    END;
    /
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>15</department_id>
      <manager>500</manager>
      <location>1600</location>
    </row>
    </rowset>
    -- nullhandling 1
    DECLARE
        result CLOB;
        qryctx DBE_XMLGEN.CTXHANDLE;
    BEGIN
    	qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=15');
    	DBE_XMLGEN.SETNULLHANDLING(qryctx, 1);
    	result:=DBE_XMLGEN.GETXML(qryctx);
    	DBE_OUTPUT.PUT_LINE(result);
    	DBE_XMLGEN.CLOSECONTEXT(qryctx);
    END;
    /
    <?xml version="1.0"?>
    <rowset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <row>
      <department_id>15</department_id>
      <department_name xsi:nil="true"/>
      <manager>500</manager>
      <location>1600</location>
    </row>
    </rowset>
    -- nullhandling 2
    DECLARE
        result CLOB;
        qryctx DBE_XMLGEN.CTXHANDLE;
    BEGIN
    	qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=15');
    	DBE_XMLGEN.SETNULLHANDLING(qryctx, 2);
    	result:=DBE_XMLGEN.GETXML(qryctx);
    	DBE_OUTPUT.PUT_LINE(result);
    	DBE_XMLGEN.CLOSECONTEXT(qryctx);
    END;
    /
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>15</department_id>
      <department_name/>
      <manager>500</manager>
      <location>1600</location>
    </row>
    </rowset>
  • DBE_XMLGEN.SETROWSETTAG

    设置xml根节点名称。

    函数原型:

    DBE_XMLGEN.SETROWSETTAG(CTX IN DBE_XMLGEN.CTXHANDLE, ROWSETTAGNAME IN VARCHAR2);

    参数说明:

    表9 DBE_XMLGEN.SETROWSETTAG接口参数说明

    参数

    描述

    CTX

    context handle。

    ROWSETTAGNAME

    xml根节点名称。

    示例:

    -- 预置数据。
    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
    INSERT INTO department VALUES(10, 'administrator', 200, 1700);
    INSERT INTO department VALUES(11, 'aaa', 200, 1700);
    INSERT INTO department VALUES(12, 'bbb', 300, 1600);
    INSERT INTO department VALUES(13, 'ccc', 400, 1600);
    INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
    INSERT INTO department VALUES(15, NULL, 500,1600);
    INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
    -- 设置根节点名称为asd。
    DECLARE
        qryctx DBE_XMLGEN.CTXHANDLE;
        result CLOB;
    BEGIN
    	qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * FROM DEPARTMENT ORDER BY DEPARTMENT_ID');
    	DBE_XMLGEN.SETROWSETTAG(qryctx, 'asd');
    	DBE_XMLGEN.SETROWTAG(qryctx, 'qwe');
    	result:=DBE_XMLGEN.GETXML(qryctx);
    	DBE_XMLGEN.CLOSECONTEXT(qryctx);
    	DBE_OUTPUT.PUT_LINE(result);
    END;
    /
    <?xml version="1.0"?>
    <asd>
    <qwe>
      <department_id>10</department_id>
      <department_name>administrator</department_name>
      <manager>200</manager>
      <location>1700</location>
    </qwe>
    <qwe>
      <department_id>11</department_id>
      <department_name>aaa</department_name>
      <manager>200</manager>
      <location>1700</location>
    </qwe>
    <qwe>
      <department_id>12</department_id>
      <department_name>bbb</department_name>
      <manager>300</manager>
      <location>1600</location>
    </qwe>
    <qwe>
      <department_id>13</department_id>
      <department_name>ccc</department_name>
      <manager>400</manager>
      <location>1600</location>
    </qwe>
    <qwe>
      <department_id>14</department_id>
      <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
      <manager>400</manager>
      <location>1600</location>
    </qwe>
    <qwe>
      <department_id>15</department_id>
      <manager>500</manager>
      <location>1600</location>
    </qwe>
    <qwe>
      <department_id>16</department_id>
      <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
      <manager>400</manager>
      <location>1600</location>
    </qwe>
    </asd>
  • DBE_XMLGEN.SETROWTAG

    设置xml中每一行数据的tag名。

    函数原型:

    DBE_XMLGEN.SETROWTAG(CTX IN DBE_XMLGEN.CTXHANDLE, ROWTAGNAME IN VARCHAR2);

    参数说明:

    表10 DBE_XMLGEN.SETROWTAG接口参数说明

    参数

    描述

    CTX

    context handle。

    ROWTAGNAME

    每一行数据的tag名。

    示例:

    -- 预置数据。
    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
    INSERT INTO department VALUES(10, 'administrator', 200, 1700);
    INSERT INTO department VALUES(11, 'aaa', 200, 1700);
    INSERT INTO department VALUES(12, 'bbb', 300, 1600);
    INSERT INTO department VALUES(13, 'ccc', 400, 1600);
    INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
    INSERT INTO department VALUES(15, NULL, 500,1600);
    INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
    -- 设置每一行数据的tag名为qwe。
    DECLARE
        qryctx DBE_XMLGEN.CTXHANDLE;
        result CLOB;
    BEGIN
    	qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * FROM DEPARTMENT ORDER BY DEPARTMENT_ID');
    	DBE_XMLGEN.SETROWSETTAG(qryctx, 'asd');
    	DBE_XMLGEN.SETROWTAG(qryctx, 'qwe');
    	result:=DBE_XMLGEN.GETXML(qryctx);
    	DBE_XMLGEN.CLOSECONTEXT(qryctx);
    	DBE_OUTPUT.PUT_LINE(result);
    END;
    /
    <?xml version="1.0"?>
    <asd>
    <qwe>
      <department_id>10</department_id>
      <department_name>administrator</department_name>
      <manager>200</manager>
      <location>1700</location>
    </qwe>
    <qwe>
      <department_id>11</department_id>
      <department_name>aaa</department_name>
      <manager>200</manager>
      <location>1700</location>
    </qwe>
    <qwe>
      <department_id>12</department_id>
      <department_name>bbb</department_name>
      <manager>300</manager>
      <location>1600</location>
    </qwe>
    <qwe>
      <department_id>13</department_id>
      <department_name>ccc</department_name>
      <manager>400</manager>
      <location>1600</location>
    </qwe>
    <qwe>
      <department_id>14</department_id>
      <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
      <manager>400</manager>
      <location>1600</location>
    </qwe>
    <qwe>
      <department_id>15</department_id>
      <manager>500</manager>
      <location>1600</location>
    </qwe>
    <qwe>
      <department_id>16</department_id>
      <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
      <manager>400</manager>
      <location>1600</location>
    </qwe>
    </asd>
  • DBE_XMLGEN.USENULLATTRIBUTEINDICATOR

    对xml中的null值所在的元素添加xsi:nil="true"属性。

    函数原型:

    DBE_XMLGEN.USENULLATTRIBUTEINDICATOR(CTX IN DBE_XMLGEN.CTXHANDLE, ATTRIND IN BOOLEAN);

    参数说明:

    表11 DBE_XMLGEN.USENULLATTRIBUTEINDICATOR接口参数说明

    参数

    描述

    CTX

    context handle。

    ATTRIND

    无意义。

    示例:

    -- 预置数据。
    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
    INSERT INTO department VALUES(10, 'administrator', 200, 1700);
    INSERT INTO department VALUES(11, 'aaa', 200, 1700);
    INSERT INTO department VALUES(12, 'bbb', 300, 1600);
    INSERT INTO department VALUES(13, 'ccc', 400, 1600);
    INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
    INSERT INTO department VALUES(15, NULL, 500,1600);
    INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
    -- 对null值添加 xsi:nil="true"属性。
    DECLARE
        result CLOB;
        qryctx DBE_XMLGEN.CTXHANDLE;
    BEGIN
    	qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=15');
    	DBE_XMLGEN.USENULLATTRIBUTEINDICATOR(qryctx, true);
    	result:=DBE_XMLGEN.GETXML(qryctx);
    	DBE_OUTPUT.PUT_LINE(result);
    	DBE_XMLGEN.CLOSECONTEXT(qryctx);
    END;
    /
    <?xml version="1.0"?>
    <rowset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <row>
      <department_id>15</department_id>
      <department_name xsi:nil="true"/>
      <manager>500</manager>
      <location>1600</location>
    </row>
    </rowset>
  • DBE_XMLGEN.USEITEMTAGSFORCOLL

    对数组类型变量所在的元素中添加'_item'后缀。

    函数原型:

    DBE_XMLGEN.USEITEMTAGSFORCOLL(CTX IN DBE_XMLGEN.CTXHANDLE);

    参数说明:

    表12 DBE_XMLGEN.USEITEMTAGSFORCOLL接口参数说明

    参数

    描述

    CTX

    context handle。

    示例:

    -- 预置数据。
    CREATE TABLE test_for_array(id INT[]);
    INSERT INTO test_for_array VALUES(ARRAY[1,2,3]);
    SELECT DBE_XMLGEN.GETXML('SELECT * from test_for_array');
    -- 数组类型添加'_item'后缀。
    DECLARE
    qryctx DBE_XMLGEN.CTXHANDLE;
    result CLOB;
    BEGIN
    	qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * from test_for_array');
    	DBE_XMLGEN.useItemTagsForColl(qryctx);
    	result:=DBE_XMLGEN.GETXML(qryctx);
    	DBE_XMLGEN.CLOSECONTEXT(qryctx);
    	DBE_OUTPUT.PUT_LINE(result);
    END;
    /
    <?xml version="1.0"?>
    <rowset>
    <row>
      <id>
        <int4_ITEM>1</int4_ITEM>
        <int4_ITEM>2</int4_ITEM>
        <int4_ITEM>3</int4_ITEM>
      </id>
    </row>
    </rowset>
  • DBE_XMLGEN.GETNUMROWSPROCESSED

    查看上一次getxml或者getxmltype返回的数据行数。

    函数原型:

    DBE_XMLGEN.GETNUMROWSPROCESSED(CTX IN DBE_XMLGEN.CTXHANDLE);

    参数说明:

    表13 DBE_XMLGEN.GETNUMROWSPROCESSED接口参数说明

    参数

    描述

    CTX

    context handle。

    示例:

    -- 预置数据。
    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
    INSERT INTO department VALUES(10, 'administrator', 200, 1700);
    INSERT INTO department VALUES(11, 'aaa', 200, 1700);
    INSERT INTO department VALUES(12, 'bbb', 300, 1600);
    INSERT INTO department VALUES(13, 'ccc', 400, 1600);
    INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
    INSERT INTO department VALUES(15, NULL, 500,1600);
    INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
    -- getNumRowsProcessed
    DECLARE
        qryctx DBE_XMLGEN.CTXHANDLE;
    	result CLOB;
    BEGIN
    	qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department');
    	-- 每一次查询返回的最大值。
    	DBE_XMLGEN.SETMAXROWS(qryctx, 4);
    	LOOP
            result := DBE_XMLGEN.GETXML(qryctx);
    		-- 这一轮查询返回的数量。
    		exit when DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx) = 0;
    		DBE_OUTPUT.PUT_LINE('-------'||DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx)||'----------');
    		DBE_OUTPUT.PUT_LINE(result);
    		DBE_OUTPUT.PUT_LINE('********************');
        END LOOP;
    	DBE_XMLGEN.CLOSECONTEXT(qryctx);
    END;
    /
    -------4----------
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>10</department_id>
      <department_name>administrator</department_name>
      <manager>200</manager>
      <location>1700</location>
    </row>
    <row>
      <department_id>11</department_id>
      <department_name>aaa</department_name>
      <manager>200</manager>
      <location>1700</location>
    </row>
    <row>
      <department_id>12</department_id>
      <department_name>bbb</department_name>
      <manager>300</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>13</department_id>
      <department_name>ccc</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>
    
    ********************
    -------3----------
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>14</department_id>
      <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>15</department_id>
      <manager>500</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>16</department_id>
      <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>
    
    ********************
  • DBE_XMLGEN.SETMAXROWS

    设置getxml最大的返回行数。

    函数原型:

    DBE_XMLGEN.SETMAXROWS(CTX IN DBE_XMLGEN.CTXHANDLE, MAXROWS IN NUMBER);

    参数说明:

    表14 DBE_XMLGEN.SETMAXROWS接口参数说明

    参数

    描述

    CTX

    context handle。

    MAXROWS

    每一次getxml最大的返回行数。

    示例:

    -- 预置数据。
    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
    INSERT INTO department VALUES(10, 'administrator', 200, 1700);
    INSERT INTO department VALUES(11, 'aaa', 200, 1700);
    INSERT INTO department VALUES(12, 'bbb', 300, 1600);
    INSERT INTO department VALUES(13, 'ccc', 400, 1600);
    INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
    INSERT INTO department VALUES(15, NULL, 500,1600);
    INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
    -- getNumRowsProcessed
    DECLARE
        qryctx DBE_XMLGEN.CTXHANDLE;
     result CLOB;
    BEGIN
     qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department');
     -- 每一次查询返回的最大值。
     DBE_XMLGEN.SETMAXROWS(qryctx, 4);
     LOOP
            result := DBE_XMLGEN.GETXML(qryctx);
     -- 这一轮查询返回的数量。
      exit when DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx) = 0;
      DBE_OUTPUT.PUT_LINE('-------'||DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx)||'----------');
      DBE_OUTPUT.PUT_LINE(result);
      DBE_OUTPUT.PUT_LINE('********************');
        END LOOP;
     DBE_XMLGEN.CLOSECONTEXT(qryctx);
    END;
    /
    -------4----------
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>10</department_id>
      <department_name>administrator</department_name>
      <manager>200</manager>
      <location>1700</location>
    </row>
    <row>
      <department_id>11</department_id>
      <department_name>aaa</department_name>
      <manager>200</manager>
      <location>1700</location>
    </row>
    <row>
      <department_id>12</department_id>
      <department_name>bbb</department_name>
      <manager>300</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>13</department_id>
      <department_name>ccc</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>
    
    ********************
    -------3----------
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>14</department_id>
      <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>15</department_id>
      <manager>500</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>16</department_id>
      <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>
    
    ********************
  • DBE_XMLGEN.SETSKIPROWS

    设置跳过sql行数。

    函数原型:

    DBE_XMLGEN.SETSKIPROWS(CTX IN DBE_XMLGEN.CTXHANDLE, SKIPROWS IN NUMBER);

    参数说明:

    表15 DBE_XMLGEN.SETSKIPROWS接口参数说明

    参数

    描述

    CTX

    context handle。

    SKIPROWS

    跳过SQL的头部行数。

    示例:

    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
    INSERT INTO department VALUES(10, 'administrator', 200, 1700);
    INSERT INTO department VALUES(11, 'aaa', 200, 1700);
    INSERT INTO department VALUES(12, 'bbb', 300, 1600);
    INSERT INTO department VALUES(13, 'ccc', 400, 1600);
    INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
    INSERT INTO department VALUES(15, NULL, 500,1600);
    INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
    -- setskiprows跳过5行。
    declare
        result CLOB;
        qryctx DBE_XMLGEN.CTXHANDLE;
    BEGIN
     qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department');
     DBE_XMLGEN.SETSKIPROWS(qryctx, 5);
     result:=DBE_XMLGEN.GETXML(qryctx);
     DBE_OUTPUT.PUT_LINE(result);
     DBE_XMLGEN.CLOSECONTEXT(qryctx);
    END;
    /
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>15</department_id>
      <manager>500</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>16</department_id>
      <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
  • DBE_XMLGEN.RESTARTQUERY

    重启sql。

    函数原型:

    DBE_XMLGEN.RESTARTQUERY(CTX IN DBE_XMLGEN.CTXHANDLE);

    参数说明:

    表16 DBE_XMLGEN.RESTARTQUERY接口参数说明

    参数

    描述

    CTX

    context handle。

    示例:

    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
    INSERT INTO department VALUES(10, 'administrator', 200, 1700);
    INSERT INTO department VALUES(11, 'aaa', 200, 1700);
    INSERT INTO department VALUES(12, 'bbb', 300, 1600);
    INSERT INTO department VALUES(13, 'ccc', 400, 1600);
    INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
    INSERT INTO department VALUES(15, NULL, 500,1600);
    INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
    
    DECLARE
        qryctx DBE_XMLGEN.CTXHANDLE;
     result CLOB;
    BEGIN
     qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department');
     -- 每一次查询返回的最大值。
     DBE_XMLGEN.SETMAXROWS(qryctx, 4);
     LOOP
            result := DBE_XMLGEN.GETXML(qryctx);
            -- 这一轮查询返回的数量。
            exit when DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx) = 0;
            DBE_OUTPUT.PUT_LINE('-------'||DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx)||'----------');
            DBE_OUTPUT.PUT_LINE(result);
            DBE_OUTPUT.PUT_LINE('********************');
        END LOOP;
        -- 重启查询。
        DBE_XMLGEN.RESTARTQUERY(qryctx);
        result := DBE_XMLGEN.GETXML(qryctx);
        DBE_OUTPUT.PUT_LINE('-------'||DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx)||'----------');
        DBE_OUTPUT.PUT_LINE(result);
        DBE_OUTPUT.PUT_LINE('********************');
        DBE_XMLGEN.CLOSECONTEXT(qryctx);
    END;
    /
    -------4----------
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>10</department_id>
      <department_name>administrator</department_name>
      <manager>200</manager>
      <location>1700</location>
    </row>
    <row>
      <department_id>11</department_id>
      <department_name>aaa</department_name>
      <manager>200</manager>
      <location>1700</location>
    </row>
    <row>
      <department_id>12</department_id>
      <department_name>bbb</department_name>
      <manager>300</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>13</department_id>
      <department_name>ccc</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>
    
    ********************
    -------3----------
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>14</department_id>
      <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>15</department_id>
      <manager>500</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>16</department_id>
      <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>
    
    ********************
    -------4----------
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>10</department_id>
      <department_name>administrator</department_name>
      <manager>200</manager>
      <location>1700</location>
    </row>
    <row>
      <department_id>11</department_id>
      <department_name>aaa</department_name>
      <manager>200</manager>
      <location>1700</location>
    </row>
    <row>
      <department_id>12</department_id>
      <department_name>bbb</department_name>
      <manager>300</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>13</department_id>
      <department_name>ccc</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>
    
    ********************
  • DBE_XMLGEN.GETXMLTYPE

    返回XMLTYPE类型的xml文本。

    函数原型:

    DBE_XMLGEN.GETXMLTYPE(SQLQUERY IN VARCHAR2, DTDORSCHEMA IN NUMBER := 0) RETURNS XMLTYPE;
    DBE_XMLGEN.GETXMLTYPE(CTX IN DBE_XMLGEN.CTXHANDLE, DTDORSCHEMA IN NUMBER := 0) RETURNS XMLTYPE;

    参数说明:

    表17 DBE_XMLGEN.GETXMLTYP接口参数说明

    参数

    描述

    SQLQUERY

    需要转换成XML的查询SQL。

    DTDORSCHEMA

    无意义

    CTX

    context handle。

    示例:

    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
    INSERT INTO department VALUES(10, 'administrator', 200, 1700);
    INSERT INTO department VALUES(11, 'aaa', 200, 1700);
    INSERT INTO department VALUES(12, 'bbb', 300, 1600);
    INSERT INTO department VALUES(13, 'ccc', 400, 1600);
    INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
    INSERT INTO department VALUES(15, NULL, 500,1600);
    INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
    DECLARE
        lr SYS_REFCURSOR;
        qryctx DBE_XMLGEN.CTXHANDLE;
        result XMLTYPE;
    BEGIN
        OPEN lr FOR SELECT department_id, department_name FROM DEPARTMENT ORDER BY DEPARTMENT_ID;
        qryctx:=DBE_XMLGEN.NEWCONTEXT(lr);
     result:=DBE_XMLGEN.GETXMLTYPE(qryctx);
     DBE_XMLGEN.CLOSECONTEXT(qryctx);
            -- 返回的是xmltype类型所以需要用getclobval转换才能被put_line输出。
     DBE_OUTPUT.PUT_LINE(result.getclobval);
    END;
    /
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>10</department_id>
      <department_name>administrator</department_name>
    </row>
    <row>
      <department_id>11</department_id>
      <department_name>aaa</department_name>
    </row>
    <row>
      <department_id>12</department_id>
      <department_name>bbb</department_name>
    </row>
    <row>
      <department_id>13</department_id>
      <department_name>ccc</department_name>
    </row>
    <row>
      <department_id>14</department_id>
      <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
    </row>
    <row>
      <department_id>15</department_id>
    </row>
    <row>
      <department_id>16</department_id>
      <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
    </row>
    </rowset>
  • DBE_XMLGEN.GETXML

    返回CLOB类型的xml文本。

    函数原型:

    DBE_XMLGEN.GETXML(SQLQUERY IN VARCHAR2, DTDORSCHEMA IN NUMBER := 0) RETURNS CLOB;
    DBE_XMLGEN.GETXML(CTX IN DBE_XMLGEN.CTXHANDLE, DTDORSCHEMA IN NUMBER := 0) RETURNS CLOB;
    DBE_XMLGEN.GETXML(CTX IN DBE_XMLGEN.CTXHANDLE, TMPCLOB INOUT CLOB, DTDORSCHEMA IN NUMBER := 0);

    参数说明:

    表18 DBE_XMLGEN.GETXML接口参数说明

    参数

    描述

    SQLQUERY

    需要转换成XML的查询SQL。

    DTDORSCHEMA

    无意义

    CTX

    context handle。

    TMPCLOB

    用于保存输出的XML的CLOB变量。

    示例:

    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
    INSERT INTO department VALUES(10, 'administrator', 200, 1700);
    INSERT INTO department VALUES(11, 'aaa', 200, 1700);
    INSERT INTO department VALUES(12, 'bbb', 300, 1600);
    INSERT INTO department VALUES(13, 'ccc', 400, 1600);
    INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
    INSERT INTO department VALUES(15, NULL, 500,1600);
    INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
    DECLARE
        qryctx DBE_XMLGEN.CTXHANDLE;
     result CLOB;
    BEGIN
     qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department');
     -- 每一次查询返回的最大值。
     DBE_XMLGEN.SETMAXROWS(qryctx, 4);
     LOOP
            result := DBE_XMLGEN.GETXML(qryctx);
            -- 这一轮查询返回的数量。
            exit when DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx) = 0;
            DBE_OUTPUT.PUT_LINE('-------'||DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx)||'----------');
            DBE_OUTPUT.PUT_LINE(result);
            DBE_OUTPUT.PUT_LINE('********************');
        END LOOP;
        DBE_XMLGEN.RESTARTQUERY(qryctx);
        result := DBE_XMLGEN.GETXML(qryctx);
        DBE_OUTPUT.PUT_LINE('-------'||DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx)||'----------');
        DBE_OUTPUT.PUT_LINE(result);
        DBE_OUTPUT.PUT_LINE('********************');
        DBE_XMLGEN.CLOSECONTEXT(qryctx);
    END;
    /
    -------4----------
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>10</department_id>
      <department_name>administrator</department_name>
      <manager>200</manager>
      <location>1700</location>
    </row>
    <row>
      <department_id>11</department_id>
      <department_name>aaa</department_name>
      <manager>200</manager>
      <location>1700</location>
    </row>
    <row>
      <department_id>12</department_id>
      <department_name>bbb</department_name>
      <manager>300</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>13</department_id>
      <department_name>ccc</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>
    
    ********************
    -------3----------
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>14</department_id>
      <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>15</department_id>
      <manager>500</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>16</department_id>
      <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>
    
    ********************
    -------4----------
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>10</department_id>
      <department_name>administrator</department_name>
      <manager>200</manager>
      <location>1700</location>
    </row>
    <row>
      <department_id>11</department_id>
      <department_name>aaa</department_name>
      <manager>200</manager>
      <location>1700</location>
    </row>
    <row>
      <department_id>12</department_id>
      <department_name>bbb</department_name>
      <manager>300</manager>
      <location>1600</location>
    </row>
    <row>
      <department_id>13</department_id>
      <department_name>ccc</department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>
    
    ********************
  • DBE_XMLGEN.CLOSECONTEXT

    关闭context handle。

    函数原型:

    DBE_XMLGEN.CLOSECONTEXT(CTX IN DBE_XMLGEN.CTXHANDLE);

    参数说明:

    表19 DBE_XMLGEN.CLOSECONTEXT接口参数说明

    参数

    描述

    CTX

    context handle。

    示例:

    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
    INSERT INTO department VALUES(10, 'administrator', 200, 1700);
    INSERT INTO department VALUES(11, 'aaa', 200, 1700);
    INSERT INTO department VALUES(12, 'bbb', 300, 1600);
    INSERT INTO department VALUES(13, 'ccc', 400, 1600);
    INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
    INSERT INTO department VALUES(15, NULL, 500,1600);
    INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
    -- 关闭context。
    DECLARE
        qryctx DBE_XMLGEN.CTXHANDLE;
        result CLOB;
    BEGIN
     qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=14');
     DBE_XMLGEN.SETCONVERTSPECIALCHARS(qryctx, false);
     result:=DBE_XMLGEN.GETXML(qryctx);
     DBE_XMLGEN.CLOSECONTEXT(qryctx);
     DBE_OUTPUT.PUT_LINE(result);
    END;
    /
    <?xml version="1.0"?>
    <rowset>
    <row>
      <department_id>14</department_id>
      <department_name>aaa</row><a>asd</a><row></department_name>
      <manager>400</manager>
      <location>1600</location>
    </row>
    </rowset>