Oracle stored procedures

[Francisco I]

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]

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]

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]

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]

Dear pisal
Can u please specify this code more elaborately.Thsi is quite intersting.
Thanks Sandhya

[Laurent Leonard]

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