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.

Java, PowerMock and the slow death of pointless Interfaces

Back in the day, say around 2000, the use of Java interfaces were pushed as the one true way (tm) for expressing dependecies between classes. The established wisdom was that if one class needs another then it should be expressed as a dependency on an interface. There are two advantages to expressing dependencies via interfaces: (1) you can have a test implementation of the interface so you can unit test a class without using the real dependency (2) you can have multiple implementations of the interface, which might be chosen at runtime. In practice, (2) happens quite rarely, but  remains a completely valid case for interfaces use.


And so, the wisdom went, you were condemned to eternal damnation called a static method on another class. A call to a static method is hard wired like concrete and steel. No way to stub it out for unit testing.


Enter PowerMock in about 2008/2009 which works with EasyMock or Mockito and which allows you to mock pretty much anything:


"PowerMock is a framework that extend other mock libraries such as EasyMock with more powerful capabilities. PowerMock uses a custom classloader and bytecode manipulation to enable mocking of static methods, constructors, final classes and methods, private methods, removal of static initializers and more. By using a custom classloader no changes need to be done to the IDE or continuous integration servers which simplifies adoption. Developers familiar with the supported mock frameworks will find PowerMock easy to use, since the entire expectation API is the same, both for static methods and constructors. PowerMock aims to extend the existing API's with a small number of methods and annotations to enable the extra features. Currently PowerMock supports EasyMock and Mockito."


I have seen PoweMock used a lot in several organizations. It just works(tm). I have noticed that it simplifies the way people write code. 


So, with PowerMock in hand, here is some advice for writing Java, that goes against established wisdom.


1) Don't write to interfaces unless you really need multiple implementations! Why create an interface and a class when just a class will do? If you find you really need an interface later then create one and use it. But remember most of the time, YAGNI for unit testing thanks to PowerMock. (where YAGNI means "you ain't gonna need interfaces" as opposed to the more traditional "you ain't gonna need it".)


2) Use EasyMock or Mockito for unit testing and the extras that PowerMock gives you if you need to. (I have nothing against JMock, and perhaps JMock has the equivalent features that PowerMock provides. )


3) Do not be afraid to use static methods if appropriate. When is appropriate? Now there's a question Rich Hickey would be happy to answer. 


Thanks to PowerMock, we are free to use interfaces where they are really needed.

Examples of Java calling Oracle PLSQL anonymous blocks

Why would you do this? Answer: Developement agility

An Oracle DBA might say that Java should not use anonymous plsql blocks as (a) it embeds embeds database logic into Java code,  and (b) is bad for performance as a stored procedure would have a precompiled execution plan.

But in the organization where I am currently consulting:

•  iBatis and hibernate (arguably)  embed database logic into Java applications. In theory its done in a "portable" way that is not tied to the database implementation. Like thats ever going to change!
• Logistically and bureaucratically, it takes weeks to get a packaged stored procedure created and installed. In my experience this is typical of most large organizations that separate Java developers from database developers and dbas. The human communication in itself between the teams, creates a bottleneck.
• The PLSQL blocks are stored in seperate files and loaded from files. Database gurus tweak the SQL and hand it over for complex queries and updates. 
• Performance is actually not bad, because Oracle bind variables are used in the plsql. This means that oracle sees the same text every time and reuses execution plans.
• Over time, if found to be durable, the PLSQL can be converted to a stored procedure and the anonmous plsql files are replaced with simple procedure calls.

Example 1: Call an anonymous PLSQL Block with one input string and one output string parameter :

  
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class CallPLSQLBlockWithOneInputStringAndOneOutputStringParameter {

    // Warning: this is a simple example program : In a long running application,
    // exception handlers MUST clean up connections statements and result sets.
    public static void main(String[] args) throws SQLException {

        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

        final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "manager");
        String plsql = "" +
        " declare " +  
        "      p_id varchar2(20) := null; " +
        " begin " +
        "    p_id := ?; " +
        "    ? := 'input parameter was = ' || p_id;" +
        " end;";
        CallableStatement cs = c.prepareCall(plsql);
        cs.setString(1, "12345");
        cs.registerOutParameter(2, Types.VARCHAR);
        cs.execute();
        
        System.out.println("Output parameter was = '" + cs.getObject(2) + "'");
        
        cs.close();
        c.close();
    }

}

Java: Call an anonymous PLSQL Block with one input string and one output string parameter and one output cursor (query result) parameter :

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;

import oracle.jdbc.OracleTypes;

public class CallPLSQLBlockWithOneInputStringAndOneOutputStringParameterAndOneOutputCursorParameter {


    // Warning: this is a simple example program : In a long running application,
    // exception handlers MUST clean up connections statements and result sets.

public static void main(String[] args) throws Exception {

        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
        
        final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "manager");
        String plsql = "" +
        " declare " +  
        "    p_id varchar2(20) := null; " +
        "     l_rc sys_refcursor;" +
        " begin " +
        "    p_id := ?; " +
        "    ? := 'input parameter was = ' || p_id;" +
        "    open l_rc for " +
        "        select 1 id, 'hello' name from dual " +
        "        union " +
        "        select 2, 'peter' from dual; " +
        "    ? := l_rc;" +
        " end;";

