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
Joe Weinstein - 28 Aug 2005 18:22 GMT
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));
> 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
[quoted text clipped - 52 lines]
>
> thank you for help
Virgil Green - 30 Aug 2005 22:54 GMT
> 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
[quoted text clipped - 47 lines]
>
> thank you for help
I'm just thinking on-line here... try
String usersSql = "{ call ? = cursors_pkg.getdirect( from_city ?, to_city
?) }";
CallableStatement stmt = db.prepareCall(usersSql);
stmt.registerOutParameter(1,OracleTypes.CURSOR);
stmt.setString(2,"London");
stmt.setString(3,"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));
}
based on the premise that you only want to substitute the data via the
parameters, not the parameter names.
A little further looking on the net tells me that the use of named
parameters is part of JDBC 3.0 and older drivers may not support the use of
named parameters.

Signature
Virgil