Ora-01000 Using Xmlelement/Xmltype Over Dblink (Doc ID 1369617.1)

Last updated on FEBRUARY 06, 2017

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Symptoms


In 11g, encountering ORA-01000 in queries that use xmltype/xmlelement over database links.
The ora-1000 error comes from the remote site.

The following reproduces this problem:


alter session set session_cached_cursors=1600;

declare
  v_xmltype XMLTYPE;
  v_SQLQuery varchar2(4000);
begin
  for rec in (select * from dba_objects where rownum<=5000) loop
   v_SQLQuery:='SELECT xmlelement ("TEST",object_name) FROM DBA_OBJECTS@dbl1
   where owner='''||rec.owner||''' and
   object_name='''||rec.object_name||''' and
   rownum=1';
   EXECUTE IMMEDIATE v_SQLQuery INTO v_xmltype;
  end loop;
end;
/



ERROR at line 1:
ORA-02063: preceding 2 lines from DBL1
ORA-06512: at line 7

In remote site: ora-1000

Workaround:
Set session_cached_cursors=0 but this may lead to performance decrease



Changes

Upgraded to 11g, the problem does not reproduce with earlier releases.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms