1

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.

  • Try creating synonym instead of view – Kumar Jan 15 at 14:45
  • I need to grant access to the db1-table to other schemas in instance 2. Thats only possible with views. – Daniel Jan 15 at 16:00
1

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.

|improve this answer|||||
  • Ok so my SQL-Developer casts the lob automaticaly so thats why the "normal" select work?! Unfortunatly, as mentioned in the documentation DBMS_LOB.SUBSTR(your_lob, 32767, 1); does not work in the view. – Daniel Jan 15 at 15:06
  • 1
    @Daniel create your view with trimmed lobs on the source database and create a view of the view on the target database – kevinsky Jan 15 at 15:09
  • Thanks man works perfectly. Luckily no column exceeds the maxval auf varchar2 right now. – Daniel Jan 15 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.