Cacher is the code snippet organizer for pro developers

alter table COM_COLMN_VALUE_MESSAGE disable constraint COM_COLMN_VALUE_MESSAGE_FK2
;
alter table COM_FIELD_VALUE_MESSAGE disable constraint COM_FIELD_VALUE_MESSAGE_FK2
;
alter table COM_BUSINESS_RULES disable constraint COM_BUSINESS_RULES_FK1
;
alter table COM_FORM_FIELDS disable constraint COM_FORM_FIELDS_FK6
;
alter table COM_FORM_ACTIONS disable constraint COM_FORM_ACTIONS_FK3
;
alter table COM_TABLE_ACTIONS disable constraint COM_TABLE_ACTIONS_FK3
;
alter table COM_TABLE_COLUMNS disable constraint COM_TABLE_COLUMNS_FK4
;
alter table COM_FORMS disable constraint COM_FORMS_FK2
;
alter table COM_FORM_RELATIVE_SERVICES disable constraint COM_FORM_RELATIVE_SERVICES_FK2
;
alter table COM_TABLE_ACTIONS disable constraint COM_TABLE_ACTIONS_FK2
;
alter table COM_LAYOUT_GROUPS disable constraint COM_LAYOUT_GROUPS_FK2
;
alter table COM_FORM_TABLES disable constraint COM_FORM_TABLES_FK6
;
alter table COM_FORM_FIELDS disable constraint COM_FORM_FIELDS_FK5
;
alter table COM_FORM_ACTIONS disable constraint COM_FORM_ACTIONS_FK2
;

-----------------------------

ALTER TABLE com_messages
  ADD new_rid number;
  
-----------------------------
declare
   l_new_rid     com_messages.new_rid%type;
   l_r_rowid   rowid;
   l_rid       com_messages.rid%type;
   v_idx       number := 1;
   v_count     number := 0; 
   v_counter   number := 0;
   v_exists    number := 0;
   cursor cur is 
       select rowid ,new_rid, rid 
       from com_messages 
       order by rid;      
       
   cursor tbl is
        select * from x_table_msg_child ;           
       -- FOR UPDATE OF rid;
BEGIN
   OPEN cur;

   loop
      fetch cur into l_r_rowid, l_new_rid,l_rid;
      exit when cur%notfound;
      update com_messages
             set new_rid = v_idx
           where rowid = l_r_rowid;
           
      if l_rid != v_idx then
      
      --v_counter := v_counter + 1;
     -- EXECUTE IMMEDIATE 'UPDATE '|| tbl.table_name || ' set message_rid = '||v_idx || ' WHERE message_rid = ' l_rid ;
