This discussion is archived
4 Replies Latest reply: Mar 11, 2013 3:31 PM by 990942 RSS

problem with RenameResource

990942 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    I'm using
    11g Release 11.2.0.2.0

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points