Hi All,
I have to create a procedure where I have to use Merge clause. However, in using condition, I have to put user_define value (V_LOCALE_CD). This value will check from locales table and then make a match with flex_labels_test table.
There is no particular requirement and I dont have any test data.
My question only how I can use user define value in using condition. Here is the procedure I created as follow.
create or replace PROCEDURE UPSERT_FLEX_LABEL_3 (V_LOCALE_CD IN VARCHAR2,
V_VALUE IN VARCHAR2,
V_FIELDVALUE IN VARCHAR2,
V_FIELDNAME IN VARCHAR2,
V_TABLENAME IN VARCHAR2,
N_FLEX_TYPE IN NUMBER,
V_COLOR IN VARCHAR2 DEFAULT NULL,
N_IMAGE_LIBRARY_ID IN NUMBER DEFAULT NULL,
N_HIERARCHY_ID IN NUMBER DEFAULT 5237260000000000001,
N_IS_INHERITED IN NUMBER DEFAULT 1)
AS
L_COUNT NUMBER;
N_LOCALE_ID NUMBER;
NN_HIERARCHY_ID NUMBER := 5237260000000000001;
NN_IS_INHERITED NUMBER := 1;
INSERTCOUNT NUMBER;
UPDATECOUNT NUMBER;
ERR_CODE NUMBER;
ERR_MSG VARCHAR2(100);
BEGIN
-- SELECT ID INTO N_LOCALE_ID FROM LOCALES WHERE LOCALE_CD = V_LOCALE_CD;
/* IT WILL CHECK DUPLICATE, IF THEY ARE PRESENT THEN IT WILL UPDATE RECORD ELSE IT WILL INSERT NEW RECORD */
BEGIN
Merge into flex_labels_test fl
using (select id from locales where locale_cd = V_LOCALE_CD) L
on (l.id = fl.locale_id)
when matched then
UPDATE SET
HIERARCHY_ID = NVL(N_HIERARCHY_ID,NN_HIERARCHY_ID),
IS_INHERITED = NVL(N_IS_INHERITED,NN_IS_INHERITED),
FLEX_TYPE = N_FLEX_TYPE,
VALUE = V_VALUE,
IMAGE_LIBRARY_ID = N_IMAGE_LIBRARY_ID,
COLOR = V_COLOR
WHERE TABLENAME = V_TABLENAME
AND FIELDNAME = V_FIELDNAME
AND FIELDVALUE = V_FIELDVALUE
when not matched then
INSERT (HIERARCHY_ID, LOCALE_ID, IS_INHERITED,FLEX_TYPE,TABLENAME,FIELDNAME, FIELDVALUE, VALUE, IMAGE_LIBRARY_ID, COLOR)
VALUES (NVL(N_HIERARCHY_ID,NN_HIERARCHY_ID),N_LOCALE_ID, NVL(N_IS_INHERITED,NN_IS_INHERITED),N_FLEX_TYPE,V_TABLENAME, V_FIELDNAME, V_FIELDVALUE ,V_VALUE, N_IMAGE_LIBRARY_ID ,V_COLOR);
UPDATECOUNT := SQL%ROWCOUNT;
EXCEPTION
WHEN OTHERS THEN
ERR_CODE := SQLCODE;
ERR_MSG := SUBSTR(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE('ERRORS ARE'||ERR_CODE||' AND '||ERR_MSG);
END;
COMMIT;
DBMS_OUTPUT.PUT_LINE('RECORD INSERTCOUNT='||NVL(INSERTCOUNT,0));
DBMS_OUTPUT.PUT_LINE('RECORD UPDATECOUNT='||NVL(UPDATECOUNT,0));
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR( -20001, 'LOCALE ID NOT FOUND' );
END;