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

Question

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!

Answer 1

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.)