我想用输出参数调用Oracle中的函数。
这是直接在数据库中完成的测试场景，它正在工作
declare
request_detail clob := '{ "p_principal":
[
{
"p_emp": 200,
"p_request": 23,
"p_date": "23-10-2024",
"p_info": "test"
}
]
,"p_detail":
[
{
"p_emp": 200,
"p_date": "23-10-2024",
"p_info": "test"
}
]
}';
json_out clob;
begin
json_out := integ_pkg.insert_order(request_detail);
dbms_output.put_line(json_out);
end;字符串
输出是这样的：
{"p_principal":[{"p_num_generate":15,"p_error":null}]
,"p_detail":[{"p_emp":200,"p_error":null}]}型
现在我想从java中调用它：
我试着用这个代码：
String input= "{ \"p_principal\":\r\n" +
" [\r\n" +
" {\r\n" +
" \"p_emp\": 200,\r\n" +
" \"p_request\": 23,\r\n" +
" \"p_date\": \"23-10-2024\",\r\n" +
" \"p_info\": \"test\"\r\n" +
" }\r\n" +
" ]\r\n" +
" ,\"p_detail\":\r\n" +
" [\r\n" +
" {\r\n" +
" \"p_emp\": 200,\r\n" +
" \"p_date\": \"23-10-2024\",\r\n" +
" \"p_info\": \"test\"\r\n" +
" }\r\n" +
" ]\r\n" +
" }";
Connection con = null;
CallableStatement cs = null;
try {
con = DriverManager.getConnection("jdbc:oracle:thin:@10.25.25.25:1521/demoDB", "demo", "demo");
String query = "{call integ_pkg.insert_order(" + input + ") )}";
cs = con.prepareCall(query);
cs.registerOutParameter(1, java.sql.Types.CLOB);
cs.executeUpdate();
System.out.println(cs.getClob(1));
System.out.println(cs.getString(1));
} catch (Exception e) {
// TODO: handle exception
System.out.println(e.getMessage());
}
finally {
if (cs != null) {
try {
cs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}型
但是当我测试的时候，
Missing IN or OUT parameter at index:: 2型
有人能帮我解决这个问题吗
- 更新时间：*
我也试着：
JSONObject input = new JSONObject( "{ \"p_principal\":\r\n" +
" [\r\n" +
" {\r\n" +
" \"p_emp\": 200,\r\n" +
" \"p_request\": 23,\r\n" +
" \"p_date\": \"23-10-2024\",\r\n" +
" \"p_info\": \"test\"\r\n" +
" }\r\n" +
" ]\r\n" +
" ,\"p_detail\":\r\n" +
" [\r\n" +
" {\r\n" +
" \"p_emp\": 200,\r\n" +
" \"p_date\": \"23-10-2024\",\r\n" +
" \"p_info\": \"test\"\r\n" +
" }\r\n" +
" ]\r\n" +
" }");
StoredProcedureQuery query = em
.createStoredProcedureQuery("integ_pkg.insert_order")
.registerStoredProcedureParameter("input", JSONObject.class, ParameterMode.IN)
.registerStoredProcedureParameter("v_json_out", JSONObject.class, ParameterMode.OUT)
.setParameter("input", input);
query.execute();
JSONObject v_json_out = (JSONObject) query
.getOutputParameterValue("v_json_out");型
但我犯了个错误
Caused by: java.lang.IllegalArgumentException: Type cannot be null型
我也试着：
String input= "{ \"p_principal\":\r\n" +
" [\r\n" +
" {\r\n" +
" \"p_emp\": 200,\r\n" +
" \"p_request\": 23,\r\n" +
" \"p_date\": \"23-10-2024\",\r\n" +
" \"p_info\": \"test\"\r\n" +
" }\r\n" +
" ]\r\n" +
" ,\"p_detail\":\r\n" +
" [\r\n" +
" {\r\n" +
" \"p_emp\": 200,\r\n" +
" \"p_date\": \"23-10-2024\",\r\n" +
" \"p_info\": \"test\"\r\n" +
" }\r\n" +
" ]\r\n" +
" }";
StoredProcedureQuery query = em
.createStoredProcedureQuery("integ_pkg.insert_order")
.registerStoredProcedureParameter("input", String.class, ParameterMode.IN)
.registerStoredProcedureParameter("v_json_out", String.class, ParameterMode.OUT)
.setParameter("input", input);
query.execute();
String v_json_out = (String) query
.getOutputParameterValue("v_json_out");
System.out.println(v_json_out);型
但我犯了个错误
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'insert_order'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored型
1条答案
按热度按时间6ioyuze21#
尝试这种变化的第一次尝试，不知道是否有一个更好的方法，但至少它应该工作：
字符串