Re: Java ResultSet from PL/SQL Output?

From: Björn Abelli <DoNotSpam.bjorn_abelli_at_hotmail.com>
Date: Sat, 31 May 2003 11:09:44 +0200
Message-ID: <bb9rgp$7ata0$1@ID-73292.news.dfncis.de>

"May Nine" wrote...

> My question is, what kind of Oracle object do I have
> my PL/SQL proc return so that CallableStatement.getResultSet()
> will work?

Perhaps some other types can work as well nowadays, but I have previously made it work with a CURSOR.

One (slimmed) example that have worked for me:

---

• Define the type of the cursor

   TYPE MyType IS REF CURSOR RETURN students%ROWTYPE;

---

• Define the procedure:

   PROCEDURE MyProc1 (o_studCur OUT MyType);    ...

---

// In Oracle's driver you don't
// use "getResultSet", but "getCursor"

  CallableStatement cstmt

• con.prepareCall ("{call MyProc1 (?)}");

  cstmt.registerOutParameter(1, OracleTypes.CURSOR);   cstmt.execute();
  ResultSet rset =
    ((OracleCallableStatement)cstmt).getCursor(1);

---

There are some issues about it however.

Different drivers works differently, as it does'nt even work with all JDBC-drivers. How it works with the different drivers is also depending on which version of the Oracle database you are using...

My example above was made with Oracle's driver.

I've never tried WebLogic's driver, but I think that one doesn't need the explicit typecast. Then again I think I've seen something about a bug in it with Oracle on a Solaris operating system.

A lot of "ifs"...

// Bjorn A Received on Sat May 31 2003 - 04:09:44 CDT