javaoracle

call function in oracle with output parameter Missing IN or OUT parameter at index


I want to call function in oracle with output parameter.

this is the scenario of test which have been done directly in database and it's working

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;
    

the output is like this :

  {"p_principal":[{"p_num_generate":15,"p_error":null}]
,"p_detail":[{"p_emp":200,"p_error":null}]}

Now I want to call it from java :

I try with this code :

 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();
                    }
                }
                
                

            }

but when I test I have this error

Missing IN or OUT parameter at index:: 2
        

can someone help me to solve this problem

Updated :

also I try with :

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");

but I have this error :

Caused by: java.lang.IllegalArgumentException: Type cannot be null

also I try with :

 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);

but I have this error :

 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

Solution

  • Try this variation of your first attempt, not sure if there is a better way but at least it should work:

    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 = "begin ?:=integ_pkg.insert_order(?); end;";
            cs = con.prepareCall(query);
            cs.setString(2, input);
            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();
                    }
                }
                
                
    
            }