Re: jdbc + CallableStatement +Incorrectly registered parameters

Here's code that works. It uses
a procedure rather than a function.
Joe Weinstein at BEA

      Statement st = c.createStatement();

      st.executeUpdate (
        "   CREATE OR REPLACE PROCEDURE joeproc(    "
        + "                  data_list IN OUT REF CURSOR)"
        + " IS                                          "
        + " BEGIN                                       "
        + "   OPEN data_list FOR                        "
        + "   SELECT * FROM DUAL;                       "
        + " END  joeproc;                               "
      );

      CallableStatement cs = c.prepareCall("{ call joeproc(?)}");

      cs.registerOutParameter(1,OracleTypes.CURSOR);
      cs.execute();
      ResultSet rs = (ResultSet)(cs.getObject(4));

piernik wrote: 
Hello I'm trying to learn how to work with stored procedures in Oracle and
java. I deal fine with ResultSets and PreparedStatement now I want to use
functions and CallableStatement.
I wrote my code looking at this sites:

http://andrej.racchvs.com/archives/2003/10/29/using-oracle-ref-cursors-in-java/
http://www.samspublishing.com/articles/article.asp?p=26251&seqNum=7
http://www.cs.bris.ac.uk/maintain/OracleDocs/java.816/a81354/samapp2.htm
http://www.enterprisedt.com/publications/oracle/result_set.html

I've created a package with a function to find buss connection between 2 cities
CREATE OR REPLACE PACKAGE cursors_pkg
AS type dire_city_curs is record (
bus_id Bus_Schedule.bus_id%TYPE,
station_name Station.station_name%TYPE, arrival_tim Bus_Schedule.arrival_time%TYPE,
departure_time Bus_Schedule.departure_time%TYPE,
station_nr Bus_Schedule.station_nr%TYPE);
TYPE refcursortype IS REF CURSOR return dire_city_curs;
FUNCTION getdirect(from_city varchar2, to_city varchar2) RETURN refcursortype;
END cursors_pkg;

CREATE OR REPLACE PACKAGE BODY cursors_pkg IS
FUNCTION getdirect(from_city varchar2, to_city varchar2) RETURN refcursortype
IS
mycursor refcursortype;
BEGIN
OPEN kurszor FOR
select **bla bla** RETURN mycursor;
END;
END cursors_pkg; 

now java

String usersSql = "{ call ? = cursors_pkg.getdirect(?,?) }";
CallableStatement stmt = db.prepareCall(usersSql);
stmt.registerOutParameter(1,OracleTypes.CURSOR);
stmt.setString(new String("from_city"),"London"); stmt.setString(new String("to_city"),"Paris");

stmt.execute();
ResultSet rset = (ResultSet) stmt.getObject(1);
//or ResultSet rset = ((OracleCallableStatement)stmt).getCursor (1);
while (rset.next()){ System.out.println( rset.getDouble(1) +" "+ rset.getString(2));
}
and the effect of my efforts:
SQLException: Incorrectly set or registered parameters.:null I'm puzzled. I don't understand what goes wrong. Can you give me any hints?
I was sent to
http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm
but I find there nothing interesting.

thank you for help 

.