英文:
Pass in/out JSON param to oracle stored procedure using simpleJdbcCall
问题
我在Oracle中有存储过程。以下是其定义：
procedure pName(pObj in out JSON_OBJECT_T)
在dBeaver中调用它的示例：
DECLAREPOBJ JSON_OBJECT_T;BEGINPOBJ := JSON_OBJECT_T.parse('{"cli_code" : "01.075648"}');COLVIR.CPL_PKGDEA_UTL.pGetDealList ( POBJ => POBJ) ;DBMS_OUTPUT.PUT_LINE(POBJ.stringify());END;
输出：{"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"}]}
这是我在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);}
我一直收到这种类型的错误：java.sql.SQLException: ORA-03115: 不支持的网络数据类型或表示。
如何声明这个输入/输出参数才能使一切正常工作？
我尝试在Oracle文档和Stack Overflow主题中搜索，但没有成功。
英文:
I have stored procedure in Oracle. Here its defenition:
procedure pName(pObj in out JSON_OBJECT_T)
Example of calling it from dBeaver:
DECLAREPOBJ JSON_OBJECT_T;BEGINPOBJ := 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
答案1
得分: 0
JSON_OBJECT_T 是一种 PL/SQL 数据类型，不等同于 JSON，是在你的 Java 代码中由 OracleTypes.JSON 引用的 SQL 数据类型。你需要将你的存储过程封装成一个接受 JSON 并将其转换为 JSON_OBJECT_T 的过程，然后再调用你的原始过程。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论