dblink错误处理两则和过程中调用dblink

在使用dblink时遇到两个问题，解决方法如下：

vs_sql := 'create database link tim
     connect to tim identified by xxxxxxxxx
       using ''(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ' ||
              as_timip ||
              ')(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = tim) ) )'

execute immediate  vs_sql;
1. 通过dblink调用远程的存储过程时会出现ORA-02064: distributed operation not supported错误，
 这是就可以在存储过程中加入
 PRAGMA AUTONOMOUS_TRANSACTION;
 如
 create or replace procedure p_d(。。。) is
  PRAGMA AUTONOMOUS_TRANSACTION;   ---加上该行
  vs_areacode varchar2(64); 
  。。。
begin
。。。

 

2.drop database link tim会提示
ORA-02018: database link of same name has an open connection

如下处理：
在过程中加入：
 commit; ---先提交，否则报 database link  is in used
 execute immediate 'alter session close database link tim';

然后再删除dblink

附：
create or replace procedure p_a(ai_code   in int,
                                             as_timip  in varchar2,
                                             as_timpwd in varchar2 default 'xxxxxxxx', --tim数据库中tim用户的密码
                                             ao_err    out varchar2) is
  /*
     2008-11-13--prt--
     参数：
       ai_code为省的号码，必须注意不要写错
       as_timip为tim的ip地址
       ao_err为返回的错误记录，如果为空表示没有错误
 
     功能：
       自动把整合一个数据库的几个表到另外一个数据库上去
 
     前提：
       两个数据库要能联通
       需要在sysdba权限下赋予建表和dblink的权限
       sqlplus / as sysdba;
       grant create any table to charge;
       grant create database link to charge;
  */

  -- select 'exec p_timarea_charge('||areacode||','''||timip||''',''xxxxxxx'',:c);  ' from upprov where tim <> '0';

  vs_tabname varchar2(64) := 'aaaa_aaaa';
  vs_dblink  varchar2(64) := '';
  vi_count   int;
  vs_errinfo varchar2(4000);
  vi_err     int;
  vs_procode varchar2(32);
  vs_sql     varchar2(4000);
  type vty_hcl is record(
    id       NUMBER,
    jc       VARCHAR2(20),
    fullname VARCHAR2(512),
    areacode VARCHAR2(64));

  vt_area_arr vty_hcl;

  TYPE vty_cur IS REF CURSOR;
  vt_cur vty_cur;

  vexp_dblink exception;
begin
  --execute immediate 'set serverout on';
  commit; ---先提交，否则报 database link  is in used
  begin
    vs_sql := 'alter session close database link tim';
    execute immediate vs_sql;
  exception
    when others then
      vs_errinfo := sqlerrm;
  end;

  begin
    vs_sql := 'drop database link tim';
    execute immediate vs_sql;
  exception
    when others then
      vs_errinfo := sqlerrm;
  end;

  begin
    vs_sql := 'create database link tim
     connect to tim identified by ' || as_timpwd ||
              ' using ''(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ' ||
              as_timip ||
              ')(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = tim) ) )''';
    execute immediate vs_sql;
  exception
    when others then
      vs_errinfo := sqlerrm;
  end;

  begin
    vs_sql := 'select count(1) from global_name@tim';
    execute immediate vs_sql
      into vi_count;
  exception
    when others then
      vs_errinfo := sqlerrm;
  end;

  ---有dblink可以使用
  if vi_count > 0 then
    vs_dblink := '@tim';
  else
    ---没有dblink tim则退出
    raise vexp_dblink;
  end if;

  select procode into vs_procode from upprov where areacode = ai_code;

  begin
    vs_sql := 'drop table ' || vs_tabname;
    execute immediate vs_sql;
  exception
    when others then
      null;
  end;
  vs_sql := 'create table ' || vs_tabname || '(
        ID         NUMBER not null,
        NAME       VARCHAR2(50),
        LASTID     NUMBER,
        JC         VARCHAR2(20),
        FULLNAME   VARCHAR2(512),
        XH         NUMBER,
        AREACODE   VARCHAR2(64),
        PARENTCODE VARCHAR2(64)
      )';
  execute immediate vs_sql;

  vs_sql := 'create index IDX_asdfghjklzxcvvbn2222_01 on ' || vs_tabname ||
            '(ID)';
  execute immediate vs_sql;

  vs_sql := 'create index IDX_asdfghjklzxcvvbn2222_02 on ' || vs_tabname ||
            '(LASTID)';
  execute immediate vs_sql;

  vs_sql := 'create index IDX_asdfghjklzxcvvbn2222_03 on ' || vs_tabname ||
            '(AREACODE)';
  execute immediate vs_sql;

  vs_sql := 'create index IDX_asdfghjklzxcvvbn2222_04 on ' || vs_tabname ||
            '(name)';
  execute immediate vs_sql;

  vs_sql := 'insert into ' || vs_tabname ||
            '(id, name, lastid, jc, fullname, xh, areacode, parentcode)
    select id, name, null, 1, name, null, ' || ai_code ||
            ', 0 from province' || vs_dblink;
  execute immediate vs_sql;

  vs_sql := 'insert into ' || vs_tabname ||
            '(id, name, lastid, jc)
    select id, name, lastid, 2 from city' || vs_dblink;
  execute immediate vs_sql;

  vs_sql := 'insert into ' || vs_tabname ||
            '(id, name, lastid, jc)
    select id, name, lastid, 3 from county' || vs_dblink;
  execute immediate vs_sql;

  vs_sql := 'insert into ' || vs_tabname ||
            '(id, name, lastid, jc)
    select id, name, lastid, 4 from town' || vs_dblink;
  execute immediate vs_sql;

  vs_sql := 'insert into ' || vs_tabname ||
            '(id, name, lastid, jc)
    select id, name, lastid, 5 from village' || vs_dblink;
  execute immediate vs_sql;

  ---分别对市县乡村的序号做处理
  for vi_j in 2 .. 5 loop
    vs_sql := 'select id, jc, fullname, areacode
                    from ' || vs_tabname || '
                   where jc = ' || (vi_j - 1);
    open vt_cur for vs_sql;
 
    loop
      fetch vt_cur
        into vt_area_arr;
      exit when vt_cur%notfound;
   
      vs_sql := 'update ' || vs_tabname ||
                ' set xh = (select c.rn
                   from (select rownum rn, id
                           from (select id
                                   from ' || vs_tabname || '
                                  where jc = ' || vi_j ||
                ' and lastid = ' || vt_area_arr.id ||
                ' order by id) b) c
                  where c.id = ' || vs_tabname ||
                '.id)  where lastid = ' || vt_area_arr.id || ' and jc = ' || vi_j;
      execute immediate vs_sql;
   
      --'7' || '.' || xh
      vs_sql := 'update ' || vs_tabname || ' set areacode   = ''' ||
                vt_area_arr.areacode || '''||''.''' ||
                '||xh,
              parentcode = ''' || vt_area_arr.areacode || ''',
             fullname   = ''' || vt_area_arr.fullname ||
                '''|| name
          where jc = ' || vi_j || ' and lastid = ' ||
                vt_area_arr.id;
      execute immediate vs_sql;
   
    end loop;
    close vt_cur;
  end loop;

  delete from area;

  vs_sql := 'select count(1) from ' || vs_tabname ||
            ' where areacode is null ';
  execute immediate vs_sql
    into vi_err;

  vi_count := 0;
  vs_sql   := 'select count(1) from ' || vs_tabname ||
              ' where name in (select name from ' || vs_tabname ||
              ' where jc in(2,3)) and jc>3 and name not in (''企业'',''社区'')';
  execute immediate vs_sql
    into vi_count;

  ---tim的区域错误，则加入该表
  if vi_err + vi_count > 0 then
    begin
      vs_sql := ' drop table area_err_' || vs_procode;
      execute immediate vs_sql;
    exception
      when others then
        null;
    end;
 
    vs_sql := ' create table area_err_' || vs_procode ||
              ' as select * from ' || vs_tabname ||
              ' where (areacode is null)  or (name in (select name from ' ||
              vs_tabname ||
              ' where jc in(2,3)) and jc>3 and name not in (''企业'',''社区''))';
    execute immediate vs_sql;
 
    vs_sql := ' delete from ' || vs_tabname || ' where areacode is null ';
    execute immediate vs_sql;
  end if;

  vs_sql := 'insert into area
    select areacode, id, name, fullname, parentcode, jc, 0 from ' ||
            vs_tabname;
  execute immediate vs_sql;

  begin
    vs_sql := ' drop table area_' || vs_procode;
    execute immediate vs_sql;
  exception
    when others then
      null;
  end;

  vs_sql := ' create table area_' || vs_procode ||
            ' as select * from area ';
  execute immediate vs_sql;

  /* delete from area_tim;
  insert into area_tim
    select * from area;
 
  delete from areatoareatim;
  insert into areatoareatim
    select areacode, areacode from area;
  commit;*/
  vs_sql := 'drop table ' || vs_tabname;
  execute immediate vs_sql;
  commit;

exception

  when others then
    ao_err := '错误原因：' || sqlerrm || '附：' || vs_sql;
    dbms_output.put_line(ao_err);
    rollback;
 
    if vt_cur%isopen then
      close vt_cur;
    end if;
 
    begin
      execute immediate ' drop table area_' || vs_procode;
    exception
      when others then
        null;
    end;
 
    begin
      execute immediate 'drop table ' || vs_tabname;
    exception
      when others then
        null;
    end;
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/559237/viewspace-495669/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/559237/viewspace-495669/