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