PL/SQL call from JDBC resulting in "SQLSyntaxErrorException: ORA-00900"

I'm trying to use the following code to run a PL/SQL statement on my database server.

public class Main {
    public static void main(String[] args) {
        String jdbcURL = "jdbc:oracle:thin:@172.22.88.9:1521/xavier.i.com";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        String user = "bigdb";
        String password = "fakepassword";
        String result = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
            conn = DriverManager.getConnection(jdbcURL, user, password);

            result = runStatement(conn,
              "{begin ANALYZE TABLE BIGDB.scr_fct_exact_access
              ESTIMATE STATISTICS; end}");

        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            ex.printStackTrace();
        }
    }

    private static String runStatement(Connection con, String statement)
      throws Exception {
        PreparedStatement cstmt = con.prepareStatement(statement);
        cstmt.execute(); // this is line 58
        cstmt.close();

        return count;
    }
}

On running the code, I get the following exception:

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)

at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)Time : -1307015416548

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)

开发者_JAVA百科at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)

at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)

at Main.prepareStatement(Main.java:58)

at Main.main(Main.java:29)

What is going wrong, and how can I fix it?

---

ANALYZE TABLE is a DDL (data definition language) statement. It's not valid within a BEGIN/END block in PL/SQL. If you wanted to execute it in PL/SQL, you'd need to use EXECUTE IMMEDIATE.

To run the statement from JDBC, just create a Statement instance and use executeUpdate:

Statement stmt = con.createStatement();
stmt.executeUpdate("ANALYZE TABLE BIGDB.scr_fct_exact_access ESTIMATE STATISTICS");
stmt.close();

But a even better idea is to use the DBMS_STATS package to analyze your tables. Then you can use your approach with BEGIN/END.

---

Use CallableStatement instead of PreparedStatement to run statements with the PL/SQL block syntax from JDBC.

However, as Codo has stated in the other answer, ANALYZE TABLE isn't exactly callable from PL/SQL. It is a SQL command, and hence cannot be executed in a PL/SQL block. And as rightly suggested, DBMS_STATS ought to be used. This is also stated in the documentation of the ANALYZE command:

Note:

For the collection of most statistics, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. See Oracle Database PL/SQL Packages and Types Reference for more information on the DBMS_STATS package. Use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer:

• To use the VALIDATE or LIST CHAINED ROWS clauses

• To collect information on freelist blocks

If you really want to avoid using DBMS_STATS (and honestly, I wouldn't see a good reason to avoid it), use EXECUTE IMMEDIATE or DBMS_SQL to execute the ANALYZE TABLE command.