--     EXECUTE IMMEDIATE 'UPDATE '|| 'COM_COLMN_VALUE_MESSAGE' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
--     EXECUTE IMMEDIATE'UPDATE '|| 'COM_FIELD_VALUE_MESSAGE' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
--     EXECUTE IMMEDIATE'UPDATE '|| 'COM_BUSINESS_RULES' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
--     EXECUTE IMMEDIATE'UPDATE '|| 'COM_FORM_FIELDS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
--     EXECUTE IMMEDIATE'UPDATE '|| 'COM_FORM_ACTIONS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
--     EXECUTE IMMEDIATE'UPDATE '|| 'COM_TABLE_ACTIONS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
--     EXECUTE IMMEDIATE'UPDATE '|| 'COM_TABLE_COLUMNS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
--     EXECUTE IMMEDIATE'UPDATE '|| 'COM_FORMS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
--     EXECUTE IMMEDIATE'UPDATE '|| 'COM_FORM_RELATIVE_SERVICES' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
--     EXECUTE IMMEDIATE'UPDATE '|| 'COM_TABLE_ACTIONS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
--     EXECUTE IMMEDIATE'UPDATE '|| 'COM_LAYOUT_GROUPS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid);
--     EXECUTE IMMEDIATE'UPDATE '|| 'COM_FORM_TABLES' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
--     EXECUTE IMMEDIATE('UPDATE '|| 'COM_FORM_FIELDS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid);
--     EXECUTE IMMEDIATE'UPDATE '|| 'COM_FORM_ACTIONS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );  
 --    commit
 
      begin
          select 1 into v_exists from dual where exists (select 1 from COM_COLMN_VALUE_MESSAGE where  message_rid = l_rid);
          if v_exists = 1 then
         --dbms_output.put_line('UPDATE '|| 'COM_COLMN_VALUE_MESSAGE' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
         EXECUTE IMMEDIATE'UPDATE '|| 'COM_COLMN_VALUE_MESSAGE' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid ;
         end if;
     exception  
          when no_data_found then    
          null;       
     end ;
     
           begin
          select 2 into v_exists from dual where exists (select 1 from COM_FIELD_VALUE_MESSAGE where  message_rid = l_rid);
          if v_exists = 2 then
         --dbms_output.put_line('UPDATE '|| 'COM_FIELD_VALUE_MESSAGE' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
         EXECUTE IMMEDIATE'UPDATE '|| 'COM_FIELD_VALUE_MESSAGE' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid ;
         end if;
     exception  
          when no_data_found then    
          null;       
     end ;
     
           begin
          select 3 into v_exists from dual where exists (select 1 from COM_BUSINESS_RULES where  message_rid = l_rid);
          if v_exists = 3 then
         --dbms_output.put_line('UPDATE '|| 'COM_BUSINESS_RULES' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
         EXECUTE IMMEDIATE'UPDATE '|| 'COM_BUSINESS_RULES' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid ;
         end if;
     exception  
          when no_data_found then    
          null;       
     end ;
     
      begin
          select 4 into v_exists from dual where exists (select 1 from COM_FORM_FIELDS where  message_rid = l_rid);
          if v_exists = 4 then
         --dbms_output.put_line('UPDATE '|| 'COM_FORM_FIELDS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
         EXECUTE IMMEDIATE'UPDATE '|| 'COM_FORM_FIELDS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid ;
         end if;
     exception  
          when no_data_found then    
          null;       
     end ;
     
     begin
          select 4 into v_exists from dual where exists (select 1 from COM_FORM_FIELDS where  help_message_rid = l_rid);
          if v_exists = 4 then
         dbms_output.put_line('UPDATE '|| 'COM_FORM_FIELDS' || ' set help_message_rid = '||v_idx || ' WHERE help_message_rid = ' || l_rid );
         EXECUTE IMMEDIATE'UPDATE '|| 'COM_FORM_FIELDS' || ' set help_message_rid = '||v_idx || ' WHERE help_message_rid = ' || l_rid ;
         end if;
     exception  
          when no_data_found then    
          null;       
     end ;

     
     
           begin
          select 5 into v_exists from dual where exists (select 1 from COM_FORM_ACTIONS where  message_rid = l_rid);
          if v_exists = 5 then
         --dbms_output.put_line('UPDATE '|| 'COM_FORM_ACTIONS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
        EXECUTE IMMEDIATE'UPDATE '|| 'COM_FORM_ACTIONS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid ;
         end if;
     exception  
          when no_data_found then    
          null;       
     end ;
     
      begin
          select 6 into v_exists from dual where exists (select 1 from COM_TABLE_ACTIONS where  message_rid = l_rid);
          if v_exists = 6 then
         --dbms_output.put_line('UPDATE '|| 'COM_TABLE_ACTIONS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
         EXECUTE IMMEDIATE'UPDATE '|| 'COM_TABLE_ACTIONS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid ;
         end if;
     exception  
          when no_data_found then    
          null;       
     end ;
     
      begin
          select 6 into v_exists from dual where exists (select 1 from COM_TABLE_ACTIONS where  confirm_message_rid = l_rid);
          if v_exists = 6 then
         dbms_output.put_line('UPDATE '|| 'COM_TABLE_ACTIONS' || ' set confirm_message_rid  = '||v_idx || ' WHERE confirm_message_rid = ' || l_rid );
         EXECUTE IMMEDIATE'UPDATE '|| 'COM_TABLE_ACTIONS' || ' set confirm_message_rid = '||v_idx || ' WHERE confirm_message_rid = ' || l_rid ;
         end if;
     exception  
          when no_data_found then    
          null;       
     end ;     
     
           begin
          select 7 into v_exists from dual where exists (select 1 from COM_TABLE_COLUMNS where  message_rid = l_rid);
          if v_exists = 7 then
         --dbms_output.put_line('UPDATE '|| 'COM_TABLE_COLUMNS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
         EXECUTE IMMEDIATE'UPDATE '|| 'COM_TABLE_COLUMNS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid ;
         end if;
     exception  
          when no_data_found then    
          null;       
     end ;
     
           begin
          select 8 into v_exists from dual where exists (select 1 from COM_FORMS where  message_rid = l_rid);
          if v_exists = 8 then
        --dbms_output.put_line('UPDATE '|| 'COM_FORMS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
         EXECUTE IMMEDIATE'UPDATE '|| 'COM_FORMS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid ;
         end if;
     exception  
          when no_data_found then    
          null;       
     end ;
     
           begin
          select 9 into v_exists from dual where exists (select 1 from COM_FORM_RELATIVE_SERVICES where  message_rid = l_rid);
          if v_exists = 9 then
         --dbms_output.put_line('UPDATE '|| 'COM_FORM_RELATIVE_SERVICES' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
         EXECUTE IMMEDIATE'UPDATE '|| 'COM_FORM_RELATIVE_SERVICES' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid ;
         end if;
     exception  
          when no_data_found then    
          null;       
     end ;
     
           begin
          select 10 into v_exists from dual where exists (select 1 from COM_LAYOUT_GROUPS where  message_rid = l_rid);
          if v_exists = 10 then
        --dbms_output.put_line('UPDATE '|| 'COM_LAYOUT_GROUPS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
         EXECUTE IMMEDIATE'UPDATE '|| 'COM_LAYOUT_GROUPS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid ;
         end if;
     exception  
          when no_data_found then    
          null;       
     end ;
     
      begin
          select 11 into v_exists from dual where exists (select 1 from COM_FORM_TABLES where  message_rid = l_rid);
          if v_exists = 11 then
         --dbms_output.put_line('UPDATE '|| 'COM_FORM_TABLES' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
         EXECUTE IMMEDIATE'UPDATE '|| 'COM_FORM_TABLES' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid ;
         end if;
     exception  
          when no_data_found then    
          null;       
     end ;
     
      end if;           
      --dbms_output.put_line(v_idx);  
      v_idx:= v_idx +1;
    commit;     
   end loop;
   dbms_output.put_line(v_count);
   commit;
END;
/

  
-----------------------------

declare 
cursor cur_move is select * from com_messages where rid != new_rid order by rid; 
begin
for i in cur_move
loop
update  com_messages set rid = i.new_rid where rid = i.rid;
--dbms_output.put_line(i.new_rid || ' ' || i.rid );
commit;
end loop;
end;  

-------------------------------

ALTER TABLE com_messages
  drop COLUMN  new_rid;
  
-------------------------------  
  
alter table COM_COLMN_VALUE_MESSAGE enable constraint COM_COLMN_VALUE_MESSAGE_FK2
;
alter table COM_FIELD_VALUE_MESSAGE enable constraint COM_FIELD_VALUE_MESSAGE_FK2
;
alter table COM_BUSINESS_RULES enable constraint COM_BUSINESS_RULES_FK1
;
alter table COM_FORM_FIELDS enable constraint COM_FORM_FIELDS_FK6
;
alter table COM_FORM_ACTIONS enable constraint COM_FORM_ACTIONS_FK3
;
alter table COM_TABLE_ACTIONS enable constraint COM_TABLE_ACTIONS_FK3
;
alter table COM_TABLE_COLUMNS enable constraint COM_TABLE_COLUMNS_FK4
;
alter table COM_FORMS enable constraint COM_FORMS_FK2
;
alter table COM_FORM_RELATIVE_SERVICES enable constraint COM_FORM_RELATIVE_SERVICES_FK2
;
alter table COM_TABLE_ACTIONS enable constraint COM_TABLE_ACTIONS_FK2
;
alter table COM_LAYOUT_GROUPS enable constraint COM_LAYOUT_GROUPS_FK2
;
alter table COM_FORM_TABLES enable constraint COM_FORM_TABLES_FK6
;
alter table COM_FORM_FIELDS enable constraint COM_FORM_FIELDS_FK5
;
alter table COM_FORM_ACTIONS enable constraint COM_FORM_ACTIONS_FK2
;

declare
   l_new_rid     com_messages.new_rid%type;
   l_r_rowid   rowid;
   l_rid       com_messages.rid%type;
   v_idx       number := 1;
   v_count     number := 0; 
   v_counter   number := 0;
   cursor cur is 
       select rowid ,new_rid, rid 
       from com_messages 
       order by rid;      
       
   cursor tbl is
        select * from x_table_msg_child ;           
       -- FOR UPDATE OF rid;
BEGIN
   OPEN cur;

   loop
      fetch cur into l_r_rowid, l_new_rid,l_rid;
      exit when cur%notfound;
      update com_messages
             set new_rid = v_idx
           where rowid = l_r_rowid;
           
      if l_rid != v_idx then
      v_count := v_count + 1;
      --v_counter := v_counter + 1;
     -- EXECUTE IMMEDIATE 'UPDATE '|| tbl.table_name || ' set message_rid = '||v_idx || ' WHERE message_rid = ' l_rid ;
     dbms_output.put_line('UPDATE '|| 'COM_COLMN_VALUE_MESSAGE' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
     dbms_output.put_line('UPDATE '|| 'COM_FIELD_VALUE_MESSAGE' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
     dbms_output.put_line('UPDATE '|| 'COM_BUSINESS_RULES' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
     dbms_output.put_line('UPDATE '|| 'COM_FORM_FIELDS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
     dbms_output.put_line('UPDATE '|| 'COM_FORM_ACTIONS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
     dbms_output.put_line('UPDATE '|| 'COM_TABLE_ACTIONS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
     dbms_output.put_line('UPDATE '|| 'COM_TABLE_COLUMNS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
     dbms_output.put_line('UPDATE '|| 'COM_FORMS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
     dbms_output.put_line('UPDATE '|| 'COM_FORM_RELATIVE_SERVICES' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
     dbms_output.put_line('UPDATE '|| 'COM_TABLE_ACTIONS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
     dbms_output.put_line('UPDATE '|| 'COM_LAYOUT_GROUPS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid);
     dbms_output.put_line('UPDATE '|| 'COM_FORM_TABLES' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
     dbms_output.put_line('UPDATE '|| 'COM_FORM_FIELDS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid);
     dbms_output.put_line('UPDATE '|| 'COM_FORM_ACTIONS' || ' set message_rid = '||v_idx || ' WHERE message_rid = ' || l_rid );
     
      end if;           
      --dbms_output.put_line(v_idx);  
      v_idx:= v_idx +1;
         
   end loop;
   dbms_output.put_line(v_count);
   commit;
END;
/