ORDS webservice with header and lines input

Question

I am trying to call a PLSQL procedure that has a record(header) and a table type(lines) as input parameters. I can change the procedure if required but at the end of the day, I need to create an item with a header and line. I saw some articles online and one mentioned that Table Types are not allowed in ORDS and only VARRAYS are allowed. Is this true? How can I implement this or is there any work around?

My handler source looks like this. This errors out as the record and table types are not passed properly.

DECLARE
    x_return_code NUMBER;
    x_return_msg VARCHAR2(200);
BEGIN
    xx.xx_manage_item.create_item(   p_req_id        => :req_id,
                                     p_header_rec    => :header_rec,
                                     p_sf_lines_tab  => :lines_tab,
                                     x_return_code   => x_return_code,
                                     x_return_msg    => x_return_msg);
END;

My Post request looks like below: Lines can be multiple

{
   "req_id":1,
   "header_rec":{
      "cust_account_id":"123",
      "order_type":"XX",
      "customer_po":"TestPO2",
      "sales_person":"",
      "currency_code":"USD",
      "end_user_address":"Stockport 12",
      "request_date":"09/10/2023"
   },
   "lines_tab":[
      {
         "inv_item_id":"112",
         "item_name":"",
         "quantity":"1",
         "uom":"EA",
         "plant_number":"w12",
         "request_date":"09/10/2023"
      }
   ]
}

Thanks in advance

Answer 1

Don't try to split the values into header and lines, just pass the entire JSON message to the procedure and process it there:

For example, you can create the procedure like this:

CREATE PROCEDURE create_item(
  p_msg         IN  CLOB,
  x_return_code OUT NUMBER,
  x_return_msg  OUT VARCHAR2
)
IS
  v_req_id          NUMBER;
  v_cust_account_id VARCHAR2(200);
  v_order_type      VARCHAR2(200);
  v_customer_po     VARCHAR2(200);
  -- ...
BEGIN
  SELECT req_id,
         cust_account_id,
         order_type,
         customer_po
         -- ...
  INTO   v_req_id,
         v_cust_account_id,
         v_order_type,
         v_customer_po
         -- ...
  FROM   JSON_TABLE(
           p_msg,
           '$'
           COLUMNS (
             req_id          NUMBER        PATH '$.req_id',
             cust_account_id VARCHAR2(200) PATH '$.header_rec.cust_accout_id',
             order_type      VARCHAR2(200) PATH '$.header_rec.order_type',
             customer_po     VARCHAR2(200) PATH '$.header_rec.customer_po'
             -- ...
           )
         );
  DBMS_OUTPUT.PUT_LINE('req_id: ' || v_req_id);
  DBMS_OUTPUT.PUT_LINE('cust_account_id: ' || v_cust_account_id);
  DBMS_OUTPUT.PUT_LINE('order_type: ' || v_order_type);
  DBMS_OUTPUT.PUT_LINE('customer_po: ' || v_customer_po);
  -- ...

  FOR line IN (
    SELECT inv_item_id,
           item_name,
           quantity
           -- ...
    FROM   JSON_TABLE(
             p_msg,
             '$.lines_tab[*]'
             COLUMNS (
               inv_item_id VARCHAR2(200) PATH '$.inv_item_id',
               item_name   VARCHAR2(200) PATH '$.item_name',
               quantity    VARCHAR2(200) PATH '$.quantity'
               -- ...
             )
           )
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('inv_item_id: ' || line.inv_item_id);
    DBMS_OUTPUT.PUT_LINE('item_name: ' || line.item_name);
    DBMS_OUTPUT.PUT_LINE('quantity: ' || line.quantity);
  END LOOP;
END;
/

and then pass your message:

DECLARE
  v_return_code NUMBER;
  v_return_msg  VARCHAR2(4000);
BEGIN
  DBMS_OUTPUT.ENABLE();
  create_item(
    p_msg => '{
   "req_id":1,
   "header_rec":{
      "cust_account_id":"123",
      "order_type":"XX",
      "customer_po":"TestPO2",
      "sales_person":"",
      "currency_code":"USD",
      "end_user_address":"Stockport 12",
      "request_date":"09/10/2023"
   },
   "lines_tab":[
      {
         "inv_item_id":"112",
         "item_name":"",
         "quantity":"1",
         "uom":"EA",
         "plant_number":"w12",
         "request_date":"09/10/2023"
      }
   ]
}',
    x_return_code => v_return_code,
    x_return_msg  => v_return_msg
  );
END;
/

Which outputs:

req_id: 1
cust_account_id: 
order_type: XX
customer_po: TestPO2
inv_item_id: 112
item_name: 
quantity: 1

fiddle

Answer 2

Found the solution to the problem. I used the body_text parameter to capture the whole JSON as a CLOB object and passed it onto my package as a parameter. I parsed the full JSON inside my package.

So, no we cannot use the table types in ORDS but we can use this work around so we dont have to. Here is the official documentation:

Oracle Documentation