Is it possible to specify in the execution code, which is in the variable CLOB, the values that are in another CLOB (JSON)

Question

JSON comes to me via api. I keep it in a removable clob (v_clob)

{
"devices":{
"id":"d652f632-0835-871b-a140-58701f019000",
"scale_id":"88348A32BD3D149FE055000000000001"
},
"data":{
"external_id":"40023"
},
"data_weight":{
"weight":"20322",
"prevWeight":"1000",
"prevTransaction":"1607680754361",
"transaction":"1607680754361",
"on":"false",
"transactionDataCount":"1",
"stable":"false",
"duration":"12",
"transactionMaxWeight":"2000",
"perimetr":"true",
"driverInCar":"false"
}
}

And I have a table with the clob format. I store some code in this table (in clob format). Here is the code (in this code I look for an entry with an external ID, and then I look for the necessary scripts depending on the conditions):

WITH rowsel as (select OBL_TAREDAT  ,OBL_BRUTTODAT,DIRECTIONID,TARE,BRUTTO   from
                                (select OBL_TAREDAT ,OBL_BRUTTODAT,DIRECTIONID,TARE,BRUTTO from  WAYBILLS 
                                 where EXTERNAL_ID = {{EXTERNAL_ID}} 
                                 and OBL_TTN is null))
SELECT CASE
WHEN rowsel.OBL_BRUTTODAT  is null and rowsel.DIRECTIONID = '1'   THEN 41
WHEN rowsel.OBL_TAREDAT  is null and rowsel.DIRECTIONID = '1'     THEN 21
WHEN rowsel.OBL_TAREDAT  is null and rowsel.DIRECTIONID = '-1'     THEN 62
WHEN rowsel.OBL_BRUTTODAT  is null and rowsel.DIRECTIONID = '-1'    THEN 61
ELSE 0 END
FROM rowsel

I have a question whether I can use the data coming to me through api to specify this data when searching for the record I need in the query. For example, I want to replace "{{EXTERNAL_ID}}" on v_clob.data.external_id???

I know that I can parse this key with a command

v_external_id: = apex_json.get_varchar2 (p_path => 'data.external_id', p_values => tv);

And then make a replacement, and run the code found in the table.

 v_msg_in := REPLACE(v_msg_in,'{{EXTERNAL_ID}}',chr(39)|| v_external_id|| chr(39))

(v_msg_in - clob I save the code to run) But the problem is that this JSON can change, I will always need to change the code. But if I can refer to Jason himself who comes to me, then I can do it through the code in the table (I will use the apex oracle).Through the top of the oracle, the user will be able to edit the query in the form, and will not require editing code in the process.

Answer 1

A query like the one below should give you the value you are looking for. In the example below, I have pasted in the entire example JSON as a string, but you can replace that with your CLOB variable in your code. The JSON_VALUE function can be used to retrieve a single value from a JSON CLOB/BLOB/VARCHAR2 by specifying the path to the value.

WITH
    rowsel
    AS
        (SELECT OBL_TAREDAT,
                OBL_BRUTTODAT,
                DIRECTIONID,
                TARE,
                BRUTTO
           FROM (SELECT OBL_TAREDAT,
                        OBL_BRUTTODAT,
                        DIRECTIONID,
                        TARE,
                        BRUTTO
                   FROM WAYBILLS
                  WHERE EXTERNAL_ID = json_value ('{
"devices":{
"id":"d652f632-0835-871b-a140-58701f019000",
"scale_id":"88348A32BD3D149FE055000000000001"
},
"data":{
"external_id":"40023"
},
"data_weight":{
"weight":"20322",
"prevWeight":"1000",
"prevTransaction":"1607680754361",
"transaction":"1607680754361",
"on":"false",
"transactionDataCount":"1",
"stable":"false",
"duration":"12",
"transactionMaxWeight":"2000",
"perimetr":"true",
"driverInCar":"false"
}
}', '$.data.external_id') AND OBL_TTN IS NULL))
SELECT CASE
           WHEN rowsel.OBL_BRUTTODAT IS NULL AND rowsel.DIRECTIONID = '1' THEN 41
           WHEN rowsel.OBL_TAREDAT IS NULL AND rowsel.DIRECTIONID = '1' THEN 21
           WHEN rowsel.OBL_TAREDAT IS NULL AND rowsel.DIRECTIONID = '-1' THEN 62
           WHEN rowsel.OBL_BRUTTODAT IS NULL AND rowsel.DIRECTIONID = '-1' THEN 61
           ELSE 0
       END
  FROM rowsel