In this short article I describe how to easily call functions and stored procedures defined in a database package from Java code, using plain jdbc with Hibernate. I am leaving it more for personal reference. It is not interesting to read, unless you want to do the exact same thing.

Introduction

On one project, I was not allowed to use the usual sql statements on some of the tables in an Oracle database. Instead, I had to call the database functions and stored procedures they defined in their database packages.

A typical declaration of such a package looks like this:

CREATE OR REPLACE PACKAGE INTEGRATED_STUFF.TEST_PKG AS

  TYPE rc_Query_Recs IS REF CURSOR;

  PROCEDURE Select_TestData(csData OUT rc_Query_Recs,
                        p_title IN TEST_TABLE.TITLE%TYPE);

  FUNCTION Fn_Insert_TestRecord(p_title IN TEST_TABLE.TITLE%TYPE,
			p_desc IN TEST_TABLE.DESCRIPTION%TYPE,
			p_userid IN VARCHAR2) RETURN VARCHAR2;

END TEST_PKG;

After that you would declare the package body with the actual implementation of the functions and procedures, but that is not what this article is about.

Calling a database function

In Hibernate, the database function in the package would be called like this:

final String title = "Test record";
final String description = "The description of the test record.";
final String userid = "admin";
Work work = new Work() {
     public void execute(Connection connection) throws SQLException {
          String query = "{? = call INTEGRATED_STUFF.TEST_PKG.Fn_Insert_TestRecord(?, ?, ?)}";
	  CallableStatement statement = connection.prepareCall(query);
	  statement.registerOutParameter(1,java.sql.Types.VARCHAR);
	  statement.setString(2, title );
	  statement.setString(3, description);
          statement.setString(4, userid);
	  statement.execute();

	  String result = statement.getString(1);
	  log.info("Result of insert function: " + result );

	  statement.close();
     }
};
hibernateSession.doWork(work);

The org.hibernate.jdbc.Work class is part of the Hibernate framework and lets you easily execute native sql.
The only special thing in the above example is the binding of the out parameter.
Notice how the out parameter is filled in after the statement execution.

Calling a stored procedure

Calling a procedure is very similar:

final String title = "Test record";
final List titles = new ArrayList();
Work work = new Work() {
	public void execute(Connection connection) throws SQLException {
		String query = "call PACKAGE INTEGRATED_STUFF.TEST_PKG.Select_TestData(?,?)";
		CallableStatement statement = connection.prepareCall(query);
		statement.registerOutParameter(1, OracleTypes.CURSOR);
		statement.setString(2, title);
		statement.execute();
		ResultSet cursor = (ResultSet) statement.getObject(1);
		while (cursor.next()) {
			titles.add(cursor.getString(1));
		}
		statement.close();
	}
};
hibernateSession.doWork(work);

Again, we bind the out parameter, and fetch its contents after statement execution. This time, its position is just among the other parameters in the query.
Notice that we had to register a very specific Oracle datatype, coming with the Oracle jdbc driver: OracleTypes.CURSOR.