Search code examples
javaoraclesimplejdbccall

Pass in/out JSON param to oracle stored procedure using simpleJdbcCall

I have stored procedure in Oracle. Here its defenition:

procedure pName(pObj in out JSON_OBJECT_T)

Example of calling it from dBeaver:

DECLARE
  POBJ JSON_OBJECT_T;
BEGIN
  POBJ := JSON_OBJECT_T.parse('{"cli_code" : "01.075648"}');
  COLVIR.CPL_PKGDEA_UTL.pGetDealList (  POBJ => POBJ) ;  
  DBMS_OUTPUT.PUT_LINE(POBJ.stringify());
END;

OUTPUT: {"cli_code":"01.075648","cpl_deals":[{"dep_id":1228,"dea_id":14754171,"dea_code":"1","fromdate":"2023-02-17","todate":"2023-12-31", "dep_id":1222,"dea_id":14754171,"dea_code":"1","fromdate":"2023-02-17","todate":"2023-12-31"}]}

Here is my function in java:

public void getDealList(String clientCode)  {

        SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
                .withSchemaName(SCHEMA_NAME)
                .withCatalogName(CPL)
                .withProcedureName(pName)
                .withoutProcedureColumnMetaDataAccess()
                .declareParameters(
                        new SqlParameter(POBJ, OracleTypes.),
                        new SqlParameter(POBJ, OracleTypes.JSON));

        OracleJsonFactory factory = new OracleJsonFactory();
        OracleJsonObject json = factory.createObject();


        json.put("cli_code", clientCode);
        Map<String, Object> map = new HashMap<>();
        map.put(POBJ, json);

        SqlParameterSource in = new MapSqlParameterSource(map);

        Map<String, Object> out = simpleJdbcCall.execute(in);
}

I keep getting this type of error: java.sql.SQLException: ORA-03115: unsupported network datatype or representation.

How should i declare this in/out parameter to make evething work?

I tried to search in oracle documentation/ in stackoverflow threads but there was no success

Solution

  • A JSON_OBJECT_T is a PL/SQL datatype not equivalent to JSON, a SQL datatype referenced by OracleTypes.JSON in you Java code. You have to wrap your procedure into one accepting a JSON and converting it into JSON_OBJECT_T to call your original one.