4 Replies Latest reply: Mar 11, 2013 5:31 PM by 990942 RSS

    problem with RenameResource

    990942
      Hello,

      I have a problem with RENAMERESOURCE procedure,

      I create an XMLTYPE table with the following instruction

      CREATE TABLE TEST OF XMLType XMLTYPE store as BINARY XML


      when I execute this:
      1) insert into table the xml value
      2) create resource using the ref at the xmltype inserted
      3) quering the resource created
      4) rename the resource
      5) quering the resource renamed

      SET SERVEROUTPUT ON
      declare
      res boolean;
      v clob;
      xref ref XMLTYPE;
      begin
      --insert xml into table
      insert into TEST t values (XMLTYPE('<root><test>test</test></root>')) returning ref(t) into xref;
      --create resource using the ref at the xmltype inserted into table
      res := dbms_xdb.CreateResource('/temp/test_before',xref );
      commit;
      --quering the resource created
      SELECT XMLType.getClobVal(column_value) into v from XMLTABLE('doc(''/temp/test_before'')//test' ) ;
      dbms_output.put_line('before rename:'||v);
      --Rename the resource
      dbms_xdb.renameResource('/temp/test_before','/temp','test_after');
      commit;
      --quering the resource renamed
      SELECT XMLType.getClobVal(column_value) into v from XMLTABLE('doc(''/temp/test_after'')//test' ) ;
      dbms_output.put_line('after rename:'||v);
      end;     


      I would expect the same result but I get an error at the second query:
      Error report:
      ORA-01403: no data found
      ORA-06512: at line 15
      01403. 00000 - "no data found"
      *Cause:   
      *Action:
      before rename:<test>test</test>


      the resource appears to have been renamed correctly and if execute this query
      SELECT * from XMLTABLE('doc(''/temp/test_after'')' )

      I get as expected the result:
      <root>
      <test>test</test>
      </root>

      but if I execute this query
      SELECT * from XMLTABLE('doc(''/temp/test_after'')//test' )
      I get no result


      Thanks

      Bassanelli Michele
        • 1. Re: problem with RenameResource
          mdrake-Oracle
          Works for me
          SQL>   SET SERVEROUTPUT ON
          SQL>   declare
            2       res  boolean;
            3       v  clob;
            4       xref ref XMLTYPE;
            5    begin
            6      if (DBMS_XDB.EXISTSRESOURCE('/public/test_before')) then
            7        DBMS_XDB.DELETERESOURCE('/public/test_before');
            8        commit;
            9      end if;
           10      if (DBMS_XDB.EXISTSRESOURCE('/public/test_after')) then
           11        DBMS_XDB.DELETERESOURCE('/public/test_after');
           12        commit;
           13      end if;
           14
           15
           16       --insert xml into table
           17      insert into TEST t  values (XMLTYPE('<root><test>test</test></root>')) returning ref(t) into xref;
           18      --create resource using the ref at the xmltype inserted into table
           19      res := dbms_xdb.CreateResource('/public/test_before',xref );
           20      commit;
           21      --quering the resource created
           22      SELECT XMLType.getClobVal(column_value) into v from XMLTABLE('doc("/public/test_before")//test' ) ;
           23      dbms_output.put_line('before rename:'||v);
           24      --Rename the resource
           25      dbms_xdb.renameResource('/public/test_before','/public','test_after');
           26      commit;
           27      --quering the resource renamed
           28      SELECT XMLType.getClobVal(column_value) into v from XMLTABLE('doc("/public/test_after")//test' ) ;
           29      dbms_output.put_line('after rename:'||v);
           30    end;
           31  /
          before rename:<test>test</test>
          after rename:<test>test</test>
          
          PL/SQL procedure successfully completed.
          
          SQL>
          Which database version...

          Note I prefer to use double quotes (") for the argument to fn:doc, but I doubt this makes a difference.
          • 2. Re: problem with RenameResource
            odie_63
            Mark,

            I can reproduce on 11.2.0.2 :
            SQL> declare
              2    res  boolean;
              3    xref ref xmltype;
              4  begin
              5    insert into tmp_xml t values (xmltype('<root><test>test</test></root>')) returning ref(t) into xref;
              6    res := dbms_xdb.CreateResource('/public/test_before', xref );
              7  end;
              8  /
            
            PL/SQL procedure successfully completed.
            
            SQL> select * from xmltable('doc("/public/test_before")//test') ;
            
            COLUMN_VALUE
            --------------------------------------------------------------------------------
            <test>test</test>
            
            SQL> select t.xmldata.dispname
              2       , t.xmldata.xmlref
              3  from xdb.xdb$resource t
              4  where equals_path (object_value, '/public/test_before') = 1 ;
            
            XMLDATA.DISPNAME
            --------------------------------------------------------------------------------
            XMLDATA.XMLREF
            --------------------------------------------------------------------------------
            test_before
            00002802098F5DBE27FE174960B3C3FE05E45AAC73E5907C45F5F24829AC7774E650FF7A910107D5
            BB0000
            
            
            SQL> exec dbms_xdb.renameResource('/public/test_before', '/public', 'test_after');
            
            PL/SQL procedure successfully completed.
            
            SQL> select * from xmltable('doc("/public/test_after")//test') ;
            
            no rows selected
            
            SQL> select t.xmldata.dispname
              2       , t.xmldata.xmlref
              3  from xdb.xdb$resource t
              4  where equals_path (object_value, '/public/test_after') = 1 ;
            
            XMLDATA.DISPNAME
            --------------------------------------------------------------------------------
            XMLDATA.XMLREF
            --------------------------------------------------------------------------------
            test_after
            After the call to renameResource, the resource loses its REF pointer to the row in the XMLType table.
            Is it the expected behaviour?

            A SQL trace confirms that the whole content is replaced :
            UPDATE /*+ NO_REF_CASCADE */ "XDB"."XDB$RESOURCE" e SET  e.XMLEXTRA= SYS.XMLTYPEEXTRA("SYS"."XMLTYPEPI"(),"SYS"."XMLTYPEPI"()),e.XMLDATA=:1 WHERE ref(e) = :2 returning sys_row_delta() into :3
            • 3. Re: problem with RenameResource
              mdrake-Oracle
              Looks like we need a bug filed for this. Based on my testing it appears the issue is fixed in the next major release of the database, but a bug will be required to determine how to fix it in the current release.
              • 4. Re: problem with RenameResource
                990942
                I'm using
                11g Release 11.2.0.2.0