Running Oracle Stored Procedure in Java Code
I have written a stored procedure in Oracle and now I want to run it in Java code. I will describe the problem. I have an object type:
TYPE PERSON_TYPE AS OBJECT (ID NUMBER(38), NAME VARCHAR2(20));
And the 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 do I run this routine in Java code? What are the best classes to do this?
You need to use the CallableStatement class :
String sql = "{call EVALUATE_PERSON_PROC(?, ?)}";
CallableStatement statement = connection.prepareCall(sql);
...
statement.execute();
Why not use Spring's DAO abstraction (a very useful and fairly lightweight library around raw JDBC that eliminates the need for boilerplate code), you can subclass the class StoredProcedure
.
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 it is done very simply as follows:
new MySproc(ds).execute("Hello", 12);
Without Connection
s database , CallableStatement
anywhere. Beautiful! Oh yes, and it provides Transaction
s annotations as well .
If your sproc is returning a table, it's incredibly easy using Spring. Just declare:
declare(new SqlReturnResultSet("rs", mapper));
Where mapper
is the instance that will convert the string a ResultSet
to the desired object. Then change your line:
Map out = super.execute(m);
return (Collection) out.get("rs");
The one returned Collection
will contain instances of objects created by your implementation mapper
.