Can we call sql function in prepared statement?

Question

I have a oracle function. function name is TESTFUNCTION. The code i used is

PreparedStatement pStmt = con.prepareStatement("SELECT dbo.TESTFUNCTION(?,?)") ;

and I set the parameters also.

ResultSet rs = pStmt.executeQuery();

This code will produce the below exception

java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

Any Help!!

Answer 1

You are missing the FROM keyword. Change to this:

PreparedStatement pStmt = con.prepareStatement("SELECT dbo.TESTFUNCTION(?,?) FROM dual;") ;

Answer 2

A PreparedStatement is useful for SQL queries and not for function call. For function calls and stored procedures, use CallableStatement.

Answer 3

This error occurs when you try to execute a SELECT statement, and you either missed or misplaced the FROM keyword. Try to check for the from keyword.

Check here for details.

Note: It would be preferrable to use Callable Statement instead of prepared statement in such cases.

You could use callable statement in this case as:

String SQL = "{call dbo.TESTFUNCTION(?,?)}";
   cstmt = con.prepareCall (SQL);