• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

getting the resultset of a stored procedure?

 
Ranch Hand
Posts: 204
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am learning stored procedures but I am having trouble getting the data

Here is my stored procedure




The stored procedure called proc1
I am using oracle sql developer.

My java code snippet to call the stored procedure is



But when I try to get the result set it gives me null.

I guess I want to know how I can have a stored procedure return stuff like resultsets back to java code .

Thanks for any help
 
author
Posts: 32
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How are you executing the call? For a proc that returns one result set, you should use executeQuery():
See http://download.oracle.com/javase/tutorial/jdbc/basics/sql.html and read the notes about which execute method to use for other types of procs.

By the way, I hope your procedure is just an example. I strongly recommend against writing a stored procedure that simply concatenates a table name and runs the query. You're creating a blatant SQL injection hole, because the caller could pass anything, including a subquery:
In fact, a procedure just like your example is featured on TheDailyWTF:
http://thedailywtf.com/Articles/For-the-Ease-of-Maintenance.aspx
 
Sam Doder
Ranch Hand
Posts: 204
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
yes , this is just an example I know that it has security issues.

I have always used just preparedStatements instead of callableStatements and just used strings with ? ? and set the values of the fields... Then executeQuery or Update.

But now using stored procedures I am curious will the executeQuery , executeUpdate return a vaild ResultSet I was getting Null when I used execute(). But maybe execute() function was the problem.

Also execute is the only way to go if I have multiple update / queries in my stored procedure.
What I am unsure about is how to set the stored procedure to return a ResultSet if I use execute().
Or what other function would you use other then execute() if you had to multiple sql update / query statements in your stored procedure?

I know execute returns a Object then I should beable to cast it to a resultset provided it is a result set.
The problem is how do I get the stored procedure to return a resultset.

NOTE for my example above I would just use the executeQuery to get the result set I believe but just imagine a huge stored procedure.

Thanks

 
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Sam Doder wrote:
I guess I want to know how I can have a stored procedure return stuff like resultsets back to java code .



Try something like this (tested on Oracle 11g2):


Results:
 
Sam Doder
Ranch Hand
Posts: 204
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
ok thanks.

But I am curious you use OracleCallableStatement

Is their away that you can just use the CallableStatement object in the standard JDBC class.

Because using oracles function is not as portable i.e ... oracle.jdbc.OracleTypes.CURSOR

When I try to use registerOutParameter from a CallableStatement statement I don't know what the parameter in place of oracle.jdbc.OracleTypes.CURSOR should be?

Question 2

I can from a JDBC Connection use a statement , preparedStatement , or CallableStatement.

Correct me if I am wrong.
But the statement cann't have ? in it .. It must be a Hardcoded SQL statement (fix).
PreparedStatements extend the statement and provide a way to dynamically customize the query string using ? ? ? ...etc
They say it is precompiled.

CallableStatement is what I am alittle confused about it extends PreparedStatement but it is used to call a stored procedure on the database.

What is the benifits of using this over just setAutoCommit(false) for the connection and then use a whole bunch of preparedStatements with java if statement (for the equivalent logic of pl/sql) then commit it or rollback. Wouldn't it have the same amount of functionality and work the same way as just creating a stored procedure on the database and calling it with a CallableStatement?

Even if the stored procedure is huge and contains alot of DDL and DML statements I would think you could do the equivalent with a ton of preparedstatements. Is their some kind of performance/memory space benfit to using one over the other?
Or is this just a matter of preference. Because I would imaging the difference is weather you want to write the code in PL/SQL call it thru java or just write it in java using SQL query's.

Seems cleaner (provided you know pl/sql) to compile the stored procedurce on the database it self and just call the procedure using callable statements thru java. Plus if you do it this way you are running the queries at the database level and not thru the java code if their is a benifit to that in any way? (maybe speed???)

Thanks for any info

Is their somewhere I can get a good tutorial on pl/sql and stored procedures ... i.e a list of all the possible key words and how to use them (hyperlink please)





 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic