Call a stored procedure via JDBC

[Bechir Bejaoui]

Hi folks

I need to call a stored procedure from within the java code, I encountred the following error
ORA-00900: invalid SQL statement

I created a procedure that works perfectly I tested it in TOAD

  CREATE OR REPLACE PROCEDURE sp_test 
  (
     firstnumber IN NUMBER ,
     secondnumber IN NUMBER,
     res OUT NUMBER 
  )
AS
BEGIN     
     BEGIN 
             res :=firstnumber + secondnumber;
             --DBMS_OUTPUT.PUT_LINE(res);
     END;
END;

This above procedure executes perfecty under toad

BEGIN 
DECLARE 
  res NUMBER;
  BEGIN
      sp_test(20,30,res);
  END;
END;

IT gives me res = 50 under toad

Now here is the code in java

import java.sql.*;
public class Test {
	public static void main(String[] args)  {
	   try
	   {
		   Class.forName("oracle.jdbc.driver.OracleDriver");
	   }
	   catch(ClassNotFoundException caught)
	   {
		 System.out.println("Driver not found");  
	   }
	   String uri ="jdbc:oracle:thin:@//localhost:1521/XE";
	   String user ="HR";
	   String password ="test";
	   try {
		      Connection connection = DriverManager.getConnection(uri,user,password);
	          String procedure = "{sp_test(?,?,?)}";
	          int result = 0;
	          CallableStatement stmt = connection.prepareCall(procedure);
	          stmt.setInt(1, 20);
	          stmt.setInt(2, 30);
	          stmt.setInt(3, result);
	          boolean flag = stmt.execute(procedure);
	          System.out.println(flag);
	          System.out.println(result);
	          
	        } catch (SQLException e) {
		      // TODO Auto-generated catch block
		      System.out.println(e.getMessage());
	    }
	   
	   
	}
}

This code gives me error exactly at this line

boolean flag = stmt.execute(procedure);

So how to fix that

Thank you

[amit punekar]

Hello,
It seems you are not registering the out parameter.
Please check the javadocs and modify the code accordingly and it should work then.

regards,
Amit

[Ishan Pandya]

String s="{call getTestData(?, ?)}"; //you have not used "call" to call procedure..

and OUT should be registered as amit said.
refer to this link Callable Statement