[SQL] unbale to get the data from ORDS plsql block if input is an json array of objects

I'm using below PLSQL block within my Oracle ORDS where my current input json body is like below

['123','456']


but i want to change the input json body to below format (unable to decide the logic)

[{
"vendorid":123"
}]


my current working code is like below (for input body ['123','456']), i'm not understanding how to change below to accept the needed json body,

DECLARE
-- JSON Object and Array to hold the final response and data
L_JSON_OBJECT JSON_OBJECT_T := JSON_OBJECT_T();
L_JSON_ARRAY JSON_ARRAY_T := JSON_ARRAY_T();
L_SUPPLIER_DATA JSON_OBJECT_T;

-- Variable to hold the parsed JSON array of vendor_ids from the request body
L_VENDOR_IDS JSON_ARRAY_T;
L_VENDOR_ID NUMBER;

-- Cursor to fetch matching records with non-null EXP_DATE and ensure uniqueness
CURSOR C_GET_SUPPLIERS (P_VENDOR_ID NUMBER) IS
SELECT DISTINCT
VENDOR_ID,
EXP_DATE
FROM
XXMIC_AP_SUPP_DETAILS_OUT_T
WHERE
VENDOR_ID = P_VENDOR_ID
AND EXP_DATE IS NOT NULL
AND SYSDATE < EXP_DATE; -- Ensuring we only fetch records with future expiration dates

-- Record to store each row fetched from the cursor
L_SUPPLIER_ROW C_GET_SUPPLIERS%ROWTYPE;

-- Status and description
L_STATUS VARCHAR2(10);
L_MESSAGE VARCHAR2(4000);

BEGIN
-- Parse the JSON array from the request body
L_VENDOR_IDS := JSON_ARRAY_T.PARSEbody); -- Assuming :body holds the input vendor_id array

-- Iterate over the list of vendor_ids
FOR I IN 1 .. L_VENDOR_IDS.GET_SIZE LOOP
-- Fetch each vendor_id from the JSON array
L_VENDOR_ID := L_VENDOR_IDS.GET_NUMBER(I);

-- Open the cursor to fetch supplier records
OPEN C_GET_SUPPLIERS(L_VENDOR_ID);
LOOP
FETCH C_GET_SUPPLIERS INTO L_SUPPLIER_ROW;
EXIT WHEN C_GET_SUPPLIERS%NOTFOUND;

-- Create a new JSON object for each supplier record
L_SUPPLIER_DATA := JSON_OBJECT_T();
L_SUPPLIER_DATA.PUT('vendor_id', L_SUPPLIER_ROW.VENDOR_ID);
L_SUPPLIER_DATA.PUT('exp_date', TO_CHAR(L_SUPPLIER_ROW.EXP_DATE, 'YYYY-MM-DD'));

-- Append the JSON object to the JSON array
L_JSON_ARRAY.APPEND(L_SUPPLIER_DATA);
END LOOP;

CLOSE C_GET_SUPPLIERS;

-- If no records found for the current vendor_id, set the status to error
IF L_JSON_ARRAY.GET_SIZE = 0 THEN
L_STATUS := 'ERROR';
L_MESSAGE := 'No matching records with valid EXP_DATE found for vendor_id: ' || L_VENDOR_ID;
ELSE
L_STATUS := 'SUCCESS';
L_MESSAGE := 'Successfully retrieved data';
END IF;
END LOOP;

-- Build the final JSON object
L_JSON_OBJECT.PUT('status', L_STATUS);
L_JSON_OBJECT.PUT('message', L_MESSAGE);
L_JSON_OBJECT.PUT('data', L_JSON_ARRAY);

-- Output the JSON response
OWA_UTIL.MIME_HEADER('application/json', TRUE);
HTP.P(L_JSON_OBJECT.TO_CLOB);

EXCEPTION
WHEN OTHERS THEN
-- Handle any errors and return an error JSON response
L_JSON_OBJECT := JSON_OBJECT_T();
L_JSON_OBJECT.PUT('status', 'ERROR');
L_JSON_OBJECT.PUT('message', 'An error occurred: ' || SQLERRM);
L_JSON_OBJECT.PUT('data', JSON_ARRAY_T());

OWA_UTIL.MIME_HEADER('application/json', TRUE);
HTP.P(L_JSON_OBJECT.TO_CLOB);
END;


my table structure is like below

SEQ_ID NOT NULL NUMBER
VENDOR_ID NOT NULL NUMBER
PARTY_ID NUMBER
VENDOR_NUMBER VARCHAR2(30)
VENDOR_START_DATE DATE
VENDOR_END_DATE DATE
VENDOR_NAME VARCHAR2(360)
PARTY_SITE_ID NUMBER
VENDOR_SITE_ID NOT NULL NUMBER
VENDOR_SITE_CODE VARCHAR2(50)
SITE_START_DATE DATE
SITE_END_DATE DATE
BUSINESS_RELATION_SHIP VARCHAR2(30)
CREATION_DATE DATE
LAST_UPDATE_DATE DATE
FUSION_CREATION_DATE DATE
FUSION_LAST_UPDATE_DATE DATE
FUSION_CREATED_BY VARCHAR2(64)
FUSION_LAST_UPDATED_BY VARCHAR2(64)
BUSINESS_UNIT_ID NUMBER
EXP_DATE DATE

Continue reading...