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

今天晚上准备利用database link从远程数据库向本地数据库 INSERT数据时,因远程表中含有CLOB字段,在SELECT 和 INSERT INTO操作时,报"ORA-22992: 无法使用从远程表选择的 LOB 定位器"错误,通过创建临时表并从临时成功把数据插入到目的表中。具体解决办法如下：
1.在本地数据库创建临时表：

 create global temporary table ztb_placard_temp as select * from ztb_bidsegmentplacard@dblinkztb 
where 1=2 ;

2.利用database link把远程数据先插入到临时表中（注意:INSERT后先不要commit,否则commit后临时表中数据就会丢失）

 insert into ztb_placard_temp select * from ztb_bidsegmentplacard@dblinkztb where opt_flag='1';

3.在上一操作所在PL/SQL Developer的SQL窗口里输入以下命令：

 insert into ztb_placard select * from ztb_placard_temp;
 commit;

4.查询本地表，数据已经成功插入。

 select * from ztb_placard;

   
创建储存过程 完成上述操作

CREATE OR REPLACE PROCEDURE ztb_placardinsert1 Authid Current_User is
  str      varchar2(300);
  countnum number(2);
BEGIN
  select count(*)
    into countnum
    from user_all_tables a
   where a.table_name like upper('ztb_placard_temp');
  if countnum > 0 then
    execute immediate 'drop table ztb_placard_temp';
  end if;
  str := 'CREATE  TABLE ztb_placard_temp as select * from ztb_bidsegmentplacard@dblinkztb where 1=2';
  execute immediate str;
  str := 'insert into ztb_placard_temp select * from ztb_bidsegmentplacard@dblinkztb where opt_flag=1';
  execute immediate str;
  str := 'insert into ztb_placard select *　from ztb_placard_temp';
  execute immediate str;
  str := 'drop table ztb_placard_temp';
  execute immediate str;
END ztb_placardinsert1;

 

转载于:https://www.cnblogs.com/yangxd1989/archive/2013/01/09/2852400.html