set serveroutput on;
set verify off;
prompt
prompt This sql script sets the host:port:service parameters for a location
prompt in the OWB Client Repository.  
prompt
prompt The connection type must be host:port:service and cannot be changed
prompt using this script.
prompt
prompt The location may be registered or unregistered.
prompt

accept LOCATION_NAME CHAR PROMPT 'Enter Location 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('Inserting' || p_name); 
       insert into cmpstringpropertyvalue_v(
              CLASSNAME,CREATEDBY,CREATIONTIMESTAMP, CUSTOMERDELETABLE,CUSTOMERRENAMABLE,
              DISABLED, EDITABLE,ELEMENTID,FROZEN,GENERATED,IMPORTED,
              LOGICALNAME,METADATASIGNATURE,NOTM,PERSISTENT,SEEDED,
              UOID,UPDATEDBY,UPDATETIMESTAMP,POSITION,PROPERTYOWNER,
              FIRSTCLASSOBJECT,VALUE)
       values ('CMPStringPropertyValue', USER, SYSDATE, '1', '0', 
              '0','1', v_seq, '0', '0', '0', 
              p_name, '9999999999', '0', '1', '0',
              v_uoid, USER, SYSDATE, '0', v_elementid, 
              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;
          
    end;
     
    function getlocation return boolean as
      v_loctype varchar2(40);
      v_loctypeversion varchar2(40);
      begin
      select loctype, elementid into 
        v_loctype, v_elementid from CMPLogicalLocation_v where name = '&LOCATION_NAME';
      dbms_output.put_line('Update location properties for ' || '&LOCATION_NAME');
      dbms_output.put_line('Location Type =  ' || v_loctype);
      return true;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          dbms_output.put_line('Location ' || '&LOCATION_NAME' ||  ' not found');
          return false;
      end;

    procedure updatelocation as
      v_connectiontype varchar2(255);
      v_found boolean;
      begin
    
        v_found := getLocation;
        if v_found = true then
          dbms_output.put_line('Location ' || '&LOCATION_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;         
        
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Connection Type = Default HOST:PORT:SERVICE Updating...');
            update_host_port_service;  
      end;
     
begin
  updatelocation;
  commit;
end;
/