• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

JDBC & oracle function

 
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok .. Anyway thanks for the input...
Srini
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic