Java and Oracle with JDBC Type 3 Driver

I forgot : I tried the code below, but it uses type 4 OracleDriver. And I don't won't to use it. But then the problem is that the driver I currently use doesn't know the OracleTypes.CURSOR type (and then I can't get the ResultSet).
Here is the code :

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

class RefCursorExample
{
   public static void main(String args[]) throws SQLException
   {
      //Load the driver.
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

      // Connect to the database.
         Connection conn =
         DriverManager.getConnection("jdbc:oracle:oci8:@", "user", "psswd");
      // Create the stored procedure.
      init(conn);

      //Prepare a call
      CallableStatement call =
         conn.prepareCall("{ ? = call java_refcursor.job_listing (?) }");

      call.registerOutParameter(1, OracleTypes.CURSOR);
      call.setString(2, "SALESMAN");
      call.execute();
      ResultSet rset = (ResultSet)call.getObject(1);

      while (rset.next())
         System.out.println(rset.getString("ENAME"));
   }

// Utility function to create the stored procedure
                                            static void init(Connection conn) throws SQLException
   {
      Statement stmt = conn.createStatement();
                                              stmt.execute("CREATE OR REPLACE PACKAGE java_refcursor AS " +
                   " type myrctype is ref cursor return EMP%ROWTYPE; " +
                   " function job_listing(j varchar2) return myrctype; " +
                   "end java_refcursor;");
                                             stmt.execute("CREATE OR REPLACE PACKAGE BODY java_refcursor AS " +
                   " function job_listing(j varchar2) return myrctype is " +
     "    rc myrctype; " +
     " begin " +
     "    open rc for select * from emp where job = j; " +
     "    return rc; " +
     " end; " +
     "end java_cursor;");
   }
}

Caribou has a JDBC Oracle driver that has an example
of Callable statement:
http://209.98.53.137/clsdown/doc/jsqlfaq.html 

Meant to add:

This way you can avoid specifically using the cursor type but can still retrieve resultsets.

Leave your stored procedure returning into the first parameter (which is a cursor) and then setting the cursor as an OTHER type allows you to retrieve it as a resultset later.

Full example of SQL and Java is as follows. It shows an example of returning both single and multiple resultsets:

[just change the weblogic driver name to the JDataConnect one you require]

SQL is:

create or replace package
curs_types as
type EmpCurType is REF CURSOR RETURN emp%ROWTYPE;
end curs_types;
/

create or replace procedure
single_cursor(curs1 IN OUT curs_types.EmpCurType,
ctype in number) AS BEGIN
  if ctype = 1 then
    OPEN curs1 FOR SELECT * FROM emp;
  elsif ctype = 2 then
    OPEN curs1 FOR SELECT * FROM emp where sal > 2000;
  elsif ctype = 3 then
    OPEN curs1 FOR SELECT * FROM emp where deptno = 20;
  end if;
END single_cursor;
/

create or replace procedure
multi_cursor(curs1 IN OUT curs_types.EmpCurType,
             curs2 IN OUT curs_types.EmpCurType,
             curs3 IN OUT curs_types.EmpCurType) AS
BEGIN
    OPEN curs1 FOR SELECT * FROM emp;
    OPEN curs2 FOR SELECT * FROM emp where sal > 2000;
    OPEN curs3 FOR SELECT * FROM emp where deptno = 20;
END multi_cursor;
/


Java is:


weblogic.jdbc.oci.CallableStatement cstmt =
    (weblogic.jdbc.oci.CallableStatement)conn.prepareCall(
                       "BEGIN OPEN ? " +
                       "FOR select * from emp; END;");
  cstmt.registerOutParameter(1, java.sql.Types.OTHER);

  cstmt.execute();
  ResultSet rs = cstmt.getResultSet(1);
  printResultSet(rs);
  rs.close();
  cstmt.close();

  weblogic.jdbc.oci.CallableStatement cstmt2 =
    (weblogic.jdbc.oci.CallableStatement)conn.prepareCall(
                       "BEGIN single_cursor(?, ?); END;");
  cstmt2.registerOutParameter(1, java.sql.Types.OTHER);

  cstmt2.setInt(2, 1);  
  cstmt2.execute();
  rs = cstmt2.getResultSet(1);
  printResultSet(rs);

  cstmt2.setInt(2, 2);  
  cstmt2.execute();
  rs = cstmt2.getResultSet(1);
  printResultSet(rs);

  cstmt2.setInt(2, 3);  
  cstmt2.execute();
  rs = cstmt2.getResultSet(1);
  printResultSet(rs);

  cstmt2.close();

  weblogic.jdbc.oci.CallableStatement cstmt3 =
    (weblogic.jdbc.oci.CallableStatement)conn.prepareCall(
                         "BEGIN multi_cursor(?, ?, ?); END;");
  cstmt3.registerOutParameter(1, java.sql.Types.OTHER);
  cstmt3.registerOutParameter(2, java.sql.Types.OTHER);
  cstmt3.registerOutParameter(3, java.sql.Types.OTHER);

  cstmt3.execute();

  ResultSet rs1 = cstmt3.getResultSet(1);
  ResultSet rs2 = cstmt3.getResultSet(2);
  ResultSet rs3 = cstmt3.getResultSet(3);

Also just use ordinary CallableStatement or equivalent for your driver...

I did exactly what you said.
And here is the result (translate from french =):
[Oracle][ODBC][Ora]ORA-06550: Line 1, column 7:
PLS-00306: number or bad arguments types in the call of MyProc'
ORA-06550: Line 1, columns 7:
PL/SQL: Statement ignored


The definition of MyProc is :
MyPkge
as
procedure MyProc(curs1 IN OUT MyPkge.EmpCurType)
as
begin
open curs1 for
select * from emp;
end MyProc;
end MyPkge;

I don't understand why it doesn't work.

Neither do I...yet :-)

I think I will need to see exactly how you are calling the statement to help anymore. Can you post the Java code?

Two things to try:

**********
1)
**********

In theory, the call to your procedure should look like this:

CallableStatement cstmt2 = conn.prepareCall(
                       "BEGIN MyProc(?); END;");    
cstmt.registerOutParameter(1, java.sql.Types.OTHER);

cstmt.execute();
ResultSet rs = cstmt.getResultSet(1);

Is this what you have?

**********
2)
**********

HOWEVER.

Something else to try in the meantime is another way of calling prepared statements returning a single resultset, like this:

CallableStatement cstmt =
   con.prepareCall("{call getTestData(?, ?)}");

cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
cstmt.registerOutParameter(2, java.sql.Types.DECIMAL, 3);

ResultSet rs = cstmt.executeQuery();

This way your conversion from Cursor to result set is implicit. This may work better as I have heard ODBC has problems with out parameters as result sets - it may work better by returning the result set from the procedure rather than it being one of the parameters.

Thanx again. I will give you the 600 points and a grade A for your help.
But (and I still don't understand why) it still doesn't work.
I have exactly the same as 1) in your previous comment.
But I don't understand the 2).
How do you declare the procedure getTestDate(?, ?)
(why Types.TINYINT?
And are the two parameters IN OUT parameters?)
Making the conversion from Cursor to ResultSet implicit is probably a good idea (and a solution), but can you give me the declaration.
If i can't do it after that, I'll give up and you'll have the points :-|
Thanx

While I post the main answer try one rather petty thing that may make a difference.

Replace the ?'s in your callablestatement with :1, :2 like this:

CallableStatement cstmt2 = conn.prepareCall(
                       "BEGIN MyProc(:1); END;");    


This may be a quirk of your JDBC driver...(there is not much support info on JDataConnect - do you have to use this driver?)

This is because Oracle and SQL92 formats for parameters are different.

Oracle JDBC drivers support execution of PL/SQL stored procedures and anonymous blocks. They support both SQL92 escape syntax and Oracle escape syntax. The following PL/SQL calls are all available from any Oracle JDBC driver:

// SQL92 Syntax
CallableStatement cs1 = conn.prepareCall
     
                       ( "{call proc (?,?)}" ) ;

     CallableStatement cs2 = conn.prepareCall
     
                       ( "{? = call func (?,?)}" ) ;

// Oracle Syntax
CallableStatement cs3 = conn.prepareCall
     
                       ( "begin proc (:1, :2); end;" ) ;

     CallableStatement cs4 = conn.prepareCall
     
                       ( "begin :1 := func(:2,:3); end;" ) ;

Just to check, can you return ordinary types from a stored procedure, like this:

SQL:

FUNCTION balance (acct_id NUMBER) RETURN NUMBER IS
  acct_bal NUMBER;
BEGIN
  SELECT bal INTO acct_bal FROM accts
    WHERE acct_no = acct_id;
  RETURN acct_bal;
END;

JAVA:

CallableStatement cstmt = conn.prepareCall("{? = CALL balance(?)}");
cstmt.registerOutParameter(1, Types.FLOAT);
cstmt.setInt(2, acctNo);
cstmt.executeUpdate();
float acctBal = cstmt.getFloat(1);


This is an Oracle example that I have hacked slightly for your purposes to use as an example of a callablestatement that actually returns a resultset:

