CODEWITHSUNDEEP
javaoracle-databaseibatisjdbc-odbc
Omnipresent
Omnipresent

Reputation: 30384

from java how to call a stored procedure passing an oracle cursor as an argument

I am using Java + iBatis and have a need to call an Oracle Stored Procedure that takes a cursor as an argument. Google didn't help me much in finding a code sample of how to call a stored procedure that accepts a cursor as an argument from java.

How can this be accomplished?

Scenario in steps:

 1. Java calls a Stored Proc passing primitives (varchar, char, etc) as
    parameters 
 2. Java retrieves the cursor returned from Step 1 
 3. Java calls a Stored Proc passing cursor from Step 2 as an argument  //how?

Upvotes: 0

Views: 3873

Answers (2)

Dave Costa
Dave Costa

Reputation: 48121

If those are really the only steps -- i.e. you aren't doing anything of importance in Java between the two calls -- then it makes more sense to me to avoid returning to Java at all.

If the first procedure were actually a function, you could simply do a single nested call:

BEGIN proc2(proc1(...)); END;

The cursor gets passed within Oracle and never needs to be handled by Java at all.

If your first procedure is a procedure that returns the cursor as an OUT parameter, you could write a wrapper function for it and do the same thing:

CREATE OR REPLACE FUNCTION func1(...)
  RETURN SYS_REFCURSOR
  AS
    foo SYS_REFCURSOR;
  BEGIN
    proc1(..., foo);
    RETURN foo;
  END func1;
/

Then BEGIN proc2(func1(...)); END; should work.

Now, if you really do need to go out to Java between the two calls, then I would try using OracleTypes.CURSOR when retrieving the output value from the first procedure, then simply pass that object into the second procedure. I don't know if this will work; if not, then there's probably no direct way to do it.

Upvotes: 1

antlersoft
antlersoft

Reputation: 14786

You can't do this.

A cursor passed into an Oracle stored procedure represents an object with an API that only Oracle can provide. Your Java program doesn't know enough about the cursor to create some sort of object that would proxy it and forward the calls back to Oracle.

Your going to have to redesign your app so that the stored procedure that takes an input cursor is only called from another stored procedure.

Upvotes: 0

Related Questions