oracle - Oracle procedure not updating table

Question

I am running a simple procedure but with commit, but it does not work.

No SQLERROR is given.

PROCEDURE change_status (id NUMBER, status number) IS 
BEGIN
  DBMS_OUTPUT.PUT_LINE ('CHANGING STATUS TO: '|| status || ' id '|| id);
  update STATISTICS_HANDLER sh set sh.status = status where sh.id = id;
  DBMS_OUTPUT.PUT_LINE ('CHANGING STATUS TO: AFTER UPDATE '|| status || ' id '|| id);
commit;
EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE ('INSIDE change_status EXCEPTION ' || SQLERRM);
END;

What am I doing wrong?

BTW, changing sh.status = status to sh.status = 8 (for example). Works great.

Thanks in Advance - I am breaking my balls for 3 hours now.

Tried the following:

EXECUTE PKG_STATISTICS.change_status(1223,5);

and

BEGIN
  PKG_STATISTICS.change_status(1223,5);
END;

and there is a line in STATISTICS_HANDLER with id 1223.

SOLUTION: Changing variable name from status to something else (not a column name) did the work.

标签： oraclestored-proceduresplsql

Answer 1

我相信 Oracle 无法正确执行更新查询，因为您使用的过程参数与列名相同。

PROCEDURE change_status (in_id NUMBER, in_status number) IS 
BEGIN
  DBMS_OUTPUT.PUT_LINE ('CHANGING STATUS TO: '|| in_status || ' in_id '|| in_id);
  update STATISTICS_HANDLER sh set sh.status = in_status  where sh.id = in_id;
  DBMS_OUTPUT.PUT_LINE ('CHANGING STATUS TO: AFTER UPDATE '|| in_status || ' in_id '|| in_id);
commit;
EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE ('INSIDE change_status EXCEPTION ' || SQLERRM);
END;