Re: Oracle jdbco73 calling PL/SQL...

Hi all

I emailed Oracle and they replied with this:

  Hi,

  The oracle jdbc drivers allow the following:

  a plsql block can have an OUT parameter of type Ref Cursor.   This type is defined in OracleTypes, the exact type name is CURSOR.

  You would use this in the following manner:

  CallableStatement cstmt = conn.prepareCall ("begin open ? for select *

  from emp; end;");

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

      while (cursor.next ())
      {
          //get the first column as a string using the standard
          // jdbc ResultSet API
          System.out.println (cursor.getString (1));
      }

  Please note that the "getCursor" API is an extension to the standard   API. It is documented in the docs for our latest beta drivers.   Also, note that the ResultSet object called "cursor", is not passed   back to an executeQuery call. The cursor object you get back   corresponds to the select which has already been executed.

  Below, you can find information about where to get our latest drivers.

  They will be available from our external web site in about a week   (http://www.oracle.com/nca/java_nca/ ). they are currently available   from our ftp site (see below).

  thanks,

  Prabha Krishna.

  We provide:

    JDBC/OCI7 and JDBC/OCI8 for Sparc Solaris     JDBC/OCI7 and JDBC/OCI8 for x86 Win32 (Windows NT and 95)     Thin JDBC for all platforms with Java virtual machine

  We now support JDK 1.1.1 as well as JDK 1.0.2. The drivers should work
  with JDK 1.1.2, although we haven't tested the configuration. If you   find any problem, please let us know. Here's how you can get the   drivers:

  For Sparc Solaris,

ftp://support.us.oracle.com/desktop/download/jdbc-beta-solaris.tar.gz

(3,322,227 bytes)

  For x86 Win32 (Windows NT/95),

    ftp://support.us.oracle.com/desktop/download/jdbc-beta-win32.zip
(654,054 bytes)

  The two files above contain all three JDBC drivers: JDBC/OCI7,   JDBC/OCI8, and thin JDBC. If you want only the thin JDBC driver,   the URL is:

    ftp://support.us.oracle.com/desktop/download/jdbc-beta-thin.tar.gz
(609,116 bytes)

  (JDBC/OCI drivers are type 2 JDBC drivers, while the thin driver is a   type 4 driver.)

  These files are available for temporary download until they are moved   to the official page at:

    http://www.oracle.com/nca/java_nca/

  The documentation has been completely redone to include much more   information than it did in the alpha release. Since there are some   incompatible changes, please read the README.txt and the HTML   documentation carefully.

  JDBC API documentation is no longer included with the drivers. You   can download JDBC 1.22 API doc from JavaSoft's JDBC web site, or you   can get it by installing the full JDK 1.1 documentation.

So I downloaded the latest Oracle driver + JDK 1.1.2. I had the following PL/SQL compiled/stored function/package (simplified):

CREATE OR REPLACE PACKAGE my_package AS
 TYPE my_cursor_type IS REF CURSOR; -- Weak REF CURSOR

 FUNCTION my_function (my_param IN VARCHAR)    RETURN my_cursor_type;
END my_package;
/
CREATE OR REPLACE PACKAGE BODY my_package AS  FUNCTION my_function (my_param IN VARCHAR)    RETURN my_cursor_type IS
    my_cursor my_cursor_type;
 BEGIN
   OPEN my_cursor FOR
    SELECT *
    FROM my_table;
   RETURN my_cursor;
 END my_function;
END my_package;
/

I modified my Java to look something like this (simplified):

    import java.sql.*;

    String jdbcDriver = "oracle.jdbc.driver.OracleDriver";     String dbURL = "jdbc:oracle:oci7:scott/tiger_at_my_db";     ResultSet rset;
    ResultSetMetaData rsmd;
    int columnCount;
    Connection dbConn;

    try {

        try {
            Class.forName(jdbcDriver);
        }
        catch (ClassNotFoundException e) {
            System.err.println ("Could not load driver " + jdbcDriver +
": " + e.getMessage());
            System.exit(1);
        }
        dbConn = DriverManager.getConnection(dbURL);
        pcall = authServer.dbConn.prepareCall("{? = call
my_function(?)}");
        pcall.setString(2, someOldString);
        pcall.registerOutParameter(1, OracleTypes.CURSOR);
        pcall.execute();    // Note the execute() method...
        rset = ((OracleCallableStatement)pcall).getCursor(1);
        rsmd = rset.getMetaData();
        coulmnCount = rsmd.getColumnCount();
        while (rset.next()) {  // Loop through each row
            for (int i = 0; i < columnCount; i++) {
                // Do Whatever to each member of the CURSOR row
            }
        }
        rset.close();    // Close the cursor
    } catch (SQLException e) {   // jdbc SQL exception
        System.err.println("SQL Exception: " + e.getMessage());
        dbConn.close();
        System.exit(1);

Now in my opinion this does not conform to the JDBC 1.2x spec - it should be possible to do the call:

    rset = pcall.executeQuery("{? = call my_function(?)}"); but it seems Oracle have added an "extension". Hmmm. Hope this helps.

Regards

--Richard. Received on Sun Jun 29 1997 - 00:00:00 CEST