XMLTYPE datatype and ORA-22992

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
           <rowset>
            <row>
             <owner>SYS</owner>
 
</row></rowset>