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/2...rsors-in-java/ > http://www.samspublishing.com/articl...26251&seqNum=7 > http://www.cs.bris.ac.uk/maintain/Or...54/samapp2.htm > http://www.enterprisedt.com/publicat...esult_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/tec...s/jdbc_faq.htm > but I find there nothing interesting.
>
> 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.
--
Virgil
==========https://forums.oracle.com/forums/thread.jspa?threadID=2153866
