CODEWITHSUNDEEP
plsql
Nate
Nate

Reputation: 837

pl/sql update procedure doesn't work through parameter

Here is Procedure:

create or replace procedure CHECK_INFO_ACTUAL_AMOUNT_UPD(
ACTUAL_AMOUNT IN CHECK_INFO.ACTUAL_AMOUNT%TYPE,
CHECK_INFO_ID IN CHECK_INFO.ID_CHECK_INFO%TYPE,
TOTAL_ADJUSTMENT IN CHECK_INFO.TOTAL_ADJUSTMENT_ACCRUEMENT%TYPE
) 
is
begin
  UPDATE CHECK_INFO C SET C.ACTUAL_AMOUNT = ACTUAL_AMOUNT,
  C.IS_ACCUMULATION_COMPLITED = 5,
  C.TOTAL_ADJUSTMENT_ACCRUEMENT = TOTAL_ADJUSTMENT 
  WHERE C.ID_CHECK_INFO = CHECK_INFO_ID;
  COMMIT;
    EXCEPTION
    WHEN OTHERS
    THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      ROLLBACK;
end CHECK_INFO_ACTUAL_AMOUNT_UPD;

The problem is that procedure doesn't update ACTUAL_AMOUNT field when i do it through passing parameter, but it update when i assign value inside procedure like

...
UPDATE CHECK_INFO C SET C.ACTUAL_AMOUNT = 111,
...

I tried to debug value to parameter is passed... Whan is the problem???

Upvotes: 1

Views: 1471

Answers (1)

Nir Alfasi
Nir Alfasi

Reputation: 53525

It doesn't work because you use the same parameters names (ACTUAL_AMOUNT, ID_CHECK_INFO).
In order to avoid confusion, you should use some kind of convention for the naming of the procedure parameters, I use p_ as initial.

Try this:

create or replace procedure CHECK_INFO_ACTUAL_AMOUNT_UPD(
P_ACTUAL_AMOUNT IN CHECK_INFO.ACTUAL_AMOUNT%TYPE,
P_CHECK_INFO_ID IN CHECK_INFO.ID_CHECK_INFO%TYPE,
P_TOTAL_ADJUSTMENT IN CHECK_INFO.TOTAL_ADJUSTMENT_ACCRUEMENT%TYPE
) 
is
begin
  UPDATE CHECK_INFO C SET C.ACTUAL_AMOUNT = P_ACTUAL_AMOUNT,
  C.IS_ACCUMULATION_COMPLITED = 5,
  C.TOTAL_ADJUSTMENT_ACCRUEMENT = P_TOTAL_ADJUSTMENT 
  WHERE C.ID_CHECK_INFO = P_CHECK_INFO_ID;
  COMMIT;
    EXCEPTION
    WHEN OTHERS
    THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      ROLLBACK;
end CHECK_INFO_ACTUAL_AMOUNT_UPD;

Upvotes: 1

Related Questions