I'm using Oracle 11.2.0 and I'm trying to write a 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 registered XMLTYPE.
This is the code in my procedure:
insert into ICF.input_xml n (
FROM input_xml@remote o;
I get this error in SQL developer when compiling the procedure. However, if I execute this query by its own, out of the procedure, it works great.
Actually, 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.
How could avoid this compiling error (because it actually works)? Or, is there any other way to do that?
Thanks in advance
Edited by: user1592798 on 29-ago-2012 1:20
Yes, I get the same error when I execute the create procedure... statement from SQL*Plus.
What I wanted to say in my first post is that the insert from select query itself, works ok if I execute it outside the procedure: it takes the XML from the remote server and stores it in the local server without problems.
Edited by: user1592798 on 29-ago-2012 5:08
Oracle automatically creates some types when registering the schema to which the XML column is linked using the following syntax:
schemaURL => 'http://schemas.conf.com/batch/schemacon',
schemaDoc => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
[ ... ] '
At [oracle docs|http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjbas.htm#CIHBIBEA] it says you cannot use database links within PL/SQL code to declare a local variable of a remote user-defined type.
With another XML column which is not linked to a XML schema there's no problem. With the schema based XML outside the PL/SQL there's no problem. So, could this schema generated type be the cause of my problem?
Edited by: goyocasero on 06-sep-2012 1:15