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?

+2


source to share


2 answers


You need to use the CallableStatement class :



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

      

+8


source


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

.

+8


source







All Articles