PLSQL Gurus:
DB Version: 12.1.0.2 hence I can't use JSON new features to directly update the attributes and I need to grep for each attribute via REGEXP and then update them targeted.
I need to figure out a way to update a JSON column in a single update rather than updating via a function call in a loop. As per the code below, the performance is very bad as it is doing row by row operations.
I was able to optimize the update for one JSON attribute via a merge statement but using my method I will have to run the update 6 times each for updating all the 6 json attributes "rType", "qualType", "intMethod","sId","TaxIdentifier","clType".
Example of update via Merge:
MERGE INTO TAB1 TGT
USING (
select
CASE WHEN LENGTH(REGEXP_SUBSTR(json_col, '"qualType":.*?,')) = 0 AND LENGTH(qualType) > 0
THEN REGEXP_REPLACE(json_col,'\A{','{"qualType":"'||qualType|| '",')
ELSE json_col END
AS json_col,
B.COL_CD
TAB1_ID
FROM
TAB2 A,
TAB3 B
where
A.CREATION_PERIOD < 201903 and A.ID = 1000
and A.COL_CD = B.COL_CD AND
B.COL2 = 3
) STG
ON (STG.TAB1_ID = tgt.TAB1_ID
)
WHEN MATCHED THEN UPDATE
SET TGT.json_col = stg.json_col
;
-- Similarly I will need to write the merge statement 5 more times to set all the target attributes correctly.
Current Code that take very long to run via a PLSQL Function Call:
create or replace FUNCTION foo_REPLACE (
ORIG_X IN CLOB,
rType IN VARCHAR2,
qualType IN VARCHAR2,
intMethod IN VARCHAR2,
sId IN VARCHAR2,
TId IN VARCHAR2,
clType IN VARCHAR2
)
RETURN CLOB
IS
NEW_X CLOB;
BEGIN
NEW_X := ORIG_JSON;
NEW_X := CASE WHEN LENGTH(REGEXP_SUBSTR(NEW_X, '"rType":.*?,')) = 0 AND LENGTH(rType) > 0
THEN REGEXP_REPLACE(NEW_X,'\A{','{"rType":"'||rType|| '",')
ELSE NEW_X END;
NEW_X := CASE WHEN LENGTH(REGEXP_SUBSTR(NEW_X, '"qualType":.*?,')) = 0 AND LENGTH(qualType) > 0
THEN REGEXP_REPLACE(NEW_X,'\A{','{"qualType":"'||qualType|| '",')
ELSE NEW_X END;
NEW_X := CASE WHEN LENGTH(REGEXP_SUBSTR(NEW_X, '"intMethod":.*?,')) = 0 AND LENGTH(intMethod) > 0
THEN REGEXP_REPLACE(NEW_X,'\A{','{"intMethod":"'||intMethod|| '",')
ELSE NEW_X END;
NEW_X := CASE WHEN LENGTH(REGEXP_SUBSTR(NEW_X, '"sId":.*?,')) = 0 AND LENGTH(sId) > 0
THEN REGEXP_REPLACE(NEW_X,'\A{','{"sId":"'||sId|| '",')
ELSE NEW_X END;
NEW_X := CASE WHEN LENGTH(REGEXP_SUBSTR(NEW_X, '"TaxIdentifier":.*?,')) = 0 AND LENGTH(TId) > 0
THEN REGEXP_REPLACE(NEW_X,'\A{','{"TaxIdentifier":"'||TId|| '",')
ELSE NEW_X END;
NEW_X := CASE WHEN LENGTH(REGEXP_SUBSTR(NEW_X, '"clType":.*?,')) = 0 AND LENGTH(clType) > 0
THEN REGEXP_REPLACE(NEW_X,'\A{','{"clType":"'||clType|| '",')
ELSE NEW_X END;
RETURN NEW_X;
END;
/
DECLARE
V_ID CHAR(9);
V_STATUS CLOB;
V_ITERATION NUMBER;
CURSOR C1 IS
select col1,
col2,
col3,
col4,
col5,
col6,
col7,
col8
from
TAB1 where col7 is NOT null;
BEGIN
FOR i in C1
LOOP
BEGIN
v_varid := i.col1;
V_STATUS := 'STRT: '||v_varid||' AT TS: '||SYSTIMESTAMP;
IF i.col2 = '3'
THEN
UPDATE TAB2 SET json_col = foo_REPLACE(json_col, i.col3, i.col4, i.col5, i.col6, i.col7, i.col8)
WHERE col1 = v_varid and CREATION_PERIOD < 201903 and ID = 1000;
END IF;
V_STATUS := V_STATUS || ' Number of rows updated: '|| sql%rowcount ||', Ending timestamp: '|| SYSTIMESTAMP;
UPDATE TAB1 SET STATUS =V_STATUS, UPDATED_TIME = SYSTIMESTAMP WHERE col1 = v_varid;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
V_STATUS:='i:'||v_varid||'FAILED-SQLERRM:' || SQLERRM || ', SQLCODE' || SQLCODE || V_STATUS;
ROLLBACK;
UPDATE TAB1 SET STATUS =V_STATUS, UPDATED_TIME = SYSTIMESTAMP WHERE col1 = v_varid;
COMMIT;
END;
END LOOP;
END;
/
Can you please suggest if it is possible to write this string manipulation via a single MERGE statement which does make changes to all the 6 attributes of a JSON doc in one update?
Thanks