如何在存储过程中创建DbLink？

Question

如何在存储过程中创建DbLink？

Answer 1

begin
 execute immediate 'CREATE DATABASE LINK ... ';
end;
/问：你为什么发这么多个帖子？

Answer 2

初学存储过程，就想试试触发器、存储过程、函数等用法，
请问有什么好的资料推荐看看吗？邮箱：[email protected]

Answer 3

给你转个全的
可以创建public和private类型的link, 也可以直接根据连接串来
创建(不需要tnsnames.ora文件中有相应的entry) CREATE OR REPLACE PROCEDURE crt_dblink
(link_name in varchar2,
 isPublic in number,
 username in varchar2,
 password in varchar2,
 bUseconnstr in number,
 strNetService in varchar2,
 strProtocol in varchar2,
 strHost in varchar2,
 strSID  in varchar2,
 iPort in number)
is
  iCount number;
  strsql varchar2(255);
  strpara  varchar2(30);
  strdomain  varchar2(64);
  strdblink  varchar2(40);
  strdblink2 varchar2(40);
  strOwner varchar2(30);
  iCheckPoint number(1);
  strPrefix varchar2(10);
  strCurUser varchar2(30);begin
  -- SYS_CONTEXT('USERENV','CURRENT_USER')
  strsql :=  'SELECT SYS_CONTEXT(' || chr(39) || 'USERENV' || chr(39)
          || ',' || chr(39) || 'CURRENT_USER' || chr(39) || ') from dual';
  execute immediate strsql into strCurUser;  iCheckPoint :=1;  if (isPublic =1) then
     strPrefix :='Public';
  else
     strPrefix :='';
  end if;  -- check parameter db_domain
  strpara :='db_domain';
  strsql :=' select value from v$parameter where name = :para';
  execute immediate strsql into strdomain using strpara;  if (strdomain is not null) then
     strdblink :=upper(link_name || '.' || strdomain);
  else
     strdblink :=upper(link_name);
  end if;  -- Modification:
  -- Add check for select statement
  -- (if no records found, it will raise an exception and can't continue next work)  iCheckPoint :=2;  if (isPublic =1) then
      strsql := ' select count(*) from all_db_links where db_link = :dblink and OWNER= '''
             || 'PUBLIC' || ''' and username is null';
      execute immediate strsql into icount using strdblink;      if (iCount>0) then
         strsql :='select db_link from all_db_links where db_link = :dblink and OWNER= '''
               || 'PUBLIC' || ''' and username is null';         execute immediate strsql into strdblink2 using strdblink;         if strdblink = upper(strdblink2) then
           execute immediate 'drop public database link ' || strdblink ;
        end if;
      end if;
  else  -- private dblink
      strsql := 'select count(*) from all_db_links where db_link = :dblink and OWNER = '''
             || strCurUser || ''' and username is not null';
      execute immediate strsql into icount using strdblink;      if (iCount>0) then
         strsql := 'select db_link,owner from all_db_links where db_link = :dblink and OWNER ='''
                || strCuruser || ''' and username is not null';         execute immediate strsql into strdblink2,strOwner using strdblink;         -- Make sure each user can only delete his own dblink
         if strdblink = upper(strdblink2) and (strCurUser = strOwner) then
            execute immediate 'drop database link ' || strOwner || '.' || strdblink;
         end if;
      end if;
  end if ;
  iCheckPoint:=3;
  -- Last step: Create new database link(public or private)
  if (isPublic = 1) then
      if (bUseconnstr=1) then
          strsql :=' Create public database link ' || link_name ||' using ' || chr(39)
                || ' (DESCRIPTION=(ADDRESS=(PROTOCOL=' || strProtocol ||')'
                || ' (HOST=' || strHost || ')'
                || ' (PORT=' || to_char(iPort) ||'))'
                || ' (CONNECT_DATA = '
                || '  (SID=' || to_char(strSID) ||')))' || chr(39);
      else
         strsql :=' Create public database link ' || link_name ||' using ' || chr(39) || strNetService || chr(39);
      end if;      -- dbms_output.put_line('SQL='|| strsql);
      execute immediate strsql;
  else
    if (bUseconnstr=0) then
       strsql :=' Create database link ' || link_name
          || ' connect to ' || username || ' identified by ' || password
          || ' using ' || chr(39) || strNetService || chr(39);
    else
       strsql :=' Create database link ' || link_name
             || ' connect to ' || username || ' identified by ' || password
             || ' using ' || chr(39)
             || ' (DESCRIPTION=(ADDRESS=(PROTOCOL=' || strProtocol ||')'
             || ' (HOST=' || strHost || ')'
             || ' (PORT=' || to_char(iPort) ||'))'
             || ' (CONNECT_DATA = '
             || '  (SID=' || to_char(strSID) ||')))' || chr(39);
    end if;    -- dbms_output.put_line('SQL='|| strsql);
    execute immediate strsql;
  end if; Exception
    WHEN no_data_found then
      if (iCheckpoint=1) then
         dbms_output.put_line('Please confirm that current user has the privilege to query view [v$parameter]');
         dbms_output.put_line (SQLERRM);
      else
         dbms_output.put_line (SQLERRM);
      end if;
      --raise_application_error(sqlcode,sqlerrm,true);    WHEN too_many_rows then
      -- dbms_output.put_line ('Query returns more than one row !');
      dbms_output.put_line (SQLERRM);
   WHEN OTHERS then
      if (iCheckpoint = 1) then
         dbms_output.put_line (SQlCODE ||':' || SQLERRM);
      else
        if (iCheckpoint =3) then
           dbms_output.put_line('Please confirm that current user has the privilege: create ' || strPrefix || 'database link');
           dbms_output.put_line (SQlCODE ||':' || SQLERRM);
        else
           dbms_output.put_line(' Error occurs when proceeding this task ...');
           dbms_output.put_line (SQlCODE ||':' || SQLERRM);
        end if;
      end if;end;
/

Answer 4

给你转个全的
可以创建public和private类型的link, 也可以直接根据连接串来
创建(不需要tnsnames.ora文件中有相应的entry) CREATE OR REPLACE PROCEDURE crt_dblink
(link_name in varchar2,
 isPublic in number,
 username in varchar2,
 password in varchar2,
 bUseconnstr in number,
 strNetService in varchar2,
 strProtocol in varchar2,
 strHost in varchar2,
 strSID  in varchar2,
 iPort in number)
is
  iCount number;
  strsql varchar2(255);
  strpara  varchar2(30);
  strdomain  varchar2(64);
  strdblink  varchar2(40);
  strdblink2 varchar2(40);
  strOwner varchar2(30);
  iCheckPoint number(1);
  strPrefix varchar2(10);
  strCurUser varchar2(30);begin
  -- SYS_CONTEXT('USERENV','CURRENT_USER')
  strsql :=  'SELECT SYS_CONTEXT(' || chr(39) || 'USERENV' || chr(39)
          || ',' || chr(39) || 'CURRENT_USER' || chr(39) || ') from dual';
  execute immediate strsql into strCurUser;  iCheckPoint :=1;  if (isPublic =1) then
     strPrefix :='Public';
  else
     strPrefix :='';
  end if;  -- check parameter db_domain
  strpara :='db_domain';
  strsql :=' select value from v$parameter where name = :para';
  execute immediate strsql into strdomain using strpara;  if (strdomain is not null) then
     strdblink :=upper(link_name || '.' || strdomain);
  else
     strdblink :=upper(link_name);
  end if;  -- Modification:
  -- Add check for select statement
  -- (if no records found, it will raise an exception and can't continue next work)  iCheckPoint :=2;  if (isPublic =1) then
      strsql := ' select count(*) from all_db_links where db_link = :dblink and OWNER= '''
             || 'PUBLIC' || ''' and username is null';
      execute immediate strsql into icount using strdblink;      if (iCount>0) then
         strsql :='select db_link from all_db_links where db_link = :dblink and OWNER= '''
               || 'PUBLIC' || ''' and username is null';         execute immediate strsql into strdblink2 using strdblink;         if strdblink = upper(strdblink2) then
           execute immediate 'drop public database link ' || strdblink ;
        end if;
      end if;
  else  -- private dblink
      strsql := 'select count(*) from all_db_links where db_link = :dblink and OWNER = '''
             || strCurUser || ''' and username is not null';
      execute immediate strsql into icount using strdblink;      if (iCount>0) then
         strsql := 'select db_link,owner from all_db_links where db_link = :dblink and OWNER ='''
                || strCuruser || ''' and username is not null';         execute immediate strsql into strdblink2,strOwner using strdblink;         -- Make sure each user can only delete his own dblink
         if strdblink = upper(strdblink2) and (strCurUser = strOwner) then
            execute immediate 'drop database link ' || strOwner || '.' || strdblink;
         end if;
      end if;
  end if ;
  iCheckPoint:=3;
  -- Last step: Create new database link(public or private)
  if (isPublic = 1) then
      if (bUseconnstr=1) then
          strsql :=' Create public database link ' || link_name ||' using ' || chr(39)
                || ' (DESCRIPTION=(ADDRESS=(PROTOCOL=' || strProtocol ||')'
                || ' (HOST=' || strHost || ')'
                || ' (PORT=' || to_char(iPort) ||'))'
                || ' (CONNECT_DATA = '
                || '  (SID=' || to_char(strSID) ||')))' || chr(39);
      else
         strsql :=' Create public database link ' || link_name ||' using ' || chr(39) || strNetService || chr(39);
      end if;      -- dbms_output.put_line('SQL='|| strsql);
      execute immediate strsql;
  else
    if (bUseconnstr=0) then
       strsql :=' Create database link ' || link_name
          || ' connect to ' || username || ' identified by ' || password
          || ' using ' || chr(39) || strNetService || chr(39);
    else
       strsql :=' Create database link ' || link_name
             || ' connect to ' || username || ' identified by ' || password
             || ' using ' || chr(39)
             || ' (DESCRIPTION=(ADDRESS=(PROTOCOL=' || strProtocol ||')'
             || ' (HOST=' || strHost || ')'
             || ' (PORT=' || to_char(iPort) ||'))'
             || ' (CONNECT_DATA = '
             || '  (SID=' || to_char(strSID) ||')))' || chr(39);
    end if;    -- dbms_output.put_line('SQL='|| strsql);
    execute immediate strsql;
  end if; Exception
    WHEN no_data_found then
      if (iCheckpoint=1) then
         dbms_output.put_line('Please confirm that current user has the privilege to query view [v$parameter]');
         dbms_output.put_line (SQLERRM);
      else
         dbms_output.put_line (SQLERRM);
      end if;
      --raise_application_error(sqlcode,sqlerrm,true);    WHEN too_many_rows then
      -- dbms_output.put_line ('Query returns more than one row !');
      dbms_output.put_line (SQLERRM);
   WHEN OTHERS then
      if (iCheckpoint = 1) then
         dbms_output.put_line (SQlCODE ||':' || SQLERRM);
      else
        if (iCheckpoint =3) then
           dbms_output.put_line('Please confirm that current user has the privilege: create ' || strPrefix || 'database link');
           dbms_output.put_line (SQlCODE ||':' || SQLERRM);
        else
           dbms_output.put_line(' Error occurs when proceeding this task ...');
           dbms_output.put_line (SQlCODE ||':' || SQLERRM);
        end if;
      end if;end;
/

Answer 5

谢谢 cenlmmx(学海无涯苦作舟)