begin 

declare
	l_id number;
	l_design_repos_host varchar2(30) := '~reposHost';
	l_runtime_repos_host varchar2(30) := '~reposHost2';
	l_db_link_name varchar2(128);

	procedure exec(p_stmt in varchar2)
	is
	begin
		execute immediate p_stmt;
	exception
		when others
		then
			null;
	end;

	procedure create_db_link_design
	is
	begin
		execute immediate 'create database link default_owb_design_link connect to ~reposSchema identified by ~reposPwd using ' ||
		 '''(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ~reposHost)(PORT = ~reposPort))(CONNECT_DATA = (SERVICE_NAME = ~reposSID)))''';
  	end;

	procedure create_db_link_runtime
	is
	begin
		execute immediate 'create database link default_owb_runtime_link connect to ~reposSchema2 identified by ~reposPwd2 using ' ||
		 '''(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ~reposHost2)(PORT = ~reposPort2))(CONNECT_DATA = (SERVICE_NAME = ~reposSID2)))''';
  	end;

begin

	if wmp_mode.isClient = true then
  		l_id := wmp_api_administration.register_repository(
  			p_name 		=> 'Oracle Warehouse Builder Design', 
    		p_host_name 	=> wmp_portal.CLIENTHOST,
      	p_db_sid 		=> 'sid',
        p_repos_schema 	=> wmp_portal.get_user,
  			p_dblink_name 	=> wmp_portal.CLIENTDBLINK, 
    		p_description 	=> 'The default Oracle Warehouse Builder Design Repository'
        );
		commit;
  else
  	if l_design_repos_host is not null and l_design_repos_host <> chr(126) || 'reposHost' then	
  		exec('drop database link default_owb_design_link');
    	create_db_link_design;

      select db_link into l_db_link_name from user_db_links where lower(db_link) like 'default_owb_design_link%';
		
  		l_id := wmp_api_administration.register_repository(
  			p_name 		=> 'Oracle Warehouse Builder Design', 
    		p_host_name 	=> '~reposHost',
      	p_db_sid 		=> '~reposSID',
        p_repos_schema 	=> '~reposSchema',
  			p_dblink_name 	=> l_db_link_name,
    		p_description 	=> 'The default Oracle Warehouse Builder Design Repository'
        );
  		commit;
    end if;

    if l_runtime_repos_host is not null and l_runtime_repos_host <> chr(126) || 'reposHost2' then	
  		exec('drop database link default_owb_runtime_link');
    	create_db_link_runtime;

      select db_link into l_db_link_name from user_db_links where lower(db_link) like 'default_owb_runtime_link%';

      execute immediate '
        declare
          l_id number;
        begin        
          l_id := owbb_api_administration.register_repository(
            p_name		=> ''Oracle Warehouse Builder Runtime'', 
            p_dblink_name 	=> :1,
        		p_description 	=> ''The default Oracle Warehouse Builder Control Center''
            );
          commit;
        end;
      ' using in l_db_link_name;
    end if;
    
  end if;

end;

end;