温馨提示:本文翻译自stackoverflow.com,查看原文请点击:oracle - View to determine if a LOB column is NULL through a database link

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

发布于 2020-03-28 23:37:23

新列(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?

查看更多

查看更多

提问者
Álvaro González
被浏览
48
Littlefoot 2020-01-31 18:36

You've posted pretty much info already so I'm not going to repeat it all.

您可能要考虑以下选项;不是一个完美的选择,这取决于是否可以。无论如何:

更改远程数据库中的表并添加新列:

alter remote_table add foo_binary_length number;

将其填充到数据库触发器中:

create or replace trigger trg_biu_remtab
  before insert or update on remote_table
  for each row
begin
  :new.foo_binary_length := dbms_lob.getlength(:new.foo_binary);
end;
/

现在,在本地数据库中创建视图是一项简单的任务:

create view remote_foo as
select foo_id, foo_name, foo_date,
case
    when foo_binary_length > 0 then 1      --> this
    else 0
end as has_foo_binary_data
from remote_table@remote_database;

发布
问题

分享
好友

手机
浏览

扫码手机浏览