četrtek, 11. februar 2010

Oracle BUG when using xmltype() function

I found a bug on Oracle DB when using types. The problem occours when you try to make an XML data using xmltype function from types that have more than 3 subtypes.
Here is an example of the test code:

CREATE OR REPLACE TYPE D_T AS OBJECT(F varchar2(1),G varchar2(2))
/
CREATE OR REPLACE TYPE C_T AS OBJECT (D1 D_T, D2 D_T)
/
CREATE OR REPLACE TYPE B_T AS OBJECT(C C_T)
/
CREATE OR REPLACE TYPE A_T AS OBJECT(B B_T)
/
declare
 v_a a_t;
begin                       
   v_a := a_t(
              b_t(
                  c_t(
                      null,
                      d_t('1','2')
                      )
                  )
              );
   dbms_output.put_line(xmltype(v_a).extract('*').getClobVal);
   v_a := a_t(
              b_t(
                  c_t(
                      d_t(null,null),
                      d_t('1','2')
                      )
                  )
              );
   dbms_output.put_line(xmltype(v_a).extract('*').getClobVal);
end;
/
The first dbms_output prints out:
<A_T>
  <B/>
</A_T>
The second one prints out:
<A_T>
  <B>
    <C>
      <D1/>
      <D2>
        <F>1</F>
        <G>2</G>
      </D2>
    </C>
  </B>
</A_T>
The output on both should be the almost the same with one difference. We can see that in the first output, the data from c_t type gets lost. The data is stored in the v_a variable but xmltype doesn't show it. The correct output from the first dbms_output should be (with C element that also contains D2 and is without D1 element):
<A_T>
  <B>
    <C>
      <D2>
        <F>1</F>
        <G>2</G>
      </D2>
    </C>
  </B>
</A_T>
The problem was tested both on 10g and 11g version of DB.

Ni komentarjev:

Objavite komentar