i created a file browser item, table, procedure and process code the file browser is mainly to load the json file into the apex table and map to display polygons but when i do it gives me the above error ORA-40834: invalid input to JSON parse/load function but my json is in a correct format
there is the table, procedure and process code i used DECLARE
l_file_content CLOB;
BEGIN
-- Get the contents of the file from the file browser item
l_file_content := utl_raw.cast_to_varchar2(apex_util.get_blob_file_src(:P1_JSON_DATA));
-- Call the procedure to load the JSON data into the table
load_json_data(p_json_data => l_file_content);
-- Optionally, you can add a success message to be displayed to the user
apex_application.g_print_success_message := 'JSON data successfully loaded.';
END;
CREATE TABLE json_data (
id VARCHAR2(200),
label VARCHAR2(200),
color VARCHAR2(100),
polygon SDO_GEOMETRY
);
CREATE OR REPLACE PROCEDURE load_json_data(p_json_data CLOB) IS
v_id VARCHAR2(200);
v_label VARCHAR2(200);
v_color VARCHAR2(100);
v_polygon SDO_GEOMETRY;
v_json_object JSON_OBJECT_T;
v_json_array JSON_ARRAY_T;
v_json_element JSON_ELEMENT_T;
BEGIN
v_json_array := JSON_ARRAY_T.PARSE(p_json_data);
FOR v_i IN 0..v_json_array.GET_SIZE - 1 LOOP
BEGIN
v_json_object := TREAT(v_json_array.GET(v_i) AS JSON_OBJECT_T).GET_OBJECT('data');
v_id := v_json_object.GET_STRING('id');
v_label := v_json_object.GET_STRING('label');
v_json_array := v_json_object.GET_ARRAY('color');
v_color := v_json_array.GET_STRING(0) || ',' || v_json_array.GET_STRING(1) || ',' || v_json_array.GET_STRING(2);
v_json_array := v_json_object.GET_ARRAY('allData');
v_json_element := TREAT(v_json_array.GET(0) AS JSON_ARRAY_T).GET(0);
v_json_object := TREAT(v_json_element AS JSON_OBJECT_T).GET_OBJECT('geometry');
v_polygon := SDO_UTIL.FROM_JSON(v_json_object.TO_CLOB);
INSERT INTO json_data(id, label, color, polygon) VALUES(v_id, v_label, v_color, v_polygon);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in JSON data: ' || SQLERRM);
END;
END LOOP;
END;
i attached the json i want to load
json file.docx