• Post Reply Bookmark Topic Watch Topic
  • New Topic

JDBC & oracle function

 
Srinivasa Raghavan
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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..


Thanks
Srini
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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



Srini
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok .. Anyway thanks for the input...
Srini
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!