-1
PROCEDURE INSERTORUPDATE_BILLINGACC(
  IN_CA_NUMBER    IN       VARCHAR2,  
  IN_BILL_CUST_ID        IN       VARCHAR2,
  OUT_RETURN_STATUS             OUT      VARCHAR2,
  OUT_RETURN_CODE               OUT      NUMBER,
  OUT_RETURN_MESSAGE            OUT      VARCHAR2
) AS
P_NAME VARCHAR2(200) := 'PKG_PAYMENT.INSERTORUPDATE_BILLINGACC';
P_ERR_LOCATION VARCHAR2(500);

BEGIN
OUT_RETURN_STATUS := 'SUCCESS';
    MERGE INTO SOME_BILLING_ACCOUNTS ABA USING DUAL ON (CANUMBER = IN_CA_NUMBER)
         WHEN NOT MATCHED THEN INSERT (CANUMBER, BILL_SYS_PREPARED, BILL_CUST_ID) 
           VALUES(IN_CA_NUMBER, 'O', IN_BILL_CUST_ID)
         WHEN MATCHED THEN UPDATE SET 
           CANUMBER = IN_CA_NUMBER, BILL_SYS_PREPARED = 'X', BILL_CUST_ID = IN_BILL_CUST_ID;
            COMMIT;
    OUT_RETURN_CODE := SQLCODE;  
    OUT_RETURN_MESSAGE := SQLERRM;
 
    EXCEPTION
       WHEN OTHERS THEN
        IF $$PLSQL_UNIT IS NOT NULL THEN
                        P_ERR_LOCATION := 'ERROR IN ' || $$PLSQL_UNIT || ' AT LINE ' || $$PLSQL_LINE;
        ELSE
                        P_ERR_LOCATION := 'ERROR IN ' || P_NAME;
        END IF;
        OUT_RETURN_STATUS := 'FAILURE';
        OUT_RETURN_CODE := SQLCODE;
        OUT_RETURN_MESSAGE :=  P_ERR_LOCATION || ' -> ' || SQLERRM;
        ROLLBACK;

END INSERTORUPDATE_BILLINGACC;
Radagast81
  • 2,921
  • 1
  • 7
  • 21
Xzone
  • 1
  • 1

1 Answers1

0

The error tells you already what is going wrong: You can not update a column in a MERGE-statement, that you used in the ON-part.

There is no reason to do the update CANUMBER = IN_CA_NUMBER cause you already made sure that this is the case in the ON-part, so simply just remove that part of the UPDATE-part of the MERGE-statement.

Radagast81
  • 2,921
  • 1
  • 7
  • 21