Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\rtasst\upgrade\owfupgrade

Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\rtasst\upgrade\owfupgrade_dblinks.sql

rem ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
rem UPGRADE Oracle Workflow to access a new Unified Repository
rem 1) Change any database link that pointed to a process deployed via the original
rem    Repository so that it is an anonymous link pointing to the new Repository
rem
rem 1.1)Add any new database links if the OWF install is now remote
rem
rem 2) Change any synonyms so that they reference OWBSYS.WB_RTI_WORKFLOW_UTIL
rem
rem 3) Add item-attributes to the item-types for Workspace Owner and Workspace Name
rem 4) Make activities use WB_RT_WORKFLOW_UTIL_11g
rem 5) Make sure SYS:WS_NAME and SYS:WS_OWNER item attributes have the correct value
rem 6) Change type of WF_PROCESS_ACTIVITIES to be OWB_11G
rem
rem Database links are identified by Process Name
rem
rem NetServiceName links will need to have their NetServiceName defn changed
rem   externally in the appropriate tnsnames.ora file
rem
rem Invoke this file in the OWF account that is to be upgraded
rem Note that the OWF_MGR user may require the privilege 'CREATE DATABASE LINK'
rem
rem listProcesses is in the form
rem      l.db_link like '%Process_Name' [or l.db_link like '%Process_NameN']*
rem
rem listSynonyms is in the form
rem      'Proc1_WB_RTI_WORKFLOW_UTIL' [, 'ProcN_WB_RTI_WORKFLOW_UTIL' ] *
rem
rem listItem_Types is in the form
rem      'ItemType1' [, 'ItemTypeN' ] *
rem
rem listProcessDecls is in the form
rem      v_processes(0) := 'Process_Name';
rem
rem localdatabase is either 0 (means remote db) or 1 (means local db)
rem
rem ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

begin
  declare
    l_host_defn varchar2(512);
    l_dblink_name varchar2(512);
    l_itemtype_name varchar2(128);
    l_localdb number := %localdatabase;
    i number;
    j number;
    l_err number;

type t_processes is table of varchar2(64) index by binary_integer;
    v_processes t_processes;
    l_counter number;
    l_count number;
    l_stmt varchar2(2000);
  begin
       
    for dbl in (
      select 
        l.db_link,
        l.host
      from 
        user_db_links l
      where
        l.host like '(DESCRIPTION%' and
        (%listProcesses)
    )
    loop
  
         begin
            execute immediate 'drop database link "' || dbl.db_link || '"';
         exception
            when others then
              raise_application_error(-20101,'Cannot drop database link ' || dbl.db_link, TRUE);
         end;

i := instr(dbl.host,'HOST=')+4;
         l_host_defn := substr(dbl.host, 0, i) || '%host';
         i := instr(dbl.host,')', i);
         j := instr(dbl.host,'PORT=',i)+5;
         l_host_defn :=  l_host_defn || substr(dbl.host, i, j-i) || '%port';
         i := instr(dbl.host,')', j);
         j := instr(dbl.host,'SERVICE_NAME=',i)+13;
         l_host_defn :=  l_host_defn || substr(dbl.host, i, j-i) || '%serviceName';       
         i := instr(dbl.host,')', j);
         l_host_defn :=  l_host_defn || substr(dbl.host, i);
         l_dblink_name := substr(dbl.db_link, instr(dbl.db_link, '@'));
      
         begin
            if (l_localdb=0) then
              execute immediate 'create database link "%serviceName' || l_dblink_name ||
               '" connect to ' || '%tsuser' || ' identified by "' || '%tspwd' ||
               '"  using ''' || l_host_defn || '''';
            end if;
         exception
            when others then
              raise_application_error(-20102,'Cannot create database link %serviceName' || l_dblink_name, TRUE);
         end;
    end loop;   
  
    for dbl in (
        select
          l.db_link,
          l.host
        from
          user_db_links l
        where
          l.host not like '(DESCRIPTION%' and
          (%listProcesses)
    )
    loop
        begin
           execute immediate 'drop database link "' || dbl.db_link || '"';
        exception
           when others then
              raise_application_error(-20101,'Cannot drop database link ' || dbl.db_link, TRUE);
        end;
                  
        l_dblink_name := substr(dbl.db_link, instr(dbl.db_link, '@'));
      
        begin
            if (l_localdb=0) then
             execute immediate 'create database link "%serviceName' || l_dblink_name ||
                 '" connect to ' || '%tsuser' || ' identified by "' || '%tspwd' ||
                 '" using ''|| dbl.host || ''';
            end if;
        exception
            when others then
              raise_application_error(-20102,'Cannot create database link %serviceName' || l_dblink_name, TRUE);
        end;

end loop;

