Execute a PL/SQL procedure using Java code

Question

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

Peter Gibbons · Accepted Answer · 2016-04-17 12:30:16Z

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

krokodilko · Accepted Answer · 2016-04-17 12:33:51Z

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();

dsp_user · Accepted Answer · 2016-04-17 18:39:28Z

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.

Note that this only works if ProjetIRSTEA.detectionX is a function. Procedures cannot be invoked from a SELECT statement.

Collectives™ on Stack Overflow

Execute a PL/SQL procedure using Java code

3 Answers 3

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Related