create or replace package body util_pkg as

function move_fk(p_table_name in varchar2, p_old_id in number, p_new_id in number) return number 
as
  lv_return number;
  -- return value = meaning
  --      0       = no problem, just no references to the old_id.
  --     -1       = old_id was invalid
  --     -2       = new_id was invalid
  --     -3       = table_name was invalid
  --     -4       = other problem encountered
  lv_sql varchar2(1000);
  lv_pk varchar2(50);
  TYPE cur_typ IS REF CURSOR;
  c_cursor cur_typ;
  lv_table varchar2(50);
  lv_col varchar2(50);
  lv_debug varchar2(1000);
  
  lv_count number := 0;
  
begin
  lv_return := null;

  -- Check the table name is valid
  select count(*) into lv_count from all_tables where table_name = upper(p_table_name);
  if lv_count != 1 then
    -- either no tables or many, either of which is a problem
    lv_return := -3;
  else
    -- valid table name
    -- get the name of the primary key
    SELECT cols.column_name
      into lv_pk
      FROM all_constraints cons, all_cons_columns cols
      WHERE cons.constraint_type = 'P'
      AND cons.constraint_name = cols.constraint_name
      and cols.table_name = upper(p_table_name);
    --insert into temp_debug (debug_date, message) values (sysdate, 'PK column is ' || lv_pk);
    
    -- now check old_id
    execute immediate 'select count(*) from ' || p_table_name || 
      ' where ' || lv_pk || ' = ' || p_old_id
      into lv_count; 
    if lv_count != 1 then
      -- either none or many rows matching old_id
      lv_return := -1;
    else
      -- valid old_id
      -- now check new_id
      execute immediate 'select count(*) from ' || p_table_name  || 
      ' where ' || lv_pk || ' = ' || p_new_id
      into lv_count; 
      if lv_count != 1 then
        -- either none or many rows matching old_id
        lv_return := -2;
      else
        -- valid new_id
        -- now we can do it
        -- first build the query to select the table and fk column names
        lv_sql := 'SELECT cols.table_name, cols.column_name '||
          'FROM all_constraints cons, all_cons_columns cols ' ||
          'WHERE cons.constraint_type = ''R'' ' ||
          'AND cons.constraint_name = cols.constraint_name ' ||
          'AND cons.owner = cols.owner ' ||
          'and cons.r_constraint_name in ' ||
          '(select constraint_name from all_constraints ' ||
            ' where constraint_type = ''P'' ' ||
            ' and table_name = ''' || upper(p_table_name) || ''')';
            

        -- now open the query
        OPEN c_cursor FOR lv_sql;
        -- loop through the results
        LOOP
          FETCH c_cursor INTO lv_table, lv_col;
          EXIT WHEN c_cursor%NOTFOUND;
          -- process row here
          execute immediate 'update ' || lv_table || 
            ' set ' || lv_col || ' = ' || p_new_id ||
            ' where ' || lv_col || ' = ' || p_old_id;
        END LOOP;
        CLOSE c_cursor;

        commit;
        
      end if;
    end if;
  end if;

  return lv_return;
exception
  when others then
    lv_debug := sqlerrm;
    return -4;
end move_fk;

end util_pkg;