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;
If you want a query which returns rows in column containing NULL or empty space then the using NULL alone in where clause will not return empty space rows. So the query should be altered as below
Actual Query
SELECT COUNT (*) FROM TABLE WHERE COL_NAME IS NULL;
Altered Query
SELECT COUNT (*)
FROM TABLE
WHERE trim(COL_NAME) IS NULL
(or)
Altered Query
SELECT COUNT (*)
FROM TABLE
WHERE COL_NAME IS NULL OR
LENGTH(TRIM (COL_NAME)) = 0