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");
    }  

}