        CallableStatement cs = c.prepareCall(plsql);
        cs.setString(1, "12345");
        cs.registerOutParameter(2, Types.VARCHAR);
        cs.registerOutParameter(3, OracleTypes.CURSOR);
        
        cs.execute();
        
        System.out.println("Result = " + cs.getObject(2));
        
        ResultSet cursorResultSet = (ResultSet) cs.getObject(3);
        while (cursorResultSet.next ())
        {
            System.out.println (cursorResultSet.getInt(1) + " " + cursorResultSet.getString(2));
        } 
        cs.close();
        c.close();
    }
}

Example:  Call an anonymous PLSQL Block with one input string array and one output string parameter and one output cursor (query result) parameter :

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;

import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class CallPLSQLBlockWithOneInputStringArrayAndOneOutputStringParameterAndOneOutputCursorParameter {

    // Warning: this is a simple example program : In a long running application,
    // exception handlers MUST clean up connections statements and result sets.
public static void main(String[] args) throws Exception {

        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
        
        // Warning: this is a simple example program : In a long running application,
        // error handlers MUST clean up connections statements and result sets.
        
        final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "manager");
        String plsql = "" +
        " declare " +  
        "    p_id string_array := null; " +
        "     l_rc sys_refcursor;" +
        " begin " +
        "    p_id := ?; " +
        "    ? := 'input parameter first element was = ' || p_id(1);" +
        "    open l_rc for select * from table(p_id) ; " +
        "    ? := l_rc;" +
        " end;";

        String[] stringArray = new String[]{ "mathew", "mark"};
        
        // MUST CREATE THIS IN ORACLE BEFORE RUNNING
        System.out.println("(This should be done once in Oracle)");
        c.createStatement().execute("create or replace type string_array is table of varchar2(32)");
        
        ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( "STRING_ARRAY", c );
        
        Array array_to_pass = new ARRAY( descriptor, c, stringArray );
        
        CallableStatement cs = c.prepareCall(plsql);
        cs.setArray( 1, array_to_pass );
        cs.registerOutParameter(2, Types.VARCHAR);
        cs.registerOutParameter(3, OracleTypes.CURSOR);
        
        cs.execute();
        
        System.out.println("Result = " + cs.getObject(2));
        
        ResultSet cursorResultSet = (ResultSet) cs.getObject(3);
        while (cursorResultSet.next ())
        {
            System.out.println (cursorResultSet.getString(1));
        } 
        cs.close();
        c.close();
    }
}  

Example: Call an anonymous PLSQL Block with one input structure array and one output string parameter and one output cursor (query result) parameter :

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class CallPLSQLBlockWithOneInputStructureArrayAndOneOutputStringParameterAndOneOutputCursorParameter {

    public static void main(String[] args) throws Exception {

        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
        
        // Warning: this is a simple example program : In a long running application,
        // error handlers MUST clean up connections statements and result sets.
        
        final Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "manager");
        String plsql = "" +
        " declare " +  
        "    p_id student_array := null; " +
        "     l_rc sys_refcursor;" +
        " begin " +
        "    p_id := ?; " +
        "    ? := 'input parameter first element was = (' || p_id(1).id_num || ', ' || p_id(1).name || ')'; " +
        "    open l_rc for select * from table(p_id) ; " +
        "    ? := l_rc;" +
        " end;";

        
        // MUST CREATE ORACLE TYPES BEFORE RUNNING
        setupOracleTypes(c);
        
        StructDescriptor structDescr = StructDescriptor.createDescriptor("STUDENT", c);
        STRUCT s1struct = new STRUCT(structDescr, c, new Object[]{1, "mathew"});
        STRUCT s2struct = new STRUCT(structDescr, c, new Object[]{2, "mark"});
        ArrayDescriptor arrayDescr = ArrayDescriptor.createDescriptor( "STUDENT_ARRAY", c );
        Array array_to_pass = new ARRAY( arrayDescr, c, new Object[]{s1struct, s2struct} );
        
        CallableStatement cs = c.prepareCall(plsql);
        cs.setArray( 1, array_to_pass );
        cs.registerOutParameter(2, Types.VARCHAR);
        cs.registerOutParameter(3, OracleTypes.CURSOR);
        
        cs.execute();
        
        System.out.println("Result = " + cs.getObject(2));
        
        ResultSet cursorResultSet = (ResultSet) cs.getObject(3);
        while (cursorResultSet.next ())
        {
            System.out.println (cursorResultSet.getInt(1) + " " + cursorResultSet.getString(2));
        } 
        cs.close();
        c.close();
    }

    private static void setupOracleTypes(final Connection c)
            throws SQLException {
        System.out.println("(This should be done once in Oracle)");
        try {
            c.createStatement().execute("drop type student_array ");
        } catch (Exception e) {
            // ignore
        }
        c.createStatement().execute("create or replace type student as object (id_num integer(4), name varchar2(25))");
        c.createStatement().execute("create or replace type student_array is table of student");
    }  

}