ora 22992 on a LOB field i am not using

Question

I have the following simple oracle query:

select A.field
from table1 A
left join table2@remotedb B on A.id = B.id

Where table B has a BLOB field

It runs fine

If i add a concat to the select:

select A.field||'x'
from table1 A
left join table2@remotedb B on A.id = B.id

I get the following error: "ora-22992 cannot use lob locators selected from remote tables"

Why adding a concat to a filed which isn't the LOB file is giving me this error?!?

What can i do to avoid it?

Answer 1

check this

with sub1 as
(
select /*+ materialize */ A.field
from table1 A
left join table2@remotedb B on A.id = B.id
)
select field || 'x'
from sub1