This week's book giveaway is in the JavaScript forum.
We're giving away four copies of Svelte and Sapper in Action and have Mark Volkmann on-line!
See this thread for details.
Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Oracle stored procedures

 
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!
 
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!
 
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).]
 
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
 
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
 
Water! People swim in water! Even tiny ads swim in water:
the value of filler advertising in 2020
https://coderanch.com/t/730886/filler-advertising
    Bookmark Topic Watch Topic
  • New Topic