set serveroutput on;
set verify off;
prompt
prompt This sql script sets the properties for Host:Port:Service location, SQL*Net Connection location, and FTP or General file system location in the OWB Client Repository.  It must be run from user OWBSYS.
prompt
prompt The connection type must be Host:Port:Service, or SQL*Net Connection for Oracle Location, and FTP or General for File System Location. The connection type of a location can not be changed by 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 NETSERVICENAME CHAR PROMPT 'New Net Service Name: '
accept DBVERSION CHAR PROMPT 'New Version: '
accept FILE_PATH CHAR PROMPT 'New File Path: '
accept FTP_HOST CHAR PROMPT 'New FTP Host: '
accept FTP_PATH CHAR PROMPT 'New FTP Path: '
 
 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;
     
    procedure update_sqlnet_loc_properties as
      v_netservice_name varchar2(500);
     begin
       if '&NETSERVICENAME' IS NOT NULL then
         dbms_output.put_line('Updating CMPLocation_NetServiceName = ' || '&NETSERVICENAME');
         updatestringprop('CMPLocation_NetServiceName', '&NETSERVICENAME');
         if SQL%NOTFOUND THEN
           insertstringprop('CMPLocation_NetServiceName', '&NETSERVICENAME');
         END IF;
       else
         if '&HOST' IS NOT NULL AND '&PORT' IS NOT NULL AND '&SERVICE' IS NOT NULL then
             v_netservice_name := '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ' || '&HOST' 
                    || ')(PORT = ' || '&PORT' 
                    || '))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ' || '&SERVICE' || ')))';   
	     	       
	     dbms_output.put_line('Updating CMPLocation_NetServiceName = ' || v_netservice_name);
	     updatestringprop('CMPLocation_NetServiceName', v_netservice_name);
	     if SQL%NOTFOUND THEN
	        insertstringprop('CMPLocation_NetServiceName', v_netservice_name);
             END IF;  	    
         END IF; 
       END IF;
     	 
       if '&NETSERVICENAME' IS NOT NULL then
         dbms_output.put_line('Updating CMPLocation_DataServerName = ' || '&NETSERVICENAME');
         updatestringprop('CMPLocation_DataServerName', '&NETSERVICENAME');
         if SQL%NOTFOUND THEN
           insertstringprop('CMPLocation_DataServerName', '&NETSERVICENAME');
         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;

    procedure update_ftp_loc_properties as
     
     begin
       if '&FTP_HOST' IS NOT NULL then
         dbms_output.put_line('Updating CMPLocation_Host = ' || '&FTP_HOST');
         updatestringprop('CMPLocation_Host', '&FTP_HOST');
         if SQL%NOTFOUND THEN
           insertstringprop('CMPLocation_Host', '&FTP_HOST');
         END IF;
       END IF;  
       
       if '&FTP_PATH' IS NOT NULL then
         dbms_output.put_line('Updating CMPLocation_RootPath = ' || '&FTP_PATH');
         updatestringprop('CMPLocation_RootPath', '&FTP_PATH');
         if SQL%NOTFOUND THEN
           insertstringprop('CMPLocation_RootPath', '&FTP_PATH');
         END IF;
      END IF;

    end;
   
    procedure update_file_loc_properties as
     
     begin       
       if '&FILE_PATH' IS NOT NULL then
         dbms_output.put_line('Updating CMPLocation_RootPath = ' || '&FILE_PATH');
         updatestringprop('CMPLocation_RootPath', '&FILE_PATH');
         if SQL%NOTFOUND THEN
           insertstringprop('CMPLocation_RootPath', '&FILE_PATH');
         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_platform 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;  
            elsif v_connectiontype = 'SQL*NET Connection' then
              dbms_output.put_line('Connection Type = SQL*NET Connection Updating...');
              update_sqlnet_loc_properties;
            elsif v_connectiontype = 'FTP' then
              dbms_output.put_line('Connection Type = FTP Updating...');
              update_ftp_loc_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
            begin
             select value into v_platform from cmpstringpropertyvalue_v where logicalname = 'CMPLocation_Platform' and propertyowner = v_elementid;
             if v_platform = 'FILE' then
               dbms_output.put_line('File Location Updating...');
               update_file_loc_properties;
             elsif v_platform = 'ORACLE' then
               dbms_output.put_line('Connection Type = Default HOST:PORT:SERVICE Updating...');
               update_properties; 
             else
               dbms_output.put_line('Can Not Update Location ' || '&LOCATION_NAME');
             end if;
             EXCEPTION
               WHEN NO_DATA_FOUND THEN
               dbms_output.put_line('Can Not Update Location ' || '&LOCATION_NAME');
            end; 
      end;
     
begin
  updatelocation;
  commit;
end;
/