生成XML PLSQL迭代 [英] Generating XML PLSQL Iterative

问题描述

我正在尝试通过Oracle 11g中的PL/SQL构建一些非常大型XML文件.我正在尝试以迭代方式生成文件-获取一行，写入文件，获取下一行，等等.以下是我的代码.我在哪里定义CLOB时遇到问题.

I am trying to build some very large XML files via PL/SQL in Oracle 11g. I am trying to build the files iteratively - fetch one row, write to file, get next row, etc. Below is my code. I am having issues on where to define the CLOB.

根据初始化和释放CLOB的位置，我会遇到两个错误: 1-超出内存-在循环前初始化CLOB和在循环后初始化freetemporary后 2-找不到Clob(指定了无效的LOB定位符)-进行循环初始化和自由循环，或者初始化并释放循环

I get two errors depending on where I initialize and free the CLOB: 1-Exceeding memory -- when initializing CLOB before loop and freetemporary after loop 2- Can't find the clob(invalid LOB locator specified) -- when initialize in loop and free in loop, or initialize out and free in loop

请告知我的问题是什么，或者最好的方法是迭代构建大型XML文件.

Please advise what the issue is in my approach or what the best approach is to build a large XML file iteratively.

PROCEDURE sql_to_xml(p_sql IN VARCHAR2,
                    p_fileName       IN VARCHAR2,
                    p_dir            IN VARCHAR2,
                    p_xml_created OUT VARCHAR2) IS

xml_result CLOB;
doc        dbms_xmldom.DOMDocument;
ctx DBMS_XMLGEN.ctxHandle;
vv_exit_code varchar2(5);
vv_ctx_open varchar2(1) := 'N';
max_rows NUMBER := 5;

BEGIN

vv_exit_code := 'XML1';
ctx := dbms_xmlgen.newcontext(p_sql);
vv_ctx_open := 'Y';
DBMS_OUTPUT.put_line(vv_exit_code);

vv_exit_code := 'XML2';
DBMS_XMLGEN.SETCONVERTSPECIALCHARS (ctx,TRUE);
DBMS_OUTPUT.put_line(vv_exit_code);

DBMS_LOB.CREATETEMPORARY(xml_result,true); 
while DBMS_XMLGEN.GETNUMROWSPROCESSED(ctx) < max_rows
LOOP
    vv_exit_code := 'XML3';
    xml_result := dbms_xmlgen.getXML(ctx);
    DBMS_OUTPUT.put_line(vv_exit_code);
    DBMS_output.put_line('Xml result is: ' ||dbms_lob.substr( xml_result, 4000, 1 ));

    IF xml_result is not null THEN
        vv_exit_code := 'XML4';    
        doc := dbms_xmldom.newDOMDocument(xml_result);
        DBMS_OUTPUT.put_line(vv_exit_code);

        vv_exit_code := 'XML5';
        dbms_xmldom.writeToFile(doc,p_dir||'/'||p_fileName, 'ISO-8859-1');
        DBMS_OUTPUT.put_line(vv_exit_code);

        vv_exit_code := 'XML6';
        dbms_xmldom.freeDocument(doc);
        p_xml_created := 'TRUE';
        DBMS_OUTPUT.put_line(vv_exit_code);

    ELSE
        p_xml_created := 'FALSE';
    END IF;

    DBMS_OUTPUT.PUT_LINE('XML Result: '||xml_result);
        dbms_lob.FREETEMPORARY(xml_result);

end loop;


DBMS_XMLGEN.CLOSECONTEXT (ctx);
vv_ctx_open := 'N';

EXCEPTION
WHEN out_of_process_memory THEN
    IF vv_ctx_open = 'Y' THEN
        DBMS_XMLGEN.CLOSECONTEXT (ctx);
    END IF;

    gv_err_msg := substr(sqlerrm,1,2000);
    DBMS_OUTPUT.put_line(gv_process_name||' failed '||gv_err_msg);
    RAISE_APPLICATION_ERROR(-20906,gv_process_name||' failed'||gv_err_msg);
    dbms_output.put_line('XML_EXPORT failed (out_of_process_memory exception) executing '||p_sql);
    raise_application_error(-20906,'XML_EXPORT (out_of_process_memory exception) failed executing '||p_sql);


WHEN OTHERS THEN
    IF vv_ctx_open = 'Y' THEN
        DBMS_XMLGEN.CLOSECONTEXT (ctx);
    END IF;
    if xml_result is NULL then
        gv_err_msg := substr(sqlerrm,1,2000);
        DBMS_OUTPUT.put_line(gv_process_name||' failed '||gv_err_msg);
        --   RAISE_APPLICATION_ERROR(-20906,gv_process_name||' failed'||gv_err_msg);
        dbms_output.put_line('XML_EXPORT failed (xml results are NULL) executing '||p_sql);
        raise_application_error(-20906,'XML_EXPORT (xml results are NULL) failed executing '||p_sql);
    else
        gv_err_msg := substr(sqlerrm,1,2000);
        DBMS_OUTPUT.put_line(gv_process_name||' failed '||gv_err_msg);
          dbms_output.put_line('XML_EXPORT failed (others exception) executing '||p_sql);
        DBMS_OUTPUT.put_line('Export Directory is: '||p_dir||'/'||p_fileName);
        raise_application_error(-20906,'XML_EXPORT (others exception) failed executing '||p_sql);
    end if;
