CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
ReDevil
ReDevil

Reputation: 77

Oracle procedure not updating table

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.

Upvotes: 0

Views: 445

Answers (1)

Raymond Nijland
Raymond Nijland

Reputation: 11602

I believe Oracle can't correctly execute the update query because you are using the same procedure param with the same name as the column names. 

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;

Upvotes: 2

Related Questions