Creating nested json data from Oracle SQL
I am trying to create a nested json object using Oracle SQL. I am able to create JSON objects where the hierarchy level is predefined. In this case, it is dynamic and not able to find SQL or PLSQL solution…
Return output parameter of oracle stored procedure using PDO and PHP
Hi I'am trying to return a the value of output parameter of oracle stored procedure using PDO. I have a class called Sql and in the constructor I am doing the connection to the database then I am using those…
Can Pl/SQL in Oracle Apex on Centos create a Canonical String from a JSON Format?
I am using Oracle Apex 22.2 and Oracle Database XE 21c on CentOS 7. I want to loop through all the nested elements in a JSON document/CLOB and achieve the Canonical String (shown below). Also, any suggestions on improving my…
How to fix ‘unimplemented or unreasonable conversion requested’ error in JSON?
I am trying to pass a large number of records as a stringify object from client to the server. In the server side there is a function called 'Get_Records' to capture that string object. Then it should parse the string…
Parsing JSON into Oracle type with nested table
Is there a way to make this Oracle query more optimal? Ideally without selecting from the same JSON_TABLE twice. SELECT to_dncl_verification(status_code => t.status, d_valid_from => to_date(t.d_valid_from, 'yyyy-mm-dd'), d_valid_to => to_date(t.d_valid_to, 'yyyy-mm-dd'), category_list => CAST( MULTISET (SELECT tc.category, CASE WHEN tc.allowed…
Bulk insert failing in Oracle Database – Reference to uninitialized collection – PHP
I am working on an application that performs bulk insertion(~12k but it can be more than that) in the Oracle database. Below is the SQL script:- DECLARE errstr VARCHAR2(4000) := ''; errors NUMBER; i NUMBER; er NUMBER; bk1 acct_common.type_tabnumber; bk2…
Removing Parenthesis After A Character – Amazon Web Sevices
I am trying to remove the Parenthesis and its contents after a comma for an Employee Name Field. I cant seem to get the syntax right to remove special characters and numbers AFTER the first comma. Then i would like…
Oracle 12c: Remove JSON object from JSON array
Need to create a function which takes input of CLOB and I need to remove array matching the condition. CREATE OR REPLACE FUNCTION remove_config_node_by_key ( p_in_json IN CLOB, p_in_key IN VARCHAR2 ) RETURN CLOB IS l_ja json_array_t; l_po json_object_t; l_key…
How To Parse JSON DATA IN ORACLE
A table has JSON data in the HUGECLOB column. And I want to parse it. How can I do it? {"errors":{"destination_country_id":["can not be blank"],"dispatch_country_id":["can not be blank"],"vehicle_id":["can not be blank"],"trailer_id":["can not be blank"]}} I tried this; SELECT t.* FROM table,…
JSON_TABLE – Get nodes regardless of path
In a XML like this: <item> <id>1</id> <name>ITEM 1</name> <subitems> <item> <id>2</id> <name>SUBITEM 1</name> <subitems/> </item> <item> <id>3</id> <name>SUBITEM 2</name> <subitems> <item> <id>4</id> <name>SUBITEM 3</name> <subitems/> </item> </subitems> </item> </subitems> </item> There can be several levels of subitems in the…