Is use of DBMS_LOB necessary when returning CLOB from PL/SQL procedure?

I would like to create some PL/SQL procedures that return XML as CLOB parameters. I want to just do this (which works fine with simple tests):

create or replace procedure p_xml_test_1(
  p_xml out nocopy clob
) is
begin
  p_xml := '<?xml version="1.0" encoding="utf8" ?>' ||
    '<test><something>some value</something></test>';
end p_xml_test_1;

But I have access to some other source code that basically does this:

create or replace procedure p_xml_test_2(
  p_xml out nocopy clob
) is
  lv_xml clob;
begin
  dbms_lob.createtemporary(
    lob_loc => p_xml,
    cache   => true
  );

  lv_xml := '<?xml version="1.0" encoding="utf8" ?>' ||
    '<test><something>some value</something></test>';

  dbms_lob.writeappend(
    lob_loc => p_xml,
    amount  => length(lv_xml),
    buffer  => lv_xml
  );
end p_xml_test_2;

I'm wondering if the first method will cause any problems for me down the road. Is it ok to do it that way? What is the advantage, if any开发者_开发技巧, to the second method? Thanks!

I ran the following procedures to compare execution time:

Version 1

create or replace procedure p_xml_test_1(
  p_xml out nocopy clob
) is
  lv_i number;
begin
  for lv_i in 1 .. 999999 loop
    p_xml := p_xml || 'a';
  end loop;
end p_xml_test_1;

Version 2

create or replace procedure p_xml_test_2(
  p_xml out nocopy clob
) is
  lv_xml clob;
  lv_i   number;
begin
  dbms_lob.createtemporary(
    lob_loc => p_xml,
    cache   => true
  );

  for lv_i in 1 .. 999999 loop
    lv_xml := 'a';

    dbms_lob.writeappend(
      lob_loc => p_xml,
      amount  => length(lv_xml),
      buffer  => lv_xml
    );
  end loop;
end p_xml_test_2;

The difference is neglible. Both come in at around .2 seconds consistently.

If I change the procedures to loop to 999999 instead of 10000, version 1's performance starts to decline somewhat (about 39 seconds versus 32 seconds for version2).

I think you should measure the performance of both methods by running them many times in a loop. I think performance is the only difference. Your xml block is short but when you concatenate a big xml block it is faster to concatenate with dbms_low.writeappend than using ||.

(At least it was in Oracle 9, I believe the performance difference is smaller in Oracle 10.)

I see no reason why you'd want to use the second one.

If lv_xml was a VARCHAR2 rather than a CLOB then I see a very good reason (the max length of a string literal vs the max length of a CLOB).

Thanks for the answers. Based on what I read at the site listed below, I'm going to assume that it is in fact a good thing to use dbms_lob.writeappend. If I don't, I might as well be using VARCHAR2s (which won't be large enough in some cases).

If you have a CLOB variable called "l_clob", and you do something like "l_clob := l_clob || l_some_string_to_concatenate;", it will convert the l_clob value on the right side of the equation to a VARCHAR2 before doing the concatenation, possibly giving you invalid results or an error.

http://www.maristream.org/srea/Huge_Strings_Using_LOBs.htm

There is another option which you don't mention: using Oracle's built-in XML functionality (presuming you have a database version of 9i or higher). For instance, generating an XML document from a query is a snip with DBMS_XMLGEN.getXML() or DBMS_XMLGEN.getXMLType().