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

Oracle stored procedures

Francisco I
Ranch Hand

Joined: Mar 27, 2001
Posts: 44
Hi everybody.
How can I capture the result set returned by an oracle stored procedure that performs a query? Is it Possible?
How can I capture return values and output variables from an Oracle Stored Proc?
Thanks!
prabhat kumar
Ranch Hand

Joined: Apr 11, 2001
Posts: 114
u can do something like this
suppose you want to call the following stored function, which returns the balance of a specified bank account:
FUNCTION balance (acct_id NUMBER) RETURN NUMBER IS
acct_bal NUMBER;
BEGIN
SELECT bal INTO acct_bal FROM accts
WHERE acct_no = acct_id;
RETURN acct_bal;
END;

From a JDBC program, your call to the function balance might look like this:
CallableStatement cstmt = conn.prepareCall("{? = CALL balance(?)}");
cstmt.registerOutParameter(1, Types.FLOAT);
cstmt.setInt(2, acctNo);
cstmt.executeUpdate();
float acctBal = cstmt.getFloat(1);

hopw it will help
Prabhat
Francisco I
Ranch Hand

Joined: Mar 27, 2001
Posts: 44
Thank you. Now, if I want to return a result set, for example: I want to get all balances with a query like
SELECT * FROM BALANCES
what would the function look like?, how do I capture the results?
Thanks!
pisal rahul
Greenhorn

Joined: Mar 02, 2001
Posts: 5
do the following
CallableStatement stmt = con.prepareCall("{? = call procedurename(?)}");
stmt.registerOutParameter(1,OracleTypes.CURSOR);
stmt.setString(2,"Rahul");
stmt.executeUpdate();
ResultSet rs = (ResultSet)stmt.getObject(1);
then display the resultset
[This message has been edited by pisal rahul (edited May 01, 2001).]
sandhya menon
Ranch Hand

Joined: May 22, 2001
Posts: 37
Dear pisal
Can u please specify this code more elaborately.Thsi is quite intersting.
Thanks Sandhya
Laurent Leonard
Ranch Hand

Joined: May 15, 2001
Posts: 35
Just some oracle advices about this :
- don't use * in a select (what happen in your program if the table structure changes) (select col1, col2 from owner.table)
- use a fully qualified name for the table ex. : OWNER.TABLE

Laurent LEONARD
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle stored procedures