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