Launch Oracle stored-procedure in Java code.

Question

I wrote a stored-procedure in Oracle and now, I want to launch it in Java code. I will describe a problem. I have a object type:

TYPE PERSON_TYPE AS OBJECT (ID NUMBER(38), NAME VARCHAR2(20));

And table type:

TYPE PERSON_TYPE_TABLE AS TABLE OF PERSON_TYPE;

My procedure looks like this:

PROCEDURE EVALUATE_PERSON_PROC(P_PERSON_ID IN NUMBER, return_data OUT NOCOPY PERSON_TYPE_TABLE) 
AS
--Some code
BEGIN
--Some code
END;

How to launch this procedure in Java code? Which classes are the best to do it?

Answer 1

You need to use the CallableStatement class:

String sql = "{call EVALUATE_PERSON_PROC(?, ?)}";
CallableStatement statement = connection.prepareCall(sql);
...
statement.execute();

Answer 2

Why not use Spring's DAO abstraction (a very useful and reasonably lightweight library around raw JDBC which eliminates the need for boilerplate code) you can subclass the StoredProcedure class.

class MySproc extends StoredProcedure {
    public MySproc(DataSource ds) {
       super(" { exec MY_SPROC ?, ? }", ds);
       declare(new SqlParameter("p1", Types.VARCHAR));
       declare(new SqlParameter("p2", Types.INT));
    }

    public void execute(String p1, int p2) {
        Map m = new HashMap();
        m.put("p1", p1);
        m.put("p2", p2);
        super.execute(m);
    }
}

Then this is executed very simply as follows:

new MySproc(ds).execute("Hello", 12);

With no database Connections, CallableStatements anywhere to be seen. Lovely! Oh yes, and it also provides annotation-based Transactions.

If your sproc returns a table, this is incredibly easy using Spring. Simply declare:

       declare(new SqlReturnResultSet("rs", mapper));

Where mapper is an instance that converts a line of a ResultSet into the desired object. Then modify your line:

        Map out = super.execute(m);
        return (Collection) out.get("rs");

The returned Collection will contain instances of objects created by your mapper implementation.