How to execute a procedure with JDBC

Question

This is my function :

public static void execute_delete_on_db(String pass, String login, String port,
        String host, String table_name, String file_path) throws Exception {

    Class.forName("oracle.jdbc.OracleDriver");
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//"
            + host + ":" + port + "/xe", login, pass);

    PreparedStatement statement = null;

    Statement stmt = null;
    String query = "delete from AA_ALL";
    stmt = conn.createStatement();
    stmt.executeQuery(query);
    sql_statement.close();
    conn.commit();
    conn.close();
}

At least this is the most important part of my function. Above code works fine. I tried to execute my procedure by calling this sql statement :

  EXECUTE delete_all_rows_from_table('all');

In Java it looked like this :

String query = "EXECUTE delete_all_rows_from_table('all')";

On the database it is working fine, but in Java this is giving me the error. Can you tell me what am I doing wrong?

Solution 1

You can call your procedure and not execute it like so :

String query = "{call delete_all_rows_from_table(?)}"; 
CallableStatement statement = connection.prepareCall(query);  
statement.setString(1, "all");  
statement.execute(); 

You can learn more here : JDBC CallableStatement – Stored Procedure OUT parameter example and JDBC Stored Procedure

Solution 2

Notice if the procedure got some out parameters, you would have to register the parameters.

Here is an example, assuming we already import the modules (java.sql.CallableStatement and java.sql.Types) and the connection has been established.

CallableStatement callStmt = conn.prepareCall("{CALL <PROCEDURE_NAME>(?, ?, ?)}");
callStmt.setString(1, data);
callStmt.registerOutParameter(2, Types.VARCHAR);
callStmt.registerOutParameter(3, Types.VARCHAR);
callStmt.executeQuery();

String outParameter1 = callStmt.getString(2);
String outParamenter2 = callStmt.getString(3);