Oracle DBLink

ora_dblinks.sql

create or replace
  type sysuser as object (
    user_client_name varchar(100),
    user_version varchar(50),
    user_name varchar(100), 
    user_password varchar(100),
    user_db_host varchar(15)
  );
/

declare
  type sysuser_table is varray(50) of sysuser;
  
  gisa_users sysuser_table := sysuser_table(
    /**/
    sysuser('name', 'version', 'db_username', 'db_password', 'host')
-- , sysuser(...
  );
  
  dbl_name varchar2(255); 
  
begin
  for i in gisa_users.first .. gisa_users.last loop
    
    dbl_name := 'dbl_'||gisa_users(i).user_name||'_'||gisa_users(i).user_version;
    
    commit;
      
    begin
      execute immediate 
        'alter session close database link '||dbl_name;      
    exception
      when others then
        dbms_output.put_line('Não consegui fechar o dblink '||dbl_name||' :/');
    end;
    
    begin
      execute immediate 
        'drop database link '||dbl_name;
    exception
      when others then
        dbms_output.put_line('Não consegui dropar o dblink '||dbl_name||' :(');
    end;
    
    execute immediate
      'create database link '||dbl_name||' connect to '||gisa_users(i).user_name||' identified by '||gisa_users(i).user_password||' using ' || 
        q'#'(description = (address_list = (address = (protocol = tcp) (host = #'|| gisa_users(i).user_db_host ||q'#) (port = 1521))) (connect_data = (server = dedicated) (service_name = orcl)))'#'; -- you can change or parm port... or anything else lol
    
    dbms_output.put_line('Criado o dblink '||dbl_name);
    
  end loop;
end;
/