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
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.