Using OUT parameters in SQL procedures called from Java to return multiple result values
Using stored procedures and functions when working with the database server has a number of advantages: it allows to abstract and externalize the implementation of certain data-crunching algorithms, may sometimes simplify the transaction processing and may reduce the overhead caused by the complex operation since there is less data to convert/serialize. It may also make the system more portable: you can minimize the amount of changes needed for the database client application and just port the implementation of the stored procedures.
Support for multiple parameters using OUT mode adds one more interesting possibility: you can return multiple values resulting from a single call to the stored procedure (using OUT parameters for the stored functions does not have too much sense). This article shows how to deal with these parameters from Java program.
Lets consider the following Oracle PL/SQL example:
create or replace procedure test_out_params(
user_name in varchar,
hello_msg out varchar,
session_id out varchar) is
begin
hello_msg := 'Hello, ' || user_name;
session_id := SYS_CONTEXT('USERENV', 'SESSIONID');
end;
/
This procedure just accepts one parameter and returns two values. In order to call this procedure and receive the values assigned to OUT parameters you need to use java.sql.CallableStatement interface. It is not only specifically designed to make the stored procedure/function calls in server-independent manner, but also supports the OUT parameters.
The following Java program makes the call to the test procedure and displays the results:
import java.sql.*;
public final class TestProcCaller {
private static final String DB_CONN_URL =
"jdbc:oracle:thin:@dbhost:1521:my_SID";
private static final String DB_USER =
"my_user";
private static final String DB_PASSWORD =
"my_password";
/**
* @param args
*/
public static void main(String[] args) throws Exception {
Connection conn = null;
CallableStatement st = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(DB_CONN_URL,
DB_USER, DB_PASSWORD);
st = conn.prepareCall("{call test_out_params(?, ?, ?)}");
st.setString(1, "Nik");
st.registerOutParameter(2, Types.VARCHAR);
st.registerOutParameter(3, Types.VARCHAR);
st.execute();
System.out.println("Received greeting from DB session " +
st.getString(3) + ": " + st.getString(2));
} catch (Exception e) {
System.err.println("Test failed: " + e.getMessage());
e.printStackTrace();
} finally {
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
}
}
}
Running this Java program with Oracle produces something like this:
$ java -classpath ojdbc14.jar:. TestProcCaller Received greeting from DB session 9387: Hello, Nik
It is important to register (with appropriate types) all OUT parameters before executing the call.
Just a little side note. I had a chat with one of my colleagues at some point, he was claiming that the using of the OUT parameters is highly discouraged because it is not OO-style etc, etc. I tried to convince him that OO is just one of the programming paradigms that does not represent the only truth in the world, that the procedural programming is another paradigm that existed long before OO one. I believe I have not succeeded to convince him, not sure that he ever programmed with Assembler or Fortran ;). First of all, the SQL is a PROCEDURAL language (yes, there are some OO constructs supported by some databases) and in either case the DB interface is procedural, even if it is wrapped in the OO-style API like JDBC. So, I believe you should use the best of both paradigms: write a good OO application if you are using OO language and use the procedural interface to your data.
blog comments powered by Disqus