This discussion is archived
0 Replies Latest reply: Sep 6, 2012 1:15 AM by 946607 RSS

inserting XML from remote db causes ORA-21700: object does not exist

946607 Newbie
Currently Being Moderated
Hi.
I'm using Oracle 11.2.0 and I'm trying to write a PL/SQL procedure which takes some tables stored in one remote server (connected by a database link) and store them in a local server.
I've no problems with usual tables, but I get "ORA-21700: object does not exist" with a table using a schema registered column of type XMLTYPE.

This is the code in my procedure:
insert into ICF.input_xml n (
ID,
FILENAME,
CREATION_DATE,
XML )
SELECT
o.ID,
o.FILENAME,
o.creationDate,
o.XML
FROM input_xml@remote o;

I get this ORA-21700 error in both SQL developer or SQL*Plus when compiling the procedure. However, if I execute this insert alone, out of the procedure, it works great. Meaning, it takes all the XML columns from input_xml@remote and store them at ICF.input_xml successfully if the insert is executed directly, out of the procedure.

XDB is installed and the same schema is registered with the same name in both servers, so I'm able to make XML operations with these tables in both servers.

When doing that with another table which XML column is not registered with any xml schema, it works great even inside the procedure. The creation sintax of the schema with which I registered the first xml table is:
BEGIN
DBMS_XMLSCHEMA.registerSchema(
schemaURL => 'http://schemas.conf.com/batch/schemacon',
schemaDoc => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xs:schema version="1.0"
[ ... ] '
);
END;

How could avoid this compiling error (because it actually works)? Or, is there any other way to do that?

Legend

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