Oracle – Create a View with Distributed LOBs

blobdatabase-linkoracleoracle-18cview

I have two Oracle 18c instances (instance 1 with schema DB1 and instance 2 with schema DB2) and I know that there ist a support for distributed lobs since 12.2. So i did the following:

CREATE TABLE DB1.TEST_TABLE ID NUMBER, FOO CLOB;
INSERT INTO DB1.TEST_TABLE (ID, FOO) VALUES (1, "RANDOM TEXT");

Now I created da databaselink to access DB1 from instance 2

SELECT * FROM DB1.TEST_TABLE@REMOTE_LINK

The select works perfect. But if I want to create a view with this select I get an error:

CREATE VIEW DB2.V_TEST_TABLE AS SELECT * FROM DB1.TEST_TABLE@REMOTE_LINK

ORA-22992: cannot use LOB locators selected from remote tables
22992. 00000 -  "cannot use LOB locators selected from remote tables"
*Cause:    A remote LOB column cannot be referenced.
*Action:   Remove references to LOBs in remote tables.

I don't undertand why selecting a distributing lob works fine but creating a view of this select not.

Best Answer

You can use a view if all the objects returned are scalars not lobs. In this case if you returned the first 4000 characters of the LOB that should work as the documentation indicates that using a function that creates a temporary lob on the source is allowed as long as the result that is returned is not a LOB.

The documentation for 12 is here. It does not say that creating a view will work so in the Oracle way that means only what they describe will work:

  • create table t as select * from ...
  • insert into t select * from ...
  • use a function that returns a scalar such as DBMS_LOB.SUBSTR(your_lob, 32767, 1);

Another workaround is to create your view with trimmed lobs on the source database and then create a view of the view on the target database.

Note the little gotcha where selecting LOBS into objects in the destination automatically converts it to the character set of the destination. The workaround is to cast to RAW and select then cast back.

Related Question