set serveroutput on;
set verify off;
prompt
prompt This sql script sets the host:port:service and version parameters for a 
prompt location 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 location may be registered or unregistered.
prompt
prompt Restart OWB client in order to see the new version in the UI.
prompt 

accept WORKSPACE_NAME CHAR PROMPT 'Enter Workspace Name: '
accept USER_NAME CHAR PROMPT 'Enter Workspace User Name: '
accept LOCATION_NAME CHAR PROMPT 'Enter Location Name: '
accept HOST CHAR PROMPT 'New Host: ' 
accept PORT CHAR PROMPT 'New Port: '
accept SERVICE CHAR PROMPT 'New Service Name: '
accept DBVERSION CHAR PROMPT 'New Version: '
 
 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,
              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_properties as
     
     begin
       if '&HOST' IS NOT NULL then
         dbms_output.put_line('Updating CMPLocation_Host = ' || '&HOST');
         updatestringprop('CMPLocation_Host', '&HOST');
         if SQL%NOTFOUND THEN
           insertstringprop('CMPLocation_Host', '&HOST');
         END IF;
       END IF;  
       
       if '&PORT' IS NOT NULL then
         dbms_output.put_line('Updating CMPLocation_Port = ' || '&PORT');
         updatestringprop('CMPLocation_Port', '&PORT');
         if SQL%NOTFOUND THEN
           insertstringprop('CMPLocation_Port', '&PORT');
         END IF;
       END IF;  

       if '&SERVICE' IS NOT NULL then
         dbms_output.put_line('Updating CMPLocation_ServiceName = ' || '&SERVICE');
         updatestringprop('CMPLocation_ServiceName', '&SERVICE');
         if SQL%NOTFOUND THEN
           insertstringprop('CMPLocation_ServiceName', '&SERVICE');
         END IF;
       END IF;
       
       if '&DBVERSION' IS NOT NULL then
         dbms_output.put_line('Updating CMPLocation_Version = ' || '&DBVERSION');
         updatestringprop('CMPLocation_Version', '&DBVERSION');
         if SQL%NOTFOUND THEN
           insertstringprop('CMPLocation_Version', '&DBVERSION');
         END IF;
      END IF;
      
    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 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 = UPPER('&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;
      v_workspace boolean;
      begin
        v_workspace := getworkspace;
        if v_workspace = true then       
          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_properties;  
            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_properties;  
      end;
     
begin
  updatelocation;
  commit;
end;
/