JDBC & oracle function

[Srinivasa Raghavan]

I have a oracle function that takes a parameter ( IN Parameter ) and returns a Oracle Cursor object as a result set .

The question is how to get the ResultSet from a Oracle function.
I mean what number should i give while registering the output parameter.
My java code goes like this..

 
 CallableStatement cstmt =  connection.prepareCall("{ call test(?)}");
 cstmt.registerOutParameter(???,Types.INTEGER); // What shd i give here.
 cstmt.execute(); // Execute the callable statement
 someObj = cstmt.getObject(???); 

Thanks
Srini

[Shailesh Chandra]

When you are registering your out parameter register your out parameter as

cstmt.registerOutParameter(???,oracle.jdbc.driver.OracleTypes.CURSOR);

then retrieve your cursor by getobject and typecast same as a resultset

then onwards you can treat your resultset however you like

[Srinivasa Raghavan]

Originally posted by Shailesh Chandra:
When you are registering your out parameter register your out parameter as

cstmt.registerOutParameter(???,oracle.jdbc.driver.OracleTypes.CURSOR);

then retrieve your cursor by getobject and typecast same as a resultset

then onwards you can treat your resultset however you like

I'm sorry there was a typo .. actually in my code i have registered as a Cursor .. but while posting here there was some Cut & Copy error's .. sorry..

Srini

[Shailesh Chandra]

I think you are looking for this

CallableStatement cs;
// Call a function with one IN parameter; the function returns a Cursor
cs = connection.prepareCall("{? = call myfuncin(?)}");

// Register the type of the return value
cs.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);

// Set the value for the IN parameter
cs.setString(2, "a string"); //or cs.set___(2,your Data type)

// Execute and retrieve the returned value
cs.execute();

ResultSet rs = (ResultSet) cs.getObject(1);

[Srinivasa Raghavan]

Originally posted by Shailesh Chandra:
I think you are looking for this

CallableStatement cs;
// Call a function with one IN parameter; the function returns a Cursor
cs = connection.prepareCall("{? = call myfuncin(?)}");

// Register the type of the return value
cs.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);

// Set the value for the IN parameter
cs.setString(2, "a string"); //or cs.set___(2,your Data type)

// Execute and retrieve the returned value
cs.execute();

ResultSet rs = (ResultSet) cs.getObject(1);

Yes the Same ..This is my doubt.
But Why you are registering the return value first & the first IN parameter as the second one..

Normally what we do is the first In Parameter will be registered something like

cs.setString(1, "a string"); // 1st param
cs.setString(2, "a string"); // 2nd param if it's there.

Srini

[Shailesh Chandra]

Srini ,

I am not 100% upto it might be some one else in forum will put better light on it.

but whenever you will call a function, it will return a value and to retrieve a value we always declare a variable first then we call a function.

if you call this function in your oracle procedure you will write in something like this

your_cursor = call function (arguement)

in java you replace your variables by "?"

but syntax remain same.

[Srinivasa Raghavan]

Ok .. Anyway thanks for the input...
Srini