Why this: with j(jval) AS ( select JSON_SERIALIZE('{"contract" :{"contract-type" : "P", "contract-id" : 123451, "proposal-no" : "123456", "serial-no" : "serialasd922", "product-code" : "s093", "contract-sign-dt" : "2016-12-01T00:00:00", "contract-start-dt" : "2016-12-09T00:00:00" } }' pretty) from dual ) SELECT json_transform(jval, insert '$.contract[*].x' =…
This answer is for the simple JSON type. PLSQL looping through JSON object I have a complex JSON type how do i loop thru a data like this. Is there any oracle function which traverses this complex type? [[{"PONumber":1,"ItemNumber":1,"Part":{"Description":"Tora! Tora!…
I'm currently struggling to import my API-Response to my Oracle table. I'm using the JSON_TABLE() but this function is not working as expected. I have a JSON value like this: { "CAR-1": [ { "state": "waiting", "timestamp": 1720476000 } ],…
I have a spring boot application running as docker container on my local machine. It is connecting to Oracle database which is there on our company datacenter. I am able to connect to database directly from my local machine using…
I am trying to replicate a function from Oracle that has the following piece of code OPEN pcursor FOR v_sql; loop fetch pcursor into out_rec.XYZ ; exit when pcursor%NOTFOUND; INSERT INTO tab1 VALUES (out_rec.XYZ); COMMIT; pipe row(out_rec); end loop; EXCEPTION…
I am in the process of migrating from Oracle to Postgres with Ora2pg but I find this problem that the signatures on the Postgres side do not exist can you help me find the right solution ? What will be…
I hope I'm in the correct forum. I desperately try to create an docker image with an oracle database that runs on my raspberry pi. There are some resources in the web with an step by step guide how one…
{ "general": { "product_key": "501088", "group_subtype_id": 1, "group_subtype_name": "Wheel Speed Sensor", "variant_id": 6, "variant_name": "DF22", "rb_customer_id": 287383 }, "partnumbers": [ { "partnumber": "F04FD009BD", "pn_type": "Series OEM", "mat_status": "00 - planned", "properties": [ {"property_id":4,"property_name":"ASIC P/N","value_id":38,"value":"8905502648"}, {"property_id":5,"property_name":"ASIC type","value_id":56,"value":"TLE4942"}, {"property_id":6,"property_name":"Axle","value_id":62,"value":"Front / Rear Right"},…
``` select JT.* FROM JSON_TABLE (json_sample) '$.general[*]' COLUMNS ( product_key NUMBER PATH '$.product_key', group_subtype_id NUMBER PATH '$.group_subtype_id', group_subtype_name VARCHAR2 ( 100 ) PATH '$.group_subtype_name', variant_id NUMBER PATH '$.variant_id', variant_name VARCHAR2 ( 100 ) PATH '$.variant_name', rb_customer_id NUMBER PATH '$.rb_customer_id', NESTED…
I'm writing a function in oracle 19c, which should, on given json_object_t that can have any structure, loop through all the keys, and if key is one of pre-specified, mask the value with asterisk. Those values would be card numbers.…