• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle stored procedures

 
Francisco I
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 114
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear pisal
Can u please specify this code more elaborately.Thsi is quite intersting.
Thanks Sandhya
 
Laurent Leonard
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic