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?

Romain Linsolas · Accepted Answer · 2009-08-31 15:17:52Z

8

You need to use the CallableStatement class:

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

answered Aug 31, 2009 at 15:17

Romain Linsolas

82.1k52 gold badges207 silver badges277 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adam Paynter Over a year ago

+1. I also suspect the author will need some documentation on returning table types: stackoverflow.com/questions/1031420/…

Michał Ziober Over a year ago

Yes, I know it. But what doing with my type which I created in PL/SQL? How to configure CallableStatement that return collection of my type?

oxbow_lakes · Accepted Answer · 2009-08-31 15:41:22Z

8

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.

edited Aug 31, 2009 at 15:41

answered Aug 31, 2009 at 15:30

oxbow_lakes

135k56 gold badges322 silver badges451 bronze badges

1 Comment

igor.beslic Over a year ago

probably for statement like this, introducing spring libraries into project is nonsense. There is also scenario where this author could decide to deploy code directly to Oracle DBMS, where spring would make a lot of pain.

Collectives™ on Stack Overflow

Launch Oracle stored-procedure in Java code

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related