FOR cur IN (SELECT  process_Id 
              FROM  tbl_process)
LOOP
  SELECT (MAX(TO_NUMBER(substr(p.old_id, instr(p.old_id, '_', -1) + 1))))
    INTO new_id
    FROM tbl_process p
   WHERE process_Id = cur.process_Id;

  IF new_id IS NULL THEN
    new_id := cur.process_Id || '_1';
  ELSE
    new_id := new_id + 1;
    new_id := cur.process_Id || '_' || new_id;
  END IF;
END LOOP;

Comments are closed.