Rem
Rem Copyright (c) 2000 by Oracle Corporation
Rem

----------------------------------------------------------------------------
-- SEED WB_RT_SERVICE_NODES
----------------------------------------------------------------------------

begin

  declare
    l_max_service_version number;
    l_min_service_version number;
    l_service_version number;
    l_service_version_string varchar2(4000);
    l_service_home varchar2(4000);
    l_base_node_id number;
    l_base_instance_number number;
    l_base_port number;
    l_base_service_name varchar2(4000);
    l_service_name_template varchar2(4000);
    l_service_name varchar2(4000);
    l_connect_spec varchar2(4000);
    l_dot_index number;
    l_base_service_name_length number;
    l_base_instance_number_string varchar2(16);
    l_base_instance_number_length number;
  begin

    select owbsys.wb_rt_integer_version_number(owbsys.wb_rt_version) into l_max_service_version from dual;
    select owbsys.wb_rt_integer_version_number(owbsys.wb_rt_minimum_service_version) into l_min_service_version from dual;

    l_base_service_name := '';
    l_base_port := '1521';

    select 
      node_id,
      instance_number
    into
      l_base_node_id,
      l_base_instance_number
    from 
      wb_rt_service_nodes;

    l_base_instance_number_string := to_char(l_base_instance_number);
    l_base_instance_number_length := length(l_base_instance_number_string);
    l_dot_index := instr(l_base_service_name, '.');

    if l_dot_index = 0
    then
      l_base_service_name_length := length(l_base_service_name);
      if substr(l_base_service_name, (l_base_service_name_length - l_base_instance_number_length + 1)) != 
        l_base_instance_number_string
      then
        l_service_name_template := l_base_service_name;
      else
        l_service_name_template := substr(l_base_service_name, 1, 
          (l_base_service_name_length - l_base_instance_number_length)) ||
          '@';
      end if;

    else
      if substr(l_base_service_name, (l_dot_index - l_base_instance_number_length), l_base_instance_number_length) != 
        l_base_instance_number_string
      then
        l_service_name_template := l_base_service_name;
      else
        l_service_name_template := substr(l_base_service_name, 1, 
          (l_dot_index - l_base_instance_number_length - 1)) ||
          '@' || substr(l_base_service_name, l_dot_index);    
      end if;
    end if;

    dbms_output.put_line(l_service_name_template);
    
    for i in (
      select distinct
        owbsys.owbrtps_key_to_instance(key) as instance_no
      from
        owbsys.owbrtps
    )
    loop

      if i.instance_no is not null
      then
        l_service_version := 0;

        for v in (
          select
            owbsys.owbrtps_key_to_version(key) as version_no,
            owbsys.owbrtps_key_to_version_string(key) as version_string,
            value as service_home
          from
            owbsys.owbrtps
          where
            owbsys.owbrtps_key_to_instance(key) = i.instance_no
        )
        loop

          if v.version_no > l_service_version and
            v.version_no >= l_min_service_version and
            v.version_no <= l_max_service_version
          then
            l_service_version := v.version_no;
            l_service_version_string := v.version_string;
            l_service_home := v.service_home;
          end if;

        end loop;

        if l_service_version > 0
        then

          if i.instance_no <> l_base_instance_number
          then

            l_service_name := replace(l_service_name_template, '@', to_char(i.instance_no));
            l_connect_spec := 'localhost:' || l_base_port || ':' || l_service_name;

            insert into wb_rt_service_nodes
            (
              node_id,
              instance_number,
              connect_spec,
              enabled,
              runtime_version,
              server_side_home
            )
            values
            (
              wb_rt_node_seq.nextval,
              i.instance_no,
              l_connect_spec,
              0,
              l_service_version_string,
              l_service_home
            );
          end if;
        end if;
      end if;
    end loop;

    commit;

  end;

end;
/