2 Replies Latest reply on Jun 12, 2012 3:34 PM by user503699

    ORA-19010

    user503699
      Hello All,

      Can somebody help me understand why I am getting below error?
      SQL> select * from v$version ;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE    11.2.0.1.0      Production
      TNS for Linux: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      
      SQL> desc resp
       Name                                                                                                        Null?    Type
       ----------------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------
       XCOL                                                                                                                 SYS.XMLTYPE STORAGE BINARY
      
      SQL> select count(*) from all_objects ;
      
        COUNT(*)
      ----------
           74512
      
      SQL> insert into resp select xmlquery('collection("oradb:/PUBLIC/ALL_OBJECTS")' returning content) from dual ;
      insert into resp select xmlquery('collection("oradb:/PUBLIC/ALL_OBJECTS")' returning content) from dual
      *
      ERROR at line 1:
      ORA-19010: Cannot insert XML fragments
      Also, another question is how do i get position/index of a node in an xml document? I tried using "position()" function but it does not appear to work.
      In the following example, I would like to get the index of the node ROW, preferably as attribute of <ROW>.
      SQL> with src as (select xmltype('<ROWSET><ROW><OBJECT_ID>10</OBJECT_ID></ROW><ROW><OBJECT_ID>20</OBJECT_ID></ROW></ROWSET>') as xdoc from dual) select xmlquery('for $i in $d/ROWSET/ROW return $i' passing src.xdoc as "d" returning content) from src ;
      
      XMLQUERY('FOR$IIN$D/ROWSET/ROWRETURN$I'PASSINGSRC.XDOCAS"D"RETURNINGCONTENT)
      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      <ROW><OBJECT_ID>10</OBJECT_ID></ROW><ROW><OBJECT_ID>20</OBJECT_ID></ROW>
      
      SQL> with src as (select xmltype('<ROWSET><ROW><OBJECT_ID>10</OBJECT_ID></ROW><ROW><OBJECT_ID>20</OBJECT_ID></ROW></ROWSET>') as xdoc from dual) select xmlquery('for $i in $d/ROWSET/ROW return $i[position()]' passing src.xdoc as "d" returning content) from src ;
      
      XMLQUERY('FOR$IIN$D/ROWSET/ROWRETURN$I[POSITION()]'PASSINGSRC.XDOCAS"D"RETURNINGCONTENT)
      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      <ROW><OBJECT_ID>10</OBJECT_ID></ROW><ROW><OBJECT_ID>20</OBJECT_ID></ROW>
        • 1. Re: ORA-19010
          odie_63
          Hi,
          ORA-19010: Cannot insert XML fragments
          That's because the oradb scheme returns a collection of ROW elements, not a valid XML document (i.e. single-rooted).
          The solution is to add a root element, e.g. :
          insert into resp 
          values ( xmlquery('<ROWSET>{collection("oradb:/PUBLIC/ALL_OBJECTS")}</ROWSET>' returning content) );
          Also, another question is how do i get position/index of a node in an xml document? I tried using "position()" function but it does not appear to work.
          In the following example, I would like to get the index of the node ROW, preferably as attribute of <ROW>.
          The usual way is via FOR ORDINALITY :
          SQL> with src as (
            2   select xmltype('<ROWSET><ROW><OBJECT_ID>10</OBJECT_ID></ROW><ROW><OBJECT_ID>20</OBJECT_ID></ROW></ROWSET>') as xdoc
            3   from dual
            4  )
            5  select x.*
            6  from src
            7     , xmltable('/ROWSET/ROW'
            8         passing src.xdoc
            9         columns idx for ordinality
           10               , object_id number path 'OBJECT_ID'
           11       ) x
           12  ;
           
                 IDX  OBJECT_ID
          ---------- ----------
                   1         10
                   2         20
           
          However, if you want to actually add the index, it's a little different.
          An XQuery positional variable can do that, but be aware it's very slow :
          SQL> with src as (
            2   select xmltype('<ROWSET><ROW><OBJECT_ID>10</OBJECT_ID></ROW><ROW><OBJECT_ID>20</OBJECT_ID></ROW></ROWSET>') as xdoc
            3   from dual
            4  )
            5  select xmlquery(
            6           'for $i at $pos in /ROWSET/ROW
            7            return element ROW { attribute num {$pos}, $i/child::* }'
            8           passing src.xdoc
            9           returning content
           10         )
           11  from src
           12  ;
           
          XMLQUERY('FOR$IAT$POSIN/ROWSET
          --------------------------------------------------------------------------------
          <ROW num="1"><OBJECT_ID>10</OBJECT_ID></ROW><ROW num="2"><OBJECT_ID>20</OBJECT_I
           
          • 2. Re: ORA-19010
            user503699
            Mark,

            Hats off to you once again!!!
            I did not even know about "at $pos" part of the "for" syntax.
            Just one more doubt.
            I am trying to execute following queries and one (against ALL_OBJECTS) never returns (ok, I gave up after 5-10 minutes) whereas the one (against XMLTYPE table containing data from ALL_OBJECTS) returns results. Any idea why?
            select xmlquery('<ROWSET>{collection("oradb:/PUBLIC/ALL_OBJECTS")/ROW[position() <= 2]}</ROWSET>' returning content) from dual ;
            Above never returns
            select xmlserialize(document xmlquery('<ROWSET>{collection("oradb:/HR/RESP")/ROWSET/ROW[position() <= 2]}</ROWSET>' returning content) as clob indent size=2) from dual ;
            The RESP table is table of XMLTYPE and is populated using
            insert into resp select xmlquery('<ROWSET>{collection("oradb:/PUBLIC/ALL_OBJECTS")}</ROWSET>' returning content) from dual ;