Clever Geek Handbook

Is DBMS_LOB used when returning CLOB from PL / SQL procedure?

I would like to create some PL / SQL procedures that return XML as CLOB parameters. I just want to do this (which works great 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 am wondering if the first method might be causing me problems along the way. Is it so? What is the advantage, if any, of the second method? Thank!

+2


source to share


5 answers


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 combine a large xml block, it is faster to combine with dbms_low.writeappend than using ||.



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

0


source


To compare the execution time, I followed the following procedures:

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 not safe. Both come in sequentially for about 2 seconds.

If I change the procedures to loop to 999999 instead of 10000, the performance of version 1 will decrease slightly (about 39 seconds versus 32 seconds for version 2).

+1


source


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

If lv_xml was VARCHAR2 and not CLOB, then I see a very good reason (maximum string literal length and maximum CLOB length).

0


source


Thanks for answers. Based on what I've read on the site linked below, I'm going to assume that it is actually good to use dbms_lob.writeappend. If I don't, I could use VARCHAR2 (which won't be big enough in some cases).

If you have a CLOB variable named "l_clob" and you do something like "l_clob: = l_clob || l_some_string_to_concatenate;" it will convert the value of l_clob on the right side of the equation to VARCHAR2 before doing the concatenation, you might get incorrect results or a mistake.

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

0


source


There is another option that you don't mention: using the native XML functionality in Oracle (assuming you have a 9i or higher database version). For example, generating an XML document from a request is a snip with DBMS_XMLGEN.getXML()

or DBMS_XMLGEN.getXMLType()

.

0


source






More articles:


All Articles