Invalid sql statement for "Explain plan for with" statement

Question

I am executing the following query:

explain plan for  
with Foo   
as (  
     select * from my_table 
   )  
select * from Foo  
where x = 7;

This results in an invalid SQL statement exception in Oracle10g. Without the explain plan the with statement executes correctly. Why does this error occur within the context of SQL Developer?

Is this complete query? When i run this using my table on Oracle it works without any error. — rs., Jan 17 '13 at 16:39
@rs that is the complete query. Sql Developer tosses an exception to me. — Woot4Moo, Jan 17 '13 at 16:40
What version of SQL Developer are you using? It works for me on 3.2.20.09. — Jon Heller, Jan 17 '13 at 19:16

score 1 · Answer 1 · answered Jan 17 '13 at 16:33

1

You have an extra parentheses:

explain plan for  
with Foo   
as (  
     select * from my_table)  -- < remove this
   )  
select * from Foo  
where x = 7;

answered Jan 17 '13 at 16:33

Taryn

242,637
56
362
405

score 0 · Answer 2 · answered Jan 17 '13 at 18:09

Not sure about this nuance but there is another way to write that will explain.

Select *
  From (
        Select * From my_table
       ) Foo
 Where Foo.x = 7;

I stay away from with clauses because they don't port well and in my experience don't get evaulated by the optimizer as well as dynamic from clauses, but those are older experiences.

Invalid sql statement for "Explain plan for with" statement

2 Answers2