set serveroutput on;
set verify off;
prompt
prompt This sql script sets the host:port:service parameters for a control center
prompt in the OWB Client Repository.  It must be run from user OWBSYS.
prompt
prompt The connection type must be host:port:service and cannot be changed
prompt using this script.
prompt
prompt The default control center (DEFAULT_CONTROL_CENTER) cannot be changed
prompt using this script.
prompt
prompt

accept WORKSPACE_NAME CHAR PROMPT 'Enter Workspace Name: '
accept USER_NAME CHAR PROMPT 'Enter Workspace User Name: '
accept CONTROL_CENTER_NAME CHAR PROMPT 'Enter Control Center Name: '
accept HOST CHAR PROMPT 'Host: '
accept PORT CHAR PROMPT 'Port: '
accept SERVICE CHAR PROMPT 'Service Name: '
 
 declare
 
    v_elementid number(9);

    procedure updatestringprop (p_name varchar2, p_value varchar2) as
 
    begin
      update cmpstringpropertyvalue_v
        set value=p_value
        where logicalname = p_name
        and firstclassobject=v_elementid;
    end;
 
 
    procedure insertstringprop (p_name varchar2, p_value varchar2) as
      v_uoid varchar2(255);
      v_seq number(9);
    begin
 
       select sys_guid(), cwmseq.nextval into v_uoid, v_seq from dual;
       dbms_output.put_line( p_name || ' Not Found Inserting' );
       insert into cmpstringpropertyvalue_v(
              CLASSNAME,CREATEDBY,CREATIONTIMESTAMP, CUSTOMERDELETABLE,CUSTOMERRENAMABLE,
              EDITABLE,ELEMENTID,IMPORTED,
              LOGICALNAME,METADATASIGNATURE,NOTM,PERSISTENT,SEEDED,
              UOID,UPDATEDBY,UPDATETIMESTAMP,POSITION,PROPERTYOWNER,
              COMPLETED, DELETEINOVERRIDE, OVERRIDEATTRIBUTES, OVERRIDECHILDREN, OVERRIDEROLES,
              FIRSTCLASSOBJECT,VALUE)
       values ('CMPStringPropertyValue', USER, SYSDATE, '1', '0', 
              '1', v_seq, '0', 
              p_name, '9999999999', '0', '1', '0',
              v_uoid, USER, SYSDATE, '0', v_elementid, 
              '0', '0', '0', '0', '0',
              v_elementid, p_value);

    end;
 
    procedure update_host_port_service as
 
     begin
       dbms_output.put_line('Updating CMPLocation_Host = ' || '&HOST');
       updatestringprop('CMPLocation_Host', '&HOST');
       if SQL%NOTFOUND THEN
         insertstringprop('CMPLocation_Host', '&HOST');
       END IF;
 
       dbms_output.put_line('Updating CMPLocation_Port = ' || '&PORT');
       updatestringprop('CMPLocation_Port', '&PORT');
       if SQL%NOTFOUND THEN
         insertstringprop('CMPLocation_Port', '&PORT');
       END IF;
 
       dbms_output.put_line('Updating CMPLocation_ServiceName = ' || '&SERVICE');
       updatestringprop('CMPLocation_ServiceName', '&SERVICE');
       if SQL%NOTFOUND THEN
         insertstringprop('CMPLocation_ServiceName', '&SERVICE');
       END IF;

      update CMPRuntimeLocation_v
        set host='&HOST' , port='&PORT'
        where name = '&CONTROL_CENTER_NAME';

 
    end;

    function getworkspace return boolean as 
      v_workspaceid number;
      begin
      select workspace_id into 
        v_workspaceid from workspace_assignment 
        where workspace_name = UPPER('&WORKSPACE_NAME') and user_name = UPPER('&USER_NAME');
      dbms_output.put_line('Select Workspace Id for workspace ' || '&WORKSPACE_NAME' || ' and user ' || '&USER_NAME');
      dbms_output.put_line('Workspace id = ' || v_workspaceid);

      owb_workspace_manager.set_workspace(v_workspaceid);
      return true;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          dbms_output.put_line('Workspace id not found for workspace ' || '&WORKSPACE_NAME' || ' and user ' || '&USER_NAME');
          return false;
        WHEN OTHERS THEN
          dbms_output.put_line('Other Error');
          return false;
      end;   

 
    function getruntimelocation return boolean as
      v_loctype varchar2(40);
      v_loctypeversion varchar2(40);
      begin
      select loctype, elementid into
        v_loctype, v_elementid from CMPRuntimeLocation_v where name = '&CONTROL_CENTER_NAME';
      dbms_output.put_line('Update location properties for ' || '&CONTROL_CENTER_NAME');
      dbms_output.put_line('Location Type =  ' || v_loctype);
      return true;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          dbms_output.put_line('Control Center ' || '&CONTROL_CENTER_NAME' ||  ' not found');
          return false;
      end;

    procedure updateruntimelocation as
      v_connectiontype varchar2(255);
      v_found boolean;
      v_workspace boolean;
      begin
        v_workspace := getworkspace;
        if v_workspace = true then       
          v_found := getruntimelocation;
          if v_found = true then
            dbms_output.put_line('Control Center ' || '&CONTROL_CENTER_NAME' || ' Found');
            select value into v_connectiontype from cmpstringpropertyvalue_v where logicalname = 'CMPLocation_ConnectionType' and propertyowner = v_elementid;
            if v_connectiontype = 'HOST:PORT:SERVICE' then
              dbms_output.put_line('Connection Type = HOST:PORT:SERVICE Updating...');
              update_host_port_service;
            else
              dbms_output.put_line('Connection Type ' || v_connectiontype || ' cannot be updated');
            end if;
          end if;
        end if;
 
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Connection Type = Default HOST:PORT:SERVICE Updating...');
            update_host_port_service;
      end;
 
begin
  updateruntimelocation;
  commit;
end;
/