Saturday, December 4, 2010

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.

4 comments:

  1. can u help me..
    i just wanna get the result of auto trace. while there was a sql statement executed.
    i used oracle 10 gr2 on linux.

    thx b4

    ReplyDelete
  2. any one please explain what is the following syntax in plsql. i am new in plsql :
    "begin ?:=jitobj.spf$find_object(?); end; ".

    ReplyDelete