Sunday, 20 May 2012

Using Spring Stored Procedure Support to call Oracle anonymous PL/SQL blocks

The code for what follows can be found on github: git://

Its an eclipse maven project and is easy to run the Main class in Eclipse but it can be run as a vanilla maven project.   (mvn test).

1) You need to manually download the oracle jdbc driver jar and put it in the lib folder.
2) You need to have an oracle instance running localhost:1521:XE with user "system" password "manager" (or change the code)

With a small amount of fiddling Spring's StoredProcedure class can be used to call an anonymous PL/SQL block. The fiddling is class OraclePLSQLBlock which extends Spring's  StoredProcedure class and overrides it to stop spring messing the sql up:

There is not much to it:

public class OraclePLSQLBlock extends StoredProcedure {
 public OraclePLSQLBlock(JdbcTemplate jdbcTemplate, String plsql) {
  super(jdbcTemplate, plsql);

 public OraclePLSQLBlock(DataSource datasource, String plsql) {
  super(datasource, plsql);
 public String getCallString() {
  // we control the creation of the sql string that is actually sent to the database
  // (by default spring will construct a string {call xxx} where xxx is the sql that 
  // we set in the contructor) 
  return getSql();
 public boolean isSqlReadyForUse() {
  // stop spring from adding ? parameter placeholders to the sql
  return true;

The simplest example of using OraclePLSQLBlock to call a PL/SQL block is in class ExampleSpringStoredProcedureCallingPLSQLBlockWithStringReturn which has an IN string parameter and and OUT string parameter.


public class ExampleSpringStoredProcedureCallingPLSQLBlockWithStringReturn extends OraclePLSQLBlock {
 private static final String PLSQL = "" +
 " declare " +  
 "      p_id varchar2(20) := null; " +
 " begin " +
 "    p_id := :inputParameter; " +
 "    ? := 'input parameter was = ' || p_id;" +
 " end;";
 public ExampleSpringStoredProcedureCallingPLSQLBlockWithStringReturn(DataSource datasource) {
  super(datasource, PLSQL);
        declareParameter(new SqlParameter("inputParameter", Types.VARCHAR));
        declareParameter(new SqlOutParameter("outputParameter", Types.VARCHAR));
 public Map< String, Object > executePLSQLBlock(String id) {
  return this.execute(id);

There are other examples, which get progressively more complex including: ExampleSpringStoredProcedureCallingComplexPLSQLBlockWithCursorReturn which passes in an Array of Students and returns a cursor query which maps results to a list of Person.

Post a Comment