Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to parse json strings in oracle". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

DECLARE l_json_str VARCHAR2 (4000); l_json json; l_year VARCHAR2 (10); l_month VARCHAR2 (10); TYPE arrays_data_rec IS RECORD (id NUMBER, code VARCHAR2 (30), NAME VARCHAR2 (100); TYPE arrays_data_tab IS TABLE OF arrays_data_rec INDEX BY BINARY_INTEGER; l_arrays_tab arrays_data_tab; l_bodylist json_list -- json list l_arrays_json json;-- used to parse each array information BEGIN l_arrays_tab.delete L_json_str: ='{"year": "2019", "month": "05", "strArray": [{"id": 1, "code": "CODE1", "name": "NAME1"}, {"id": 2, "code": "CODE2", "name": "NAME2"}, {"id": 3, "code": "CODE3" "name": "NAME3"}]}' BEGIN-- converts the string to json format l_json: = json (l_json_str);-- gets the value of json l_year: = json_ext.get_string (l_json, 'year') L_month: = json_ext.get_string (l_json, 'month'); dbms_output.put_line (' year:' | | l_year | |'--month:' | | l_month);-- json array initialization l_arrays_json: = json () -- saves the strArray array in the l_bodylist object l_bodylist: = json_ext.get_json_list (l_json, 'strArray'); IF l_bodylist IS NOT NULL THEN-- circular array that writes the value to the memory table FOR idx IN 1.. L_bodylist.count LOOP l_arrays_json: = json (l_bodylist.get (idx)) -- Note: convert according to the data type of the passed value, otherwise the value l_arrays_tab (idx) .id: = json_ext.get_number (l_arrays_json, 'id') will not be obtained. L_arrays_tab (idx) .code: = json_ext.get_string (l_arrays_json, 'code') L_arrays_tab (idx) .name: = json_ext.get_string (l_arrays_json, 'name'); dbms_output.put_line (l_arrays_tab (idx) .id | |'--'| | l_arrays_tab (idx). Code | |'-'| | l_arrays_tab (idx) .name) END LOOP; END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line ('JSON format conversion exception!'); END;EXCEPTION WHEN fnd_api.g_exc_error THEN dbms_output.put_line ('error:' | | SQLCODE | |' -'| | SQLERRM) WHEN fnd_api.g_exc_unexpected_error THEN dbms_output.put_line ('Unexpected_error.' | | SQLCODE | |' -'| | SQLERRM); WHEN OTHERS THEN dbms_output.put_line ('Other error:' | | SQLCODE | |'--'| SQLERRM); END; 's "how to parse json strings by oracle" ends here. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.