Calling PL/SQL code in Java

In this blogpost, I will show you how to interact with Oracle PL/SQL code in Java. Before we continue with the Java code, let's create a simple Oracle package we can use to test our code.

Compile the following package specification and body in the Oracle database.

Package specification:

create or replace
package test_package as

  procedure test_procedure(v varchar2);

  function test_function(v varchar2) return varchar2;

end test_package;

Package body:

create or replace
package body package test_package as

procedure test_procedure(v varchar2) is
begin
    null;
end;

function test_function(v varchar2) return varchar2 is
begin
    return v;
end;

end test_package;

The package contains a procedure and a function that returns a VARCHAR2 type. We can call the procedure with the following Java code:

DataSource ds = ...
Connection con = ds.getConnection();
CallableStatement cs =
    con.prepareCall("{call test_package.test_procedure(?)}");
cs.setString(1, "Calling test_procedure!");
cs.executeUpdate();
cs.close();

The function is slightly different, because we have to register the type the function returns.

DataSource ds = ...
Connection con = ds.getConnection();
CallableStatement cs =
    con.prepareCall("{? = call test_package.test_function(?)}");
cs.registerOutParameter(1, java.sql.Types.VARCHAR);
cs.setString(2, "Calling test_function!");
cs.execute();
String s = cs.getString(1);
cs.close();

The function can also be called in another way. Like this:

DataSource ds = ...
Connection con = ds.getConnection();
PreparedStatement ps =
    con.prepareStatement(
        "SELECT test_package.test_function(?) S FROM dual"
    );
ps.setString(1, "Calling test_function!");
ResultSet r = ps.executeQuery();
String result = null;

if (r.next()) {
    result = r.getString("S");
}

con.close();

This way is similar to executing a standard SQL-statement.