Oracle 临时表解决ORA-22992问题

Oracle 临时表解决ORA-22992问题

通过Database Link集成远程数据库时，从远程数据库上查询Blob字段时总返回ORA- 22992 错误 。有人采用物化视图解决这个问题，但物化视图同步机制会带来麻烦，如果同步时间设置过短，则占用大量的系统资源，给服务器带来极大的压力；如果设置时间过长，前台用户不可接受。

参考网上资料，据说来自 AskTom，使用全局临时表，实现了自认为优雅的解决。 

代码如下：
create or replace package pkg_photo as
  type sys_refcursor is ref cursor;
  procedure proc_photo(p_sfzh in string,
  p_photo OUT pkg_rkzp.sys_refcursor);
  end pkg_photo;
 
  create or replace package body pkg_photo as
   procedure proc_photo(p_sfzh in string, p_photo OUT pkg_rkzp.sys_refcursor)
      AS
      v_num number;
      v_sql varchar2(2000);
      v_sfzh string(18);
      BEGIN   
          select count(*) into v_num from user_tables where table_name=upper('tmp_jn_czrk_zp');
          dbms_output.PUT_LINE(v_num);
          if v_num<1 then
              v_sql := 'create global temporary table tmp_ryzp(sfzh char(18),photo blob) on commit preserve rows';
              EXECUTE IMMEDIATE v_sql;
          end if;
         
          v_sql := 'insert into tmp_photo select sfzh,photo from v_remote_table@mydblink where sfzh=:1';
          EXECUTE IMMEDIATE v_sql using p_sfzh;
         
          OPEN p_photo FOR v_sql;
          EXCEPTION
            WHEN NO_DATA_FOUND            
              THEN NULL;
            WHEN OTHERS
              THEN RAISE;
      END proc_photo;
end pkg_photo;