By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test_table(id, image) AS
SELECT 1, EMPTY_CLOB() || 'Image1' FROM DUAL UNION ALL
SELECT 1, EMPTY_CLOB() || 'Image2' FROM DUAL UNION ALL
SELECT 1, EMPTY_CLOB() || 'Image3' FROM DUAL UNION ALL
SELECT 1, EMPTY_CLOB() || 'Image4' FROM DUAL;
4 rows affected
DECLARE
v_json_result_clob CLOB;
BEGIN
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
KEY 'id' VALUE ID,
KEY 'blob_data' VALUE IMAGE
RETURNING CLOB
)
RETURNING CLOB
)
INTO v_json_result_clob
FROM TEST_TABLE
WHERE ID = 1 /*apex_application.g_x01*/;
DBMS_OUTPUT.PUT_LINE(v_json_result_clob);
-- ...
END;
/
1 rows affected
dbms_output:
[{"id":1,"blob_data":"Image1"},{"id":1,"blob_data":"Image2"},{"id":1,"blob_data":"Image3"},{"id":1,"blob_data":"Image4"}]