By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE XXMIC_AP_SUPP_DETAILS_OUT_T (vendor_id, exp_date) AS
SELECT 123, SYSDATE + 1 FROM DUAL UNION ALL
SELECT 456, SYSDATE + 1 FROM DUAL UNION ALL
SELECT 789, SYSDATE + 1 FROM DUAL UNION ALL
SELECT 456, SYSDATE + 2 FROM DUAL;
4 rows affected
DECLARE
L_JSON CLOB;
BEGIN
SELECT JSON_OBJECT(
KEY 'status' VALUE CASE COUNT(*)
WHEN 0
THEN 'ERROR'
ELSE 'SUCCESS'
END,
KEY 'message' VALUE CASE COUNT(*)
WHEN 0
THEN 'No matching records with valid EXP_DATE found'
ELSE 'Successfully retrieved data'
END,
KEY 'data' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
KEY 'vendor_id' VALUE s.vendor_id,
KEY 'exp_date' VALUE s.exp_date
)
)
)
INTO L_JSON
FROM JSON_TABLE(
'[{"vendorid":123},{"vendorid":456}]',
'$[*]'
COLUMNS (
vendorid NUMBER PATH '$.vendorid'
)
) v
INNER JOIN XXMIC_AP_SUPP_DETAILS_OUT_T s
ON (v.vendorid = s.vendor_id)
WHERE SYSDATE < EXP_DATE;
DBMS_OUTPUT.PUT_LINE(L_JSON);
-- Output the JSON response
--OWA_UTIL.MIME_HEADER('application/json', TRUE);
1 rows affected
dbms_output:
{"status":"SUCCESS","message":"Successfully retrieved data","data":[{"vendor_id":123,"exp_date":"2024-10-18T14:48:14"},{"vendor_id":456,"exp_date":"2024-10-18T14:48:14"},{"vendor_id":456,"exp_date":"2024-10-19T14:48:14"}]}