Hello,
I'm using 11gR2.
Here is an example of what I'm doing.
I have data stored in XML and I extract values into a PL/SQL user type structure (here Order_Typ ).
My_Xml can have 500 items.
I call Extract_Order_Typ function 50.000 times among my process.
Under enterprise manager, I see that EXTRACTVALUE is taking 25% of the CPU used athough It is not the main thing that I'm doing.
What should I do to optimize Extract_Order_Typ function ?
Thanks
CREATE OR REPLACE package Xml_2_Typ
is
My_Xml xmltype:=
xmltype('<root>
<gross_value>325</gross_value>
<net_value>370</net_value>
<tax_value>45</tax_value>
<item_list>
<item>
<description>books</description>
<price>10</price>
<tax_percent>0.1</tax_percent>
<quantity>20</quantity>
<gross_value>200</gross_value>
<net_value>220</net_value>
<tax_value>20</tax_value>
</item>
<item>
<description>pencils</description>
<price>1.25</price>
<tax_percent>0.2</tax_percent>
<quantity>100</quantity>
<gross_value>125</gross_value>
<net_value>150</net_value>
<tax_value>25</tax_value>
</item>
</item_list>
</root>');
type Item_Typ is record
(description varchar2(100),
price number(10,2),
tax_percent number(8,7),
quantity number(10),
gross_value number(10,2),
net_value number(10,2),
tax_value number(10,2));
Type Item_List_Typ is table of Item_Typ;
Type Order_Typ is record
(gross_value number(10,2),
net_value number(10,2),
tax_value number(10,2),
item_list Item_List_Typ);
function Extract_Order_Typ(I_XML IN XMLTYPE) return Order_Typ;
procedure Test_It;
end;
/
CREATE OR REPLACE package body Xml_2_Typ
is
function Extract_Item_Typ(I_XML IN XMLTYPE)
return Item_Typ
is
v_rst Item_Typ;
v_xml xmltype;
begin
select extractvalue(I_XML,'/*/description'),
extractvalue(I_XML,'/*/prices'),
extractvalue(I_XML,'/*/tax_percent'),
extractvalue(I_XML,'/*/quantity'),
extractvalue(I_XML,'/*/gross_value'),
extractvalue(I_XML,'/*/net_value'),
extractvalue(I_XML,'/*/tax_value')
into v_rst.description,
v_rst.price,
v_rst.tax_percent,
v_rst.quantity,
v_rst.gross_value,
v_rst.net_value,
v_rst.tax_value
from dual;
return v_rst;
end;
function Extract_Item_List_Typ(I_XML IN XMLTYPE)
return Item_List_Typ
is
v_rst Item_List_Typ := Item_List_Typ();
type xml_tab_typ is table of xmltype;
v_data xml_tab_typ := xml_tab_typ();
begin
select value(a) as val
bulk collect
into v_data
from table (xmlsequence(extract(I_XML,'/*/item'))) a;
if v_data is not null and v_data.count>0 then
for i in v_data.first..v_data.last loop
v_rst.extend;
v_rst(v_rst.last) := Extract_Item_Typ(v_data(i));
end loop;
end if;
return v_rst;
end;
function Extract_Order_Typ(I_XML IN XMLTYPE)
return Order_Typ
is
v_rst Order_Typ;
v_xml xmltype;
begin
select extractvalue(I_XML,'/*/gross_value'),
extractvalue(I_XML,'/*/net_value'),
extractvalue(I_XML,'/*/tax_value')
into v_rst.gross_value,
v_rst.net_value,
v_rst.tax_value
from dual;
select extract(I_XML,'/*/item_list') into v_xml from dual;
v_rst.item_list := Extract_Item_List_Typ(v_xml);
return v_rst;
end;
procedure Test_It
is
v_rst Order_Typ;
begin
v_rst := Extract_Order_Typ(My_Xml);
end;
end;
/