import java.sql.*;                                        
import java.io.*;

class RefCursorExample
{
   public static void main(String args[]) throws SQLException
   {
      //Load the driver.
      DriverManager.registerDriver(new yourdriver);

      // Connect to the database.
      // You can put a database name after the @ sign in the connection URL.
      Connection conn =
         DriverManager.getConnection(your connection string);
                                                            // line 16
      // Create the stored procedure.
      init(conn);

      // Prepare a PL/SQL call.                                line 20
      CallableStatement call =
         conn.prepareCall("{ ? = call java_refcursor.job_listing (?) }");
         // or conn.prepareCall("{ :1 = call java_refcursor.job_listing (:2) }");

      // Find out who all the sales people are.                line 24
      call.registerOutParameter(1, java.sql.Types.OTHER);
      call.setString(2, "SALESMAN");
      call.execute();
      ResultSet rset = (ResultSet)call.getObject(1);
      // I think getObject and casting is preferable to getResultset()...

      // Output the information in the cursor.                 line 30
      while (rset.next())
         System.out.println(rset.getString("ENAME"));
   }

// Utility function to create the stored procedure
                                                            // line 36
   static void init(Connection conn) throws SQLException
   {
      Statement stmt = conn.createStatement();
                                                            // line 40
      stmt.execute("CREATE OR REPLACE PACKAGE java_refcursor AS " +
                   " type myrctype is ref cursor return EMP%ROWTYPE; " +
                   " function job_listing(j varchar2) return myrctype; " +
                   "end java_refcursor;");
                                                            // line 45
      stmt.execute("CREATE OR REPLACE PACKAGE BODY java_refcursor AS " +
                   " function job_listing(j varchar2) return myrctype is " +
                   "    rc myrctype; " +
                   " begin " +
                   "    open rc for select * from emp where job = j; " +
                   "    return rc; " +
                   " end; " +
                   "end java_cursor;");                     // line 53
   }
}


If this does not work replace:

      CallableStatement call =
         conn.prepareCall("{ ? = call java_refcursor.job_listing (?) }");

with
      CallableStatement call =
         conn.prepareCall("{ call java_refcursor.job_listing (?) }");
and
      call.execute();
      ResultSet rset = (ResultSet)call.getObject(1);
with
      ResultSet rset = call.executeQuery();

(you now no longer need to do
  call.registerOutParameter(1, java.sql.Types.OTHER);
hopefully avoiding the spectre of incompatable types that seems to be plaguing you)

to see if your JDBC driver can return the ResultSet implicitly as a return type rather than a specific IN/OUT parameter.

ALSO

Variations on the syntax of your stored function to try and coax out the ResultSet as a return type are:

create or replace function sp_ListEmp return types.cursortype
as
    l_cursor    types.cursorType;
begin
    open l_cursor for select ename, empno from emp order by ename;
    return l_cursor;
end;

or even

create or replace function sp_ListEmp
as
begin
   select ename, empno from emp order by ename;
end;

These two variations may be more conducive to returning resultsets as opposed to the:

return myrctype is rc myrctype;

form of the return.

Other than this, I can only suggest asking JDataConnect what's going on, as their may be some trickery needed to get the ResultSet out of their driver when using Oracle.

Hope this helps...

Pfff, I tried all the good things you give to me, and it doesn't work.
I think it's due to the JDBC driver I use.
So here is my last question :
If I use this driver it's because I'm building an intranet app doing connections to databases. And I don't want to install softs on the clients. The clients download an applet that does connections to a server using the JDBC driver (a Server part of the driver runs on the server side) : it's a JDBC type 3 driver that provides zero install on client workstations.
Does the driver you use allows to do the same?
If yes, where can I found this driver.
If no, do you know a driver that allows it and runs with Oracle?

PS : The advantage of JDataConnect is that there is no limit on the number of client connections to the server, the prices are good (if you have free drivers it's better =) ), it's compatible with JDBC2, it's easy to use.

Thank you very much.

Your problem seems to be acombination of ODBC and the JDataConnect driver. I really can't find any support on JDataConnect specifically but do you have a support contract with them so that they can help you with this?

Other drivers of note are the Oracle drivers and details are here:

http://wpi.wpi.edu/java.815/a64685/overvw3.htm#1000908

Is there any reason in particular why you cannot use the Oracle JDBC thin driver? (also no client install)

As for other drivers, well take your pick...

http://java.sun.com/products/jdbc/drivers.html

or

http://kraken.csi1.com/installation/jdbc.html

I can't really express any particular preference on this large list of suppliers, though IBM Alphaworks may have a free driver available - woth cjecking out.