aspose file tools*
The moose likes JDBC and the fly likes JDBC & oracle function Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC & oracle function" Watch "JDBC & oracle function" New topic
Author

JDBC & oracle function

Srinivasa Raghavan
Ranch Hand

Joined: Sep 28, 2004
Posts: 1228
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


Thanks & regards, Srini
MCP, SCJP-1.4, NCFM (Financial Markets), Oracle 9i - SQL ( 1Z0-007 ), ITIL Certified
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

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


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Srinivasa Raghavan
Ranch Hand

Joined: Sep 28, 2004
Posts: 1228
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

Joined: Aug 13, 2004
Posts: 1081

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

Joined: Sep 28, 2004
Posts: 1228
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

Joined: Aug 13, 2004
Posts: 1081

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

Joined: Sep 28, 2004
Posts: 1228
Ok .. Anyway thanks for the input...
Srini
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC & oracle function