when i run the procedure i get this error [Err] ORA-24344: success with compilation error missing keyword

Question

CREATE OR REPLACE 
PROCEDURE "UNASSIGN_CUSTOMER_FEATURES" 
(CustomerID_Param IN NUMBER, FeatureID_Param IN NUMBER, WalletID_Param IN NUMBER)
AS
BEGIN

DELETE FROM CUSTOMER_EXTRA_FEATURES WHERE FEATURES_ID = FeatureID_Param 
AND CUSTOMER_ID = CustomerID_Param;

MERGE INTO CUSTOMER_SERVICE_CONFIG c
USING 
            (SELECT BUSINESS_SERVICE_CONFIG.ID from BUSINESS_SERVICE_CONFIG join SERVICE_CONFIG_MAP 
                    ON BUSINESS_SERVICE_CONFIG.Business_SERVICE_TYPE = SERVICE_CONFIG_MAP.Service_type_ID
                    and BUSINESS_SERVICE_CONFIG.ORGANIZATION_ID = WalletID_Param
                    and BUSINESS_SERVICE_CONFIG.BUSINESSSERVICECATEGORY = 0 
                    and SERVICE_CONFIG_MAP.FEATURES_ID = FeatureID_Param ) ids

ON (c.SERVICE_CONFIG_ID = ids.ID and c.CUSTOMER_ID = CustomerID_Param )
WHEN MATCHED THEN 
DELETE WHERE CUSTOMER_ID = CustomerID_Param AND SERVICE_CONFIG_ID = ids.ID;

END;

Answer 1

You are missing an UPDATE statement prior to doing a DELETE

something like

WHEN MATCHED THEN 
  UPDATE SET <some field> with <some value>
  DELETE WHERE CUSTOMER_ID = CustomerID_Param AND SERVICE_CONFIG_ID =ids.ID;
END;

Refer the following url: Oracle sql merge to insert and delete but not update

Hope that helps!

Answer 2

The syntax diagram for the merge statement shows that you the delete clause is an optional part of the update, not standalone:

You're getting the "ORA-00905: missing keyword" error because you don't have an update. You could put in a dummy update, but it looks like you really want to delete all rows that match, with no updates to other rows or inserts of new rows; which would be simpler as a plain delete, something like:

DELETE FROM CUSTOMER_SERVICE_CONFIG c
WHERE CUSTOMER_ID = CustomerID_Param
AND SERVICE_CONFIG_ID IN (
  SELECT BUSINESS_SERVICE_CONFIG.ID from BUSINESS_SERVICE_CONFIG join SERVICE_CONFIG_MAP 
  ON BUSINESS_SERVICE_CONFIG.Business_SERVICE_TYPE = SERVICE_CONFIG_MAP.Service_type_ID
  and BUSINESS_SERVICE_CONFIG.ORGANIZATION_ID = WalletID_Param
  and BUSINESS_SERVICE_CONFIG.BUSINESSSERVICECATEGORY = 0 
  and SERVICE_CONFIG_MAP.FEATURES_ID = FeatureID_Param );

or

DELETE FROM CUSTOMER_SERVICE_CONFIG c
WHERE CUSTOMER_ID = CustomerID_Param
AND EXISTS (
  SELECT null from BUSINESS_SERVICE_CONFIG join SERVICE_CONFIG_MAP 
  ON BUSINESS_SERVICE_CONFIG.Business_SERVICE_TYPE = SERVICE_CONFIG_MAP.Service_type_ID
  and BUSINESS_SERVICE_CONFIG.ORGANIZATION_ID = WalletID_Param
  and BUSINESS_SERVICE_CONFIG.BUSINESSSERVICECATEGORY = 0 
  and SERVICE_CONFIG_MAP.FEATURES_ID = FeatureID_Param
  WHERE BUSINESS_SERVICE_CONFIG.ID = c.SERVICE_CONFIG_ID );