END sql_to_xml;

推荐答案

尝试在PL/SQL中生成非常大的XML毫无意义.问题不在于PL/SQL本身，而是PL/SQL仅支持XML DOM，而DOM根本无法很好地处理大型XML.您没有说XML文档的大小，但是发现PL/SQL用于构建文档的内存大约是生成的文档大小的10到30倍，我不会感到惊讶.

There's no point attempting to generate very large XML in PL/SQL. The problem isn't PL/SQL as such, but that PL/SQL only supports XML DOM, and DOM does not handle large XML at all well. You don't say what size of XML document you have, but I would not be surprised to find the memory being used by PL/SQL to build your document being about 10 to 30 times the size of the resulting document.

是否可以使用PL/SQL以外的其他方式生成XML?如果不是这样，并且确实必须在Oracle数据库中生成大型XML文件，我会考虑使用Java存储过程. 这个问题对如何在Java中执行这种操作有一些答案.

Is there an option to generate the XML using something other than PL/SQL? If not, and I really had to generate large XML files in an Oracle database, I would consider using Java stored procedures. This question has some answers on how to do this kind of thing in Java.

编辑以回应您的评论:您的代码绝对不会一次写一行.它是一起写的，这是我通过在Oracle 11g XE数据库上使用查询SELECT * FROM all_objects运行它来验证的事实.该循环运行了一次，写了7341个对象，创建了一个刚好超过3MB的XML文件.

EDIT in response to your comment: your code is most definitely not writing one line at a time. It is writing the lot together, a fact I verified by running it using the query SELECT * FROM all_objects on my Oracle 11g XE database. The loop ran once and wrote 7341 objects, creating an XML file just over 3MB in size.

然后，我尝试修改您的代码以更好地支持您描述的增量"方法.这涉及到:

I then tried modifying your code to better support the 'incremental' approach you describe. This involved:

• 添加一行dbms_xmlgen.setmaxrows(ctx, max_rows);来告诉DBMS_XMLGEN一次仅生成5行.否则，它会尝试一次性生成批次.

• adding a line dbms_xmlgen.setmaxrows(ctx, max_rows); to tell DBMS_XMLGEN to only generate 5 rows at a time. Otherwise it attempts to generate the lot in one go.

将WHILE循环顶部的代码修改为

modifying the code at the top of the WHILE loop to

xml_result := dbms_xmlgen.getXML(ctx);
num_rows_processed := DBMS_XMLGEN.GETNUMROWSPROCESSED(ctx);
dbms_output.put_line('Got ' || num_rows_processed || ' rows processed');

while num_rows_processed > 0
  -- rest of loop omitted

将这三行中的第一行添加到WHILE循环的底部之前.

adding the first of these three lines just before the bottom of the WHILE loop.

然后我重新运行您的代码，我可以看到它每次将每行五行写入文件.但是，这种方法存在一个小问题，因为每次都覆盖该文件.最后，我在输出XML文件中只有一条记录.我无法想象这就是您想要的.

I then re-ran your code, and I could see it writing each batch of five rows to the file each time. However, there is a slight issue with this approach, in that the file was overwritten each time. At the end I had only a single record in the output XML file. I can't imagine this would be what you want.

WRITETOCLOB，WRITETOBUFFER和WRITETOFILE方法"nofollow noreferrer> DBMS_XMLDOM 并不暗示可以附加到现有文件，而且老实说，我并不对此感到惊讶.如果可以的话，最终将导致无效的XML，因为文件中将有多个<?xml ... ?>声明.

The WRITETOCLOB, WRITETOBUFFER and WRITETOFILE methods in DBMS_XMLDOM don't hint at the ability to append to an existing file, and to be honest I'm not surprised that they don't. If you could, you would end up with invalid XML, as there would be more than one <?xml ... ?> declaration in the file.

我坚持以前的建议.每当需要在Oracle数据库或其他地方处理大型XML时，请使用SAX或StAX. PL/SQL都不支持，所以您需要在Java存储过程中做任何事情，或者在数据库外进行.

I stand by my previous advice. Whenever you need to deal with large XML, in an Oracle database or elsewhere, use SAX or StAX. PL/SQL doesn't support either, so do whatever you need to do in Java stored procedures or do it out of the database.

这篇关于生成XML PLSQL迭代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！