通过dblink从远程表中选择Oracle字段
通过dblink从远程表中选择Oracle字段
提问于 2019-03-22 18:07:38
我试图通过远程表格通过flink选择一个clob字段，我能够看到编辑器结果中的值，但无法选择thru程序或任何导出。
SQL Error: 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我能够选择本地字段并在应用程序中使用它们，但是失败了。当在editor.there中运行查询时，我可以很好地查看蟾蜍的结果，也不能导出特定的字段。
回答 1
Database Administration用户
发布于 2019-03-24 15:55:06
远程数据库：
SQL> create table t1(id number, c1 clob);
Table created.
SQL> insert into t1 values(1, 'HELLO WORLD!');
1 row created.
SQL> insert into t1 values(2, LPAD('A', 4000, 'A'));
1 row created.
SQL> update t1 set c1 = c1 || c1 where id = 2;
1 row updated.
SQL> /
1 row updated.
SQL> /
1 row updated.
SQL> /
1 row updated.
SQL> /
1 row updated.
SQL> commit;
Commit complete.
SQL> select id, length(c1) from t1;
ID LENGTH(C1)
---------- ----------
1 12
2 128000本地数据库：
SQL> desc t1@s112
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
C1 CLOB
SQL> select c1 from t1@s112 where id = 1;
ERROR:
ORA-22992: cannot use LOB locators selected from remote tables
no rows selected
SQL> select to_char(c1) from t1@s112 where id = 1;
TO_CHAR(C1)
--------------------------------------------------------------------------------
HELLO WORLD!问题是，CHAR类型仅限于4000字节，因此：
SQL> select to_char(c1) from t1@s112 where id = 2;
select to_char(c1) from t1@s112 where id = 2
*
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual:
128000, maximum: 4000)
ORA-02063: preceding line from S112一种可能的解决办法：
SQL> create global temporary table t1_tmp on commit preserve rows as select * from t1@s112 where 1 = 2;
Table created.
SQL> insert into t1_tmp select * from t1@s112 where id = 2;
1 row created.
SQL> commit;
Commit complete.
SQL> select id, length(c1) from t1_tmp;
ID LENGTH(C1)
---------- ----------
2 128000以上数据来源于11.2数据库。
从12.2开始，取消了通过dblink选择LOB的限制，不需要解决方法。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/232953
复制相关文章
相似问题
腾讯云开发者
