send array of object from java to oracle procedure

Question

I tried to send json contain array of json to procedure and this procedure will add the data in two tables, i build the below procedure:

create or replace PROCEDURE INSERT_ELE
(
  ELEMENT_SET_NAME IN VARCHAR2 
, ELEMENT_SET_TYPE VARCHAR2 
, EFFECTIVE_START_DATE IN VARCHAR2 
, EFFECTIVE_END_DATE IN VARCHAR2
, ELEMENT_TYPE_ID IN VARCHAR2 
, OUT_SEQ OUT NUMBER
) AS 
BEGIN
  INSERT ALL INTO payroll_test.PAY_ELEMENT_SETS(ELEMENT_SET_NAME, ELEMENT_SET_TYPE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
  VALUES (ELEMENT_SET_NAME, ELEMENT_SET_TYPE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
  
  INTO payroll_test.PAY_ELEMENT_SET_MEMBERS(ELEMENT_TYPE_ID, ELEMENT_SET_ID)
  VALUES (ELEMENT_TYPE_ID, PAY_ELEMENT_SETS_MEMBERS_SEQ.NEXTVAL +1)
  select  ELEMENT_SET_NAME as ELEMENT_SET_NAME, ELEMENT_SET_TYPE as ELEMENT_SET_TYPE, EFFECTIVE_START_DATE as EFFECTIVE_START_DATE, EFFECTIVE_END_DATE as EFFECTIVE_END_DATE, ELEMENT_TYPE_ID as ELEMENT_TYPE_ID  from dual;
  COMMIT;
END INSERT_ELE

And this is my java code:

            String query = null;
            query = "{call INSERT_ELE(?,?,?,?,?,?)}";
            cstmt = connection.prepareCall(query);
            cstmt.setString(1, objectGroupFormBean.getElementSetName());
            cstmt.setString(2, objectGroupFormBean.getElementSetType());
            cstmt.setString(3, objectGroupFormBean.getEffectiveStartDate());
            cstmt.setString(4, objectGroupFormBean.getEffectiveEndDate());
            cstmt.setString(5, objectGroupFormBean.getElementTypeId());
            cstmt.registerOutParameter(6, OracleTypes.NUMBER);
            cstmt.executeUpdate();
            objectGroupFormBean.setElementSetId(cstmt.getInt(6));
            objectGroupFormBean.getElementSetId();
            objectGroupFormBeanList.add(objectGroupFormBean);

but this just accept like this payload:

{
    "elementSetName": "test",
    "elementSetType": "App",
    "effectiveStartDate": "10-10-1981",
    "effectiveEndDate": "20-08-2020",
    "element":
        {
            "elementId": "181",
            "inclusionStatus": "Include"
        }
    
}

How i can make it to take element as json of objects like this:

{
    "elementSetName": "test",
    "elementSetType": "App",
    "effectiveStartDate": "10-10-1981",
    "effectiveEndDate": "20-08-2020",
    "element": [
        {
            "elementId": "181",
            "inclusionStatus": "Include"
        },
        {
            "elementId": "189",
            "inclusionStatus": "Include"
        }
    ]
}

user_7367293 · Answer

How about flipping this around to avoid all the parameter passing. Just send the JSON to the procedure and let it do the insertion, eg

SQL> with t as
  2  (
  3    select
  4    '{
  5        "elementSetName": "test",
  6        "elementSetType": "App",
  7        "effectiveStartDate": "10-10-1981",
  8        "effectiveEndDate": "20-08-2020",
  9        "element": [
 10            {
 11                "elementId": "181",
 12                "inclusionStatus": "Include"
 13            },
 14            {
 15                "elementId": "189",
 16                "inclusionStatus": "Include"
 17            }
 18        ]
 19    }' j from dual
 20  )
 21  select
 22    json_value(j,'$.elementSetName') name,
 23    json_value(j,'$.elementSetType') type,
 24    json_value(j,'$.effectiveStartDate') sdate,
 25    json_value(j,'$.effectiveEndDate') edate
 26  from t;

NAME            TYPE            SDATE           EDATE
--------------- --------------- --------------- ---------------
test            App             10-10-1981      20-08-2020

1 row selected.

SQL>
SQL> with t as
  2  (
  3    select
  4    '{
  5        "elementSetName": "test",
  6        "elementSetType": "App",
  7        "effectiveStartDate": "10-10-1981",
  8        "effectiveEndDate": "20-08-2020",
  9        "element": [
 10            {
 11                "elementId": "181",
 12                "inclusionStatus": "Include"
 13            },
 14            {
 15                "elementId": "189",
 16                "inclusionStatus": "Include"
 17            }
 18        ]
 19    }' j from dual
 20  )
 21  select jt.*
 22  from t,
 23     json_table(j,'$.element[*]' columns ( elementId number path '$.elementId',
 24                                        inclusionStatus varchar2(20) path '$.inclusionStatus')) jt;

 ELEMENTID INCLUSIONSTATUS
---------- --------------------
       181 Include
       189 Include

2 rows selected.

SQL>
SQL>
SQL>

Thus your procedure would end up being

procedure INS(p_json varchar2) is
begin
  insert into ...
  select ...
end;

where the SELECT is simply based on the ones above

send array of object from java to oracle procedure

1 Answers