It doesn't appear that toObject will convert null tags. 


1  declare
  2         myxml xmltype;
  3         my_sys sysParm;
  4      begin
  5         myxml := xmltype('<sysParm id="10">
  6                           <name>version_control</name>
  7                           <isSessModfiable>N</isSessModfiable>
  8                           <isSysModifiable>N</isSysModifiable>
  9                           <value></value>  10                          </sysParm>');
 11        myxml.toObject(my_sys );
 12        open :x for select my_sys from dual;
 13*    end;
 14  /
declare
*
ERROR at line 1:
ORA-22814: attribute or element value is larger than specified in type
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 11


SQL> 
 

Hi Tom,

I need your help to populate a pl/sql table type varuable from an xmltype variable.

here's my sample script:

CREATE OR REPLACE TYPE MTP AS OBJECT
( ID NUMBER,
SYNOP CLOB);
/

CREATE OR REPLACE TYPE TAB_MTP AS TABLE OF MTP;
/

insert into clob_tab
select object_id,lpad('*',10,'*')
from user_objects
where rownum <= 10
/

SQL> ;
  1  DECLARE
  2     qryCtx DBMS_XMLGEN.ctxHandle;
  3     result CLOB;
  4     l_xml     xmltype;
  5     l_obj tab_mtp;
  6  BEGIN
  7    qryCtx := dbms_xmlgen.newContext('select id,synop from clob_tab');
  8      DBMS_XMLGEN.setRowTag(qryCtx, 'TABLE');
  9    -- now get the result
 10    result := DBMS_XMLGEN.getXML(qryCtx);
 11    --close context
 12    DBMS_XMLGEN.closeContext(qryCtx);
 13    l_xml := xmltype.createxml(result);
 14    l_xml.toobject(l_obj);
 15* END;
SQL> /
  l_xml.toobject(l_obj);
  *
ERROR at line 14:
ORA-06550: line 14, column 3:
PLS-00306: wrong number or types of arguments in call to 'TOOBJECT'
ORA-06550: line 14, column 3:
PL/SQL: Statement ignored

Is it a restriction that we cannot convert to a pl/sql table type. Maybe I am not doing it the right way.

Your help will be much appreciated.

Thanks in advance for your valuable time.

Regards
Ray

 

I think your xml statement is not well formed.

Your line 9 is wrong:

<value></value>10</sysParm>');


Remove 10 and it works.

Hi Tom,

would you mind to take a brief look at the minimized fully self-contained test case below?

Could you please explain how/why "toObject" returns an instance of "MySuperType" from the XML generated from an instance of "MySubType" (and how to avoid this if possible)?

drop user USR cascade ;

create user USR
identified by values 'NoEntry'
account lock ;

create or replace type USR.MySuperType as object(
myAtt varchar2( 30 )
) not instantiable not final
/

create or replace type USR.MySubType under USR.MySuperType(
)
/

declare
x USR.MySubType := USR.MySubType( 'ABC' ) ;
y xmltype ;

begin
y := xmltype( x ) ;
if x is of ( USR.MySubType ) then
DBMS_Output.Put_Line( 'MySubType' ) ;
end if ;

x := NULL ;
DBMS_Output.Put_Line( y.getCLOBVal() ) ;

xmltype.toObject( y, x ) ;
if x is of ( USR.MySuperType ) then
DBMS_Output.Put_Line( 'MySuperType' ) ;
end if ;
end ;
/

Thank you very much in advance.

ops$tkyte%ORA10GR2> declare
  2      x USR.MySubType := USR.MySubType( 'ABC' ) ;
  3      y xmltype ;
  4
  5  begin
  6      y := xmltype( x ) ;
  7      if x is of ( USR.MySuperType ) then
  8          DBMS_Output.Put_Line( 'MySuperType' ) ;
  9      end if ;
 10      if x is of ( USR.MySubType ) then
 11          DBMS_Output.Put_Line( 'MySubType' ) ;
 12      end if ;
 13
 14      x := NULL ;
 15      DBMS_Output.Put_Line( y.getCLOBVal() ) ;
 16
 17      xmltype.toObject( y, x ) ;
 18      if x is of ( USR.MySuperType ) then
 19          DBMS_Output.Put_Line( 'MySuperType' ) ;
 20      end if ;
 21      if x is of ( USR.MySubType ) then
 22          DBMS_Output.Put_Line( 'MySubType' ) ;
 23      end if ;
 24  end ;
 25  /
MySuperType
MySubType
<MYSUBTYPE><MYATT>ABC</MYATT></MYSUBTYPE>
MySuperType
MySubType

PL/SQL procedure successfully completed.

I have a requirement to read an XML document, convert to oracle object and then pass as a parameter to PL/SQL procedure. I am able to achieve this without any issue, when my input xml has a wrapper element for the repeating objects, but if my input xml doesn't have the wrapper element then not sure how do I achieve this.
I am not sure if this is achievable, because in XML schema you can represent a repeating element without a wrapper, but in database how do I create an object containing multiple objects without creating a table type..not sure..
If possible..can you please explain if I can achieve this(I am on oracle database 11g (11.1.0.7.0)).

Below is my sample object creation script.

CREATE OR REPLACE TYPE "item" AS OBJECT
(
"itemnumber" varchar2(240)
,"quantity" number
,"amount" number
);
/
create or replace type "itemsarray" is table of "item";
/
CREATE OR REPLACE TYPE "itemorder" AS OBJECT
(
"ordernumber" varchar2(240)
,"customername" varchar2(240)
,"items" "itemsarray"
);
/

Below is my sample xml without wrapper element for the repeating object (item).

variable x refcursor;

declare
v_order_xml xmltype;
v_order_obj "itemorder";
begin
v_order_xml := xmltype('<?xml version="1.0" encoding="UTF-8"?>
<order>
<ordernumber>123-01</ordernumber>
<customername>Idea Networks</customername>
<item>
<itemnumber>T-01</itemnumber>
<quantity>1</quantity>
<amount>10</amount>
</item>
<item>
<itemnumber>T-20</itemnumber>
<quantity>3</quantity>
<amount>8</amount>
</item>
</order>');
v_order_xml.toObject(v_order_obj);
open :x for select v_order_obj from dual;
end;
/

Thanks for your help,
Thiru