oracle - 通过数据库链接查看以确定LOB列是否为NULL

新列（BLOB类型）已添加到远程数据库表中。我维护的应用程序通过视图读取该表，该视图是数据库链接上的简单select语句。我需要更新视图，以便可以访问新列。

显然，您无法通过数据库链接读取LOB列：

ORA-22992：无法使用从远程表中选择的LOB定位器

到目前为止，我只需要知道行是否有数据。该查询可以完美地工作：

select foo_id, foo_name, foo_date,
case
    when foo_binary is not null then 1
    else 0
end as has_foo_binary_data
from remote_table@remote_database;

但是，除非删除case表达式，否则无法创建视图：

create view remote_foo as
select foo_id, foo_name, foo_date,
case
    when foo_binary is not null then 1
    else 0
end as has_foo_binary_data
from remote_table@remote_database;

RA-22992：无法使用从远程表中选择的LOB定位器

DBMS_LOB.GETLENGTH(foo_binary) 开始可爱的catch-22：

ORA-02069: global_names parameter must be set to TRUE for this operation

alter session set global_names = true`;
create view ...

ORA-02085: database link remote_database connects to remote_sid

Cause: a database link connected to a database with a different name. The connection is rejected.

Action: create a database link with the same name as the database it connects to, or set global_names=false.

Not sure if I've hit a wall or I'm just making silly mistakes. Is there some way to obtain any of information about the CLOB (not the blob data) that works in a view?

查看更多