Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • 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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic