Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle procedure works in direct SQL but not via Java

 
Bob Grossman
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've created an Oracle stored procedure called recalc_1_student_grades_v1. When I use sqlplus to connect to the database, I can run the procedure just fine:



However, when my Java program calls the database to run the exact same command, I get an SQLException:



The code that actually calls the database, tryUpdate(), is:



What am I doing wrong?
 
Koen Aerts
Ranch Hand
Posts: 344
Java Linux Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Instead of executing the Stored Procedure via EXECUTE or CALL in the SQL String, try to execute it via a java.sql.CallableStatement instead.
 
Ireneusz Kordal
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bob Grossman wrote:

The code that actually calls the database, tryUpdate(), is:

so
What am I doing wrong?


The error message says, that the SQL passed to the aStatement.execudeUpdate is wrong (that is, SQL from the 'doThis' string)..
Since you didn't show us what you are passing to this string, it's hard to guess what exactly is wrong.

 
Bob Grossman
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, Koen, your solution worked.

For anyone else reading this, the syntax of the call to the procedure is not obvious. Note the curly braces in the SQL. You use setInt(), setString(), etc. methods to set the values where the question marks go:


 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bob Grossman wrote:Thanks, Koen, your solution worked.

For anyone else reading this, the syntax of the call to the procedure is not obvious. Note the curly braces in the SQL. You use setInt(), setString(), etc. methods to set the values where the question marks go:



Though really not obvious, it is actually documented in the CallableStatement javadoc.

Just for completeness, the curly braces are "translated" by the JDBC driver into proper syntax needed to call stored procedures in the database supported by the driver. As the exact syntax differs across databases, this allows to use database-agnostic code when calling stored procedures.

Nothing actually prevents you to use real target database syntax. In your case you'd replace the curly braces and the enclosed text with begin recalc_1_student_grades_v1(?, ?); end - rest of the code would remain the same. This does not bring you anything actually and just makes the code database dependent, but sometimes you can use this trick to use other database dependent feature that is not directly supported by JDBC, such as the returning clause in Oracle's DML statements, which can be quite useful in putting a sequence number into a new record and returning it to the application in one go (among other uses).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic