Hi,
I am creating a RESTful service in Oracle APEX 24.1. I am trying to create a POST Method API, upon accessing its end point URL, the data should be inserted into tables.
Table Structure
------ HEADER ------
CREATE TABLE CUST_BL_HEADER
(
HEADER_ID NUMBER,
BL_NUMBER VARCHAR2(100 BYTE),
ORG_ID NUMBER
)
----- LINES--------
CREATE TABLE CUST_BL_DETAIL
(
LINE_ID NUMBER,
ITEM_CODE VARCHAR2(100 BYTE),
QUANTITY NUMBER
)
----- JSON PAYLOAD-----------
{
"BL_NUMBER": "BL12345",
"ORG_ID": 22,
"DETAILS": [
{
"item_code": "ITEM001",
"quantity": 10
},
{
"item_code": "ITEM002",
"quantity": 20
}
]
}
-------- PLSQL CALL IN ORACLE APEX Front end Application -------
declare
-- Declare variables to hold the incoming data
p_bl_number varchar2(100);
p_org_id number;
-- v_header_id number;
-- Declare variables for detail records
-- v_line_id number;
v_item_code varchar2(100);
v_quantity number;
begin
--apex_json.parse(:BODY);
-- Parse the input JSON body to get the parameters
p_bl_number := :BL_NUMBER;
p_org_id := :ORG_ID;
-- Insert data into the header table (cust_bl_header)
insert into cust_bl_header (bl_number, org_id)
values (p_bl_number, p_org_id);
-- returning header_id into v_header_id;
-- -- Loop through the detail data (assuming JSON array for details)
for i in 1..apex_json.get_count('DETAILS') loop
insert into cust_bl_detail ( item_code, quantity)
values ( 'TEST1', 500);
end loop;
-- -- Commit the transaction
commit;
-- Return a success message or status code
apex_util.set_session_state('success', 'Data inserted successfully');
exception
when others then
-- Rollback in case of error
commit;
-- Return an error message
apex_util.set_session_state('error', 'An error occurred: ' || sqlerrm);
end;
The problem is that lines are not being inserted into the table, If i remove “for i in 1..apex_json.get_count('DETAILS') loop” and replace it with a number like for i in 1..3 loop, then the loop runs and inserts the data into table.
I want to insert data into lines table based on the number of lines in JSON payload.