2

Can JDBC statement run explain plan on query string?

The code throws SQL exception Error message: Incorrect syntax near the keyword 'plan'. Stacktrace is null

I just copy from internet of using stmt.execute. However, it seems that stmt.execute() only Returns true if the first result is a ResultSet object; false if it is an update count or there are no results 

conn = getEntityManager().unwrap(java.sql.Connection.class);
stmt = conn.createStatement();         
stmt.execute("explain plan for SELECT   1 from Dual"); // throws sql exception
rs = stmt.executeQuery("select plan_table_output from table(dbms_xplan.display())");
while (rs.next()) 
{
  System.out.println(rs.getString(1));
}
ITpeople
  • 29
  • 1
  • 5
  • 1
    I don't think it will work, but you might be able to write a stored procedure which gets a text output from `EXPLAIN` on the database, and then call that proc from your Java code. – Tim Biegeleisen Oct 10 '19 at 09:42
  • 1
    Please post the stacktrace of the thrown exception. – Mark Rotteveel Oct 10 '19 at 09:57
  • Yes that should work - how else would JDBC based SQL clients be able to do that? What exactly is the exception you get? –  Oct 10 '19 at 10:28
  • @a_horse_with_no_name What makes you think JDBC-based SQL clients *can* do that? – user207421 Oct 10 '19 at 11:43
  • 1
    @user207421 Because Oracle's own GUI client ("SQL Developer") is based on Java using JDBC. And I am using another JDBC based client that can do that. And because I have also done it myself through plain JDBC –  Oct 10 '19 at 12:18
  • I just copy from internet of using stmt.execute. However, it seems that stmt.execute() only Returns true if the first result is a ResultSet object; false if it is an update count or there are no results – ITpeople Oct 11 '19 at 01:57
  • @ITpeople You need to read the API documentation of `Statement`. When the return value is `true`, you need to call `getResultSet()`, otherwise `getUpdateCount()`, process that result, then call `getMoreResults()` (where the returned boolean has the same meaning), do this until you get false **and** `getUpdateCount()` returns `-1`. See also my answer [here](https://stackoverflow.com/a/14829425/466862). – Mark Rotteveel Oct 11 '19 at 08:31
  • error message: Incorrect syntax near the keyword 'plan'.\n stacktrace is null – ITpeople Oct 14 '19 at 02:12
  • @MarkRotteveel: `execute("explain plan for SELECT 1 from Dual");` does not return anything in Oracle. The code in the question should work, there is no need to check the return code of the `execute()` call (It seems the code was copied directly from [my answer here](https://stackoverflow.com/a/4376639/330315)). –  Oct 14 '19 at 06:44
  • @a_horse_with_no_name You're right, I was thinking of database systems where the plan is returned as a result set. – Mark Rotteveel Oct 14 '19 at 11:39
  • what if the DB is sybase? – ITpeople Oct 17 '19 at 06:59

0 Answers0