XMLTYPE datatype and ORA-22992

Anton has a small but informative post on LOB handling across database links and avoiding a possible ORA-22992. I was not sure if XMLTYPE’s, CLOB based storage or Binary XML (Securefile) storage had the same limitations and/or in need of possible workarounds. So the best way to be sure is of course too just to test it.

So that is what I did…

SQL> select * from v$version;

BANNER
------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create user clob identified by clob;

User created.

SQL> grant dba, xdbadmin to clob;

Grant succeeded.

SQL> conn clob/clob
Connected.

SQL>  CREATE TABLE "CLOB"."TEST_XMLTYPE_LOB"
  2    (    "ID"      NUMBER,
  3         "C_LOB"   CLOB,
  4         "XMLCLOB" "XMLTYPE",
  5         "XMLBIN"  "XMLTYPE"
  6    )
  7   TABLESPACE "USERS"
  8  LOB ("C_LOB") STORE AS BASICFILE
  9  XMLTYPE COLUMN "XMLCLOB" STORE AS BASICFILE CLOB
 10  XMLTYPE COLUMN "XMLBIN" STORE AS SECUREFILE BINARY XML;

Table created.

SQL> set long 100000
SQL> set pages 5000
SQL> set trimspool on
SQL> set lines 170

SQL> select dbms_metadata.get_ddl('TABLE','TEST_XMLTYPE_LOB') from dual;

  CREATE TABLE "CLOB"."TEST_XMLTYPE_LOB"
   (    "ID" NUMBER,
        "C_LOB" CLOB,
        "XMLCLOB" "SYS"."XMLTYPE" ,
        "XMLBIN" "SYS"."XMLTYPE"
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 LOB ("C_LOB") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
 XMLTYPE COLUMN "XMLCLOB" STORE AS BASICFILE CLOB (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
 XMLTYPE COLUMN "XMLBIN" STORE AS SECUREFILE BINARY XML (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  CACHE  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
  ALLOW NONSCHEMA DISALLOW ANYSCHEMA

SQL> -- Creating a "loopback database link" 
  
SQL> create database link XX connect to clob identified by clob
  2  using 'ORCL112.amis.local';

Database link created.

SQL> col db_link for a10
SQL> col host    for a20

SQL> select db_link, host from user_db_links@xx;

DB_LINK    HOST
---------- --------------------
XX         ORCL112.amis.local
  
SQL> cle bre col
breaks cleared
columns cleared

SQL> describe test_xmltype_lob@XX

 Name             Null?    Type
 ---------------- -------- -----------------------------------
 ID                        NUMBER
 C_LOB                     CLOB
 XMLCLOB                   SYS.XMLTYPE
 XMLBIN                    SYS.XMLTYPE STORAGE BINARY

SQL> select count(*) from all_objects;

  COUNT(*)
----------
     72908
 
SQL> -- Be aware the following took over 1 minute to complete 
     
SQL> insert into test_xmltype_lob
  2  values
  3  (1,
  4   xmltype(cursor(select * from all_objects)).getCLOBVal(),
  5   xmltype(cursor(select * from all_objects)),
  6   xmltype(cursor(select * from all_objects))
  7  );
  
1 row created.

SQL> select dbms_lob.getlength(xl.c_lob)                as "CLOB",
  2         dbms_lob.getlength(xl.xmlclob.getCLOBVal()) as "XMLTYPE_CLOB",
  3         dbms_lob.getlength(xl.xmlbin.getCLOBVal())  as "XMLTYPE_BINARY"
  4  from test_xmltype_lob xl;

      CLOB XMLTYPE_CLOB XMLTYPE_BINARY
---------- ------------ --------------
  30334775     30334775       32245553  

SQL> select dbms_lob.getlength(xl.c_lob) as "CLOB"
  2  from test_xmltype_lob@XX xl;

select dbms_lob.getlength(xl.c_lob) as "CLOB"
                          *
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables

SQL> select dbms_lob.getlength(xl.xmlclob.getCLOBVal()) as "XMLTYPE_CLOB"
  2  from test_xmltype_lob@XX xl;

XMLTYPE_CLOB
------------
    30334775

SQL> select dbms_lob.getlength(xl.xmlbin.getCLOBVal())  as "XMLTYPE_BINARY"
  2  from test_xmltype_lob@XX xl;

XMLTYPE_BINARY
--------------
      32245553

SQL> set long 50

SQL>
SQL> -- Anton's workaround works of course as advertised in this version
SQL>

SQL> select id,
  2         ( select c_lob from dual ) as "CONTENT",
  3         dbms_lob.getlength( ( select c_lob from dual ) ) as "CLOB"
  4  from test_xmltype_lob@XX xl;

        ID CONTENT                                                  CLOB
---------- -------------------------------------------------- ----------
         1 < ?xml version="1.0"?>                                30334775
           
            
             SYS