File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Oracle procedure works in direct SQL but not via Java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle procedure works in direct SQL but not via Java" Watch "Oracle procedure works in direct SQL but not via Java" New topic
Author

Oracle procedure works in direct SQL but not via Java

Bob Grossman
Ranch Hand

Joined: Dec 18, 2008
Posts: 69
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

Joined: Feb 07, 2012
Posts: 344

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

Joined: Jun 21, 2008
Posts: 423
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

Joined: Dec 18, 2008
Posts: 69
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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle procedure works in direct SQL but not via Java