I am getting only this output. How can I transform this to get only ? I want to extract ony partial text. The 2 json files are as below. The 2 jsons needs to be compared against each other using key value pairs and get this compared. How to get this done? am getting only this output. How can I transform this to get only ? I want to extract ony partial text. The 2 json files are as below. The 2 jsons needs to be compared against each other using key value pairs and get this compared. How to get this done?

Error starting at line : 2,120 in command - FUNCTION get_unmatched_json_keys1 (v_tc_id in number) Error report - Unknown Command

SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.

Error starting at line : 2,121 in command - RETURN json_key_table PIPELINED AS Error report - Unknown Command

Error starting at line : 2,122 in command - v_key_name VARCHAR2(4000) Error report - Unknown Command

Error starting at line : 2,123 in command - v_json1 CLOB Error report - Unknown Command

Error starting at line : 2,124 in command - v_json2 CLOB Error report - Unknown Command

SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.

Error report - ORA-06550: line 3, column 68: PL/SQL: ORA-00904: "V_TC_ID": invalid identifier ORA-06550: line 3, column 5: PL/SQL: SQL Statement ignored ORA-06550: line 4, column 69: PL/SQL: ORA-00904: "V_TC_ID": invalid identifier ORA-06550: line 4, column 5: PL/SQL: SQL Statement ignored ORA-06550: line 7, column 8: PLS-00201: identifier 'V_JSON1' must be declared ORA-06550: line 7, column 5: PL/SQL: Statement ignored ORA-06550: line 14, column 76: PL/SQL: ORA-00904: "V_JSON1": invalid identifier ORA-06550: line 14, column 9: PL/SQL: SQL Statement ignored ORA-06550: line 30, column 9: PLS-00629: PIPE statement cannot be used in non-pipelined functions 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

  


 FUNCTION get_unmatched_json_keys1 (v_tc_id in number) 
 RETURN json_key_table PIPELINED AS
    v_key_name VARCHAR2(4000);
    v_json1 CLOB;
    v_json2 CLOB;
BEGIN
    -- Fetch JSON data safely
    SELECT prepayment_msg INTO v_json1 FROM xml_poc2 WHERE tc_id = v_tc_id;
    SELECT postpayment_msg INTO v_json2 FROM xml_poc2 WHERE tc_id = v_tc_id;

    -- If either JSON is NULL, return immediately
    IF v_json1 IS NULL OR v_json2 IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('One of the IDs does not exist. Exiting function.');
        RETURN;
    END IF;

    -- Extract keys from JSON1 and JSON2
    FOR rec IN (
        WITH json1 AS ( SELECT DBMS_LOB.SUBSTR(v_json1, DBMS_LOB.GETLENGTH(v_json1), 1) AS json_content FROM dual ),
             json2 AS ( SELECT DBMS_LOB.SUBSTR(v_json2, DBMS_LOB.GETLENGTH(v_json2), 1) AS json_content FROM dual ),
             json1_keys AS (
                 SELECT DISTINCT TRIM(REGEXP_SUBSTR(json1.json_content, '"([^"]+)":', 1, LEVEL, NULL, 1)) AS key_name
                 FROM json1 CONNECT BY LEVEL <= REGEXP_COUNT(json1.json_content, '"([^"]+)":')
             ),
             json2_keys AS (
                 SELECT DISTINCT TRIM(REGEXP_SUBSTR(json2.json_content, '"([^"]+)":', 1, LEVEL, NULL, 1)) AS key_name
                 FROM json2 CONNECT BY LEVEL <= REGEXP_COUNT(json2.json_content, '"([^"]+)":')
             )
        SELECT json2_keys.key_name 
        FROM json2_keys 
        LEFT JOIN json1_keys ON json2_keys.key_name = json1_keys.key_name
        WHERE json1_keys.key_name IS NULL
    )
    LOOP
        PIPE ROW (rec.key_name);
    END LOOP;

    RETURN;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('One of the IDs does not exist in the table.');
        RETURN;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
        RETURN;
END get_unmatched_json_keys1;
/