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.PARSE(:body);  -- 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

You need to sign in to view this answers

Related Post