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?
source to share
You need to use the CallableStatement class :
String sql = "{call EVALUATE_PERSON_PROC(?, ?)}";
CallableStatement statement = connection.prepareCall(sql);
...
statement.execute();
source to share
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
.
source to share