PL/SQL invalid ROWID for update

Question

I try update salary, but get error:

invalid ROWID

Cause: A ROWID was entered incorrectly. ROWIDs must be entered as formatted hexadecimal strings using only numbers and the characters A through F. A typical ROWID format is '000001F8.0001.0006'.

this is my code

DECLARE 
    CURSOR get_sls(mgr NUMBER, dep VARCHAR2) IS 
      SELECT * 
        FROM emp_n_m
       WHERE emp_n_m.mgr = mgr 
        FOR UPDATE OF emp_n_m.sal ;
BEGIN 
  OPEN get_sls(7902, 'SALES');
  if (get_sls%notfound) then
     dbms_output.put_line('incorrect mgr');
  else
    UPDATE emp_n_m
       SET emp_n_m.sal = emp_n_m.sal + 50
     WHERE CURRENT OF get_sls;  
    COMMIT; 
  end if;
  CLOSE get_sls;
END;
/

You just opened the cursor. You haven't fetch anything yet, thus `invalid rowid`. You need to fetch data from it first. Note if you'll do it in a loop, do not commit inside the loop, otherwise you'll get `"fetch out of sequence"` error. — Nick Krasnov, Oct 18 '16 at 11:41

score 1 · Answer 1 · answered Oct 18 '16 at 12:50

this should work:

DECLARE 
    CURSOR get_sls(mgr NUMBER, dep VARCHAR2) IS 
      SELECT * 
        FROM emp_n_m
       WHERE emp_n_m.mgr = mgr 
        FOR UPDATE OF emp_n_m.sal ;
  v_emp get_sls%rowtype;
BEGIN 
  OPEN get_sls(7902, 'SALES');
  FETCH get_sls INTO v_emp;
  if (get_sls%notfound) then
     dbms_output.put_line('incorrect mgr');
  else
    UPDATE emp_n_m
       SET emp_n_m.sal = emp_n_m.sal + 50
     WHERE CURRENT OF get_sls;  
    COMMIT; 
  end if;
  CLOSE get_sls;
END;
/

score 0 · Answer 2 · answered Oct 18 '16 at 21:37

Is the cursor really required in your case? Would it not be efficient to get this done in a DML operation?

All you want to do is to update salary for manager 7902 from dept 'SALES ...

UPDATE emp_n_m SET emp_n_m.sal = emp_n_m.sal + 50 
WHERE emp_n_m.mgr =  7902
AND dept = 'SALES';

COMMIT;

PL/SQL invalid ROWID for update

2 Answers2