Notifications
Clear all

[Solved] Return a Value from Stored Procedure to Java

8 Posts
6 Users
0 Reactions
15.8 K Views
0
Topic starter

What is the best way to return a value from a SQL Stored Procedure and pass that returned value to Java code.

Matt Nutsch Topic starter 2021-06-18 20:43:30

After research, this appears like it may not be supported as of version NEO 3.3.

 

SqlResult seems to be the proper class to store and access the result from a SQL call. executeStoredProcedure seems to be the proper method to execute a stored procedure.

 

However, the executeStoredProcedure method has a void return method.

4 Answers
0

You are correct that SqlService.executeStoredProcedure doesn't support those.

 

However, i belive you could use com.onenetwork.platform.data.sql.SqlService.executeQuery(String, SqlParams) to achieve the same, leveraging the JDBC syntax for executing a stored proc: 

 

"{call MY_PROC($PARAM1$, $PARAM2$}"

 

I have not tried this but i suspect it will work.

 

Marco Gouveia 2021-06-18 22:02:53

Unlike SQL Server, Stored Procedures in Oracle cannot return a recordset. The only way to get a response from a Stored Procedure is through an output parameter. I'm currently running some tests to try to capture the value of the output parameter after the call.

0

Here's an example.

Create a Stored Procedure that has an output parameter:

CREATE OR REPLACE PROCEDURE getStringLength(inputText IN VARCHAR2, outputNumber OUT NUMBER)
AS BEGIN
outputNumber := LENGTH(inputText);
END;
/

Then use the following java code to run it:

String res;
String inputText="AAAAAA";
try {
  CallableStatement cs = null;
  Connection conn = DataSourceFactory.getDataSource().getConnection();
  cs = conn.prepareCall("{ call getStringLength(?,?) }");
  cs.setString(1, inputText);
  cs.registerOutParameter(2, java.sql.Types.INTEGER);
  cs.execute();
  res = "Text length = " + cs.getInt(2);
}
catch(Exception e){
  res = "Error: " + e.getMessage();
}

 

0

I'm also facing this problem. But i cannot find solution yet.

Matt Nutsch Topic starter 2021-07-07 21:00:52

Hi @muhammad-majid. @mgouveia provides an example of how to do this in his reply above. Were you not able to get it to work after trying that example? Thanks.

0

same problem with me. please any one can help me.

This post was modified 2 years ago by maria smith1122
jcourter 2022-08-08 12:53:16

Hello @maria-smith1122. @mgouveia provided an example of calling a SQL Procedure from java in his reply above. Are you able to leverage his example as a solution? Thanks.