OraFAQ Forum: SQL & PL/SQL » When converting to xml: ORA-21700: Object does not exist or is marked for delete

Home » SQL & PL/SQL » SQL & PL/SQL » When converting to xml: ORA-21700: Object does not exist or is marked for delete (Oracle 10.2.0.5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

When converting to xml: ORA-21700: Object does not exist or is marked for delete [message #529500]

Tue, 01 November 2011 09:34

c_stenersen
Messages: 255
Registered: August 2007

Senior Member

We're having a problem when connecting to an rss feed and trying to convert this by using httpuritype.getxml() We're able to get the file using httpuritype.getclob() but when using getxml or the xmltype constructor it fails with the ORA-21700 exception. Does anyone have an idea of why this could happen? It works on some of our servers but not on all of them. Also it works for some urls but not all of them. Are there some special settings which might not be set correctly, or grants which need to be given? I know it's hard to find the problem without actually knowing what settings we have, but do any of you know where the problem might lie? (Because I don't even know where to start looking.) Have you had the same problem in the past?

SQL> declare
  2      v_uri       Httpuritype;
  3      v_xml_clob  clob;
  4      v_xml       Xmltype;
  5  begin
  6      v_uri       := httpuritype.createUri('http://www.snb.ch/selector/en/mmr
/intfeed/rss/rss.htm');
  7      v_xml_clob  := v_uri.getClob();
  8      v_xml       := Xmltype(v_xml_clob);
  9
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at line 8


SQL>

SQL> declare
  2      v_uri       Httpuritype;
  3      v_xml_clob  clob;
  4      v_xml       Xmltype;
  5  begin
  6      v_uri       := httpuritype.createUri('http://www.banqueducanada.ca/stat
s/assets/rates_rss/noon/fr_all.xml');
  7      v_xml_clob  := v_uri.getClob();
  8      v_xml       := Xmltype(v_xml_clob);
  9
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>

Report message to a moderator

Re: When converting to xml: ORA-21700: Object does not exist or is marked for delete [message #529502 is a reply to message #529500]

Tue, 01 November 2011 09:41

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It works in 10.2.0.4:

SQL> declare
  2    v_uri       Httpuritype;
  3    v_xml_clob  clob;
  4    v_xml       Xmltype;
  5  begin
  6    v_uri       := httpuritype.createUri('http://www.snb.ch/selector/en/mmr/intfeed/rss/rss.htm');
  7    v_xml_clob  := v_uri.getClob();
  8    v_xml       := Xmltype(v_xml_clob);
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> @v

Version Oracle : 10.2.0.4.0

Regards
Michel

Report message to a moderator

Re: When converting to xml: ORA-21700: Object does not exist or is marked for delete [message #529504 is a reply to message #529502]

Tue, 01 November 2011 10:06

c_stenersen
Messages: 255
Registered: August 2007

Senior Member

The thing is that we have the same database version on database A where it doesn't work and on database B where it does work. So I don't think it's the version which is the problem. There must be something wrong in the settings somewhere. Also the fact that it works for some URLs but not for others is a bit strange.

I tried checking the character set after reading somewhere that this could be the problem (and seeing that the character set of the xml files returned by the two URLs were different), but the character set is the same in both databases. I'm completely lost.

Report message to a moderator

Re: When converting to xml: ORA-21700: Object does not exist or is marked for delete [message #529560 is a reply to message #529504]

Tue, 01 November 2011 15:29

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

What happens if you:

purge dba_recyclebin;

then try running your code again?

Report message to a moderator

Re: When converting to xml: ORA-21700: Object does not exist or is marked for delete [message #529635 is a reply to message #529560]

Wed, 02 November 2011 04:28

c_stenersen
Messages: 255
Registered: August 2007

Senior Member

I tried purging the recyclebin. I still have the same error.

Report message to a moderator

Re: When converting to xml: ORA-21700: Object does not exist or is marked for delete [message #529701 is a reply to message #529635]

Wed, 02 November 2011 10:28

c_stenersen
Messages: 255
Registered: August 2007

Senior Member

We experimented a little with the xml files, and tried to replace the header of the one that didn't work (from snb.ch) with the header of the one that worked (from banquecanada.ca). It then works. All we had to do was that for the xsi:SchemaLocation attribute which pointed to w3.org in the one which didn't work we replaced it by w3c.org which was in the one that worked.

But now however our dba has installed DBMS_XDB on the server, and it works for both xml files without any changes. So that must have been what was missing on the servers where it didn't work.

Thank you for your help anyways. Smile

Report message to a moderator

Previous Topic:	PLS-00049 BAD BIND VARIABLE
Next Topic:	how to solve this query? Analytical SQL Functions?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 04 23:38:38 CDT 2025