2

I need to parse execution plans for queries and extract data out of them for my application. While this answer JDBC Oracle - Fetch explain plan for query helped me fetch a text execution plan through jdbc, it is not possible to parse the text output as the queries are runtime params. Below mentioned sample query returns the xml in "Sql Developer" but NOT through jdbc. I am using Oracle 11g XE with ojdbc6.jar driver. Why is this query not working via the jdbc api? Any help will be appreciated.

EXPLAIN PLAN SET STATEMENT_ID = '123' FOR select * from jobs where rownum < 3 order by job_id desc

SELECT dbms_xplan.build_plan_xml(statement_id => '123') AS XPLAN FROM dual;

Below is my java code:

    Driver d = (Driver) Class.forName("oracle.jdbc.OracleDriver").newInstance();
    Connection conn = d.connect("jdbc:oracle:thin:username/password@//localhost:1521/XE", new Properties());
    Statement statement1 = conn.createStatement(); 
    statement1.execute("explain plan set STATEMENT_ID = '"+ id + "' for " + query);
    statement1.executeQuery("select dbms_xplan.build_plan_xml(statement_id => '"+ id + "') AS XPLAN FROM dual");
    ResultSet resultSet = statement1.getResultSet();            
    while (resultSet.next()) {
        executionPlan = resultSet.getSQLXML(1).getString();
    }
Community
  • 1
  • 1
Snoopy
  • 37
  • 1
  • 9

1 Answers1

2

I don't think the result is returned as a "real" JDBC XML type.

Try

resultSet.getString(1)

instead

If that doesn't work, try also to cast the result to a clob:

  executeQuery("select to_clob(dbms_xplan.build_plan_xml(....

That definitely will require you to use getString(1) instead of getSQLXML()

  • using getString(1) returns a null value while using to_clob gives the following error in both jdbc and Sql Developer <![CDATA[ORA-00904: "OTHER_XML": invalid identifier]]> – Snoopy Aug 31 '11 at 14:23
  • But that error message means, that it basically worked and some other error occurred while running dbms_xplan.build_plan_xml –  Aug 31 '11 at 14:44
  • 1
    @user812236, can you show the exact statement you used when trying the TO_CLOB approach? `select to_clob(dbms_xplan.build_plan_xml(statement_id => '123')) AS XPLAN FROM dual` worked fine for me in SQL Developer. – Dave Costa Aug 31 '11 at 16:04
  • The problem seemed to be with my Plan Table. Dropping and recreating the table worked for me. Now when I run select to_clob(dbms_xplan.build_plan_xml(statement_id => '123')) AS XPLAN FROM dual I am getting the plan both through jdbc and through Sql Developer. Thanks for the help. – Snoopy Sep 01 '11 at 07:07
  • FYI to all who read this - I had errors with large execution plans using this example (`to_clob(...)`) until I switched to `select dbms_xplan.build_plan_xml(statement_id => '123').getclobval() AS XPLAN FROM dual` – Jake Feasel Mar 12 '12 at 22:20