I would like to execute PL/SQL procedure using Java code. I've tried this so far :
Statement myStmt = null;
myStmt = conn.createStatement();
myStmt.executeQuery("EXECUTE ProjetIRSTEA.detectionX");
And I get the following error message:
java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
You have to use the CallableStatement class to execute Stored Procedures.
Please, check this sample published by Oracle to see how to use this class: https://docs.oracle.com/cd/A84870_01/doc/java.816/a81354/samapp2.htm
Try:
myStmt.executeUpdate("BEGIN ProjetIRSTEA.detectionX; END");
You can also use a method of calling stored procedures defined by the JDBC standard, using CallableStatement Interface:
CallableStatement myCall = connection.prepareCall("{call ProjetIRSTEA.detectionX()}")
myCall.executeUpdate();
On Oracle, you can use either a CallableStatement (as explained above) or just issue a normal sql query using a Statement or PreparedStatement (the former method is preferred though) .
String sql = " select ProjetIRSTEA.detectionX() from dual";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.execute();
Note that you have to reference the system table dual in your select statement.
ProjetIRSTEA.detectionX is a function. Procedures cannot be invoked from a SELECT statement.