if (l_localdb=0) then
      %listProcDecls
      l_counter := v_processes.first;
      while l_counter<= v_processes.last loop
        select count(*) into l_count
        from (select l.db_link, l.host from user_db_links l
                            where l.host like '(DESCRIPTION%' and
                            l.db_link like '%WB_LK_' || v_processes(l_counter));
        if (l_count=0) then
           l_stmt :='create database link ' || '%serviceName' || '@' || 'WB_LK_' || v_processes(l_counter);
           l_stmt := l_stmt || ' connect to ' || '%tsuser' || ' identified by "' || '%tspwd' || '" using ';
           l_stmt := l_stmt || '''(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=%host)(PORT=%port)))(CONNECT_DATA=(SERVICE_NAME=%serviceName)))''';
           begin
             execute immediate l_stmt;
           exception
             when others then
               raise_application_error(-20103,'Cannot create database link %serviceName' || '@WB_LK_' || v_processes(l_counter), TRUE);
           end;
        end if;
        l_counter := l_counter+1;
      end loop;
    end if;
    
    for syn in (
      select 
        s.synonym_name
      from 
        user_synonyms s
      where
        s.synonym_name in (%listSynonyms)
    )
    loop
  
      begin
        execute immediate 'drop synonym "' || syn.synonym_name || '"';

if (l_localdb=0) then
           i := instr(syn.synonym_name,'_WB_RTI_WORKFLOW_UTIL');
           l_itemtype_name := substr(syn.synonym_name, 0, i-1);
      
           execute immediate 'create synonym "' || syn.synonym_name ||
                '"  for owbsys.wb_rti_workflow_util@%serviceName@WB_LK_' || l_itemtype_name;
        else
           execute immediate 'create synonym "' || syn.synonym_name ||
                '"  for owbsys.wb_rti_workflow_util';
        end if;

exception 
        when others then
          raise_application_error(-20104,'Cannot create synonym ' || syn.synonym_name, TRUE);
      end;
    end loop;

for itemt in (
      select
        t.name,
        a.max_seq
      from 
        wf_item_types t,
       (select max(sequence) as max_seq, item_type from wf_item_attributes group by item_type) a
      where 
        t.name in (%listItem_Types) and
        a.item_type = t.name and
        t.name not in (select ws.item_type from wf_item_attributes ws where ws.name='SYS:WS_OWNER')
    )
    loop
       begin
         wf_load.upload_item_attribute(
            x_item_type => itemt.name,
            x_name => 'SYS:WS_OWNER',
            x_display_name => 'SYS:ws_owner',
            x_description => '',
            x_sequence => itemt.max_seq,
            x_type => 'VARCHAR2',
            x_protect_level => 20,
            x_custom_level => 0,
            x_subtype => null,
            x_format => null,
            x_default => '%repo_user',
            x_level_error => l_err);
          
        wf_load.upload_item_attribute(
            x_item_type => itemt.name,
            x_name => 'SYS:WS_NAME',
            x_display_name => 'SYS:ws_name',
            x_description => '',
            x_sequence => itemt.max_seq+1,
            x_type => 'VARCHAR2',
            x_protect_level => 20,
            x_custom_level => 0,
            x_subtype => null,
            x_format => null,
            x_default => '%workspaceShortName',
            x_level_error => l_err);
          
         update wf_activities a
           set a.function = 'WB_RT_WORKFLOW_UTIL_11G' || substr(a.function,instr(a.function,'.'))
         where
           a.item_type=itemt.name and
           a.function like 'WB_RT_WORKFLOW_UTIL_%';

exception 
         when others then
            raise_application_error(-20105,'Error updating Item Attributes (' || itemt.name || ')', TRUE);
       end;
     end loop;

for itemt in (
      select
        t.name,
        a.max_seq
      from 
        wf_item_types t,
       (select max(sequence) as max_seq, item_type from wf_item_attributes group by item_type) a,
       wf_item_attributes ia
      where 
        t.name in (%listItem_Types) and
        a.item_type = t.name and
        ia.name = 'SYS:WS_OWNER' and
        ia.item_type = t.name and
        ia.text_default != '%repo_user'
    )
    loop
       begin
         wf_load.upload_item_attribute(
            x_item_type => itemt.name,
            x_name => 'SYS:WS_OWNER',
            x_display_name => 'SYS:ws_owner',
            x_description => '',
            x_sequence => itemt.max_seq,
            x_type => 'VARCHAR2',
            x_protect_level => 20,
            x_custom_level => 0,
            x_subtype => null,
            x_format => null,
            x_default => '%repo_user',
            x_level_error => l_err);
       end;
    end loop;

for itemt in (
      select
        t.name,
        a.max_seq
      from 
        wf_item_types t,
       (select max(sequence) as max_seq, item_type from wf_item_attributes group by item_type) a,
       wf_item_attributes ia
      where 
        t.name in (%listItem_Types) and
        a.item_type = t.name and
        ia.name = 'SYS:WS_NAME' and
        ia.item_type = t.name and
        ia.text_default != '%workspaceShortName'
    )
    loop
       begin
         wf_load.upload_item_attribute(
            x_item_type => itemt.name,
            x_name => 'SYS:WS_NAME',
            x_display_name => 'SYS:ws_name',
            x_description => '',
            x_sequence => itemt.max_seq,
            x_type => 'VARCHAR2',
            x_protect_level => 20,
            x_custom_level => 0,
            x_subtype => null,
            x_format => null,
            x_default => '%workspaceShortName',
            x_level_error => l_err);
       end;
    end loop;

for itemt in (
      select
        t.name
      from 
        wf_item_types t
      where 
        t.name in (%listItem_Types)
    )
    loop
        update wf_process_activities a
          set a.activity_item_type='OWB_11G'
        where
          a.process_item_type = itemt.name and
          a.activity_item_type = 'OWB_10G';
    end loop;

end;
end;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de