Java 在Oracle中调用输出参数在索引处缺少IN或OUT参数的函数

我想用输出参数调用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调用它:

我try 使用以下代码:

 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
        

有人能帮我解决这个问题吗？

Updated :个

此外，我还try :

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

此外，我还try :

 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