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:
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.
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):
Joined: Feb 06, 2008
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?
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)