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

The code for what follows can be found on github: git://github.com/azzoti/CallAnonymousPLSQLUsingSpring.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).


Prerequisites:
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);
 }
 
 @Override
 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();
 }
 
 @Override
 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;";
 
 @Autowired
 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.