Oracle WITH clause returns no data

I am trying to use a WITH clause in Oracle, but it is not returning any data.

This is the query I am trying to run...

with test as 
 (select count(*)  
 from my_table)
select *
from test;

When开发者_如何学C I run this code, I get back the count of the records in my_table

select count(*)  
 from my_table

I am on Oracle 10g so the query should work...

select * from v$version;

yields

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Solaris: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

Could it a permissions issue or something?

*EDIT: *

I believe my question is clear. Using the WITH statement will not return any records for me, even though the "select count(*) from my_table" statement inside the WITH statement works correctly, which would lead me to believe that there is another issue that I am unable to figure out, hence this question :)

EDIT 2

OK, so if I try and execute the query from a linked server from SQL server management studio I get some error information back:

sg 7357, Level 16, State 2, Line 1 Cannot process the object "with test as (select count(*) from v$version) select * from test;". The OLE DB provider "MSDAORA" for linked server "MyServer" indicates that either the object has no columns or the current user does not have permissions on that object.

---

Maybe the optimizer is materializing the count query (dumb, I agree). It's a shot in the dark but do you have these privileges?

• grant query rewrite to youruser;
• grant create materialized view to youruser;

---

Try giving the aggregate an alias name.

with test as 
 (select count(*) as MyCount 
 from my_table)
select MyCount
from test;

---

The following worked just fine for me (10gR2)

SQL> with test as
  2   (select count(*)
  3   from user_tables)
  4  select *
  5  from test;

  COUNT(*)
----------
       593

SQL> 

What client are you using?

---

This question is confusing. Are you saying you are or are not getting back the count from my_table?

You should be getting back the count because that's exactly what you asked for in the with clause.

It's analogous to writing:

select * from (select count(*) from my_table);

---

Some people at my company ran into this the other day - we traced it down to the Oracle client version [and thus the OCI.dll] version that was being picked up by PL/SQL developer. Some of our dev PCs had Oracle 8 (!) client installs still knocking around on them as well as more recent versions.

The symptom was that not only were queries written using a WITH clause returning no rows, they were returning no columns either! If you manually set the app to pick up the Oracle 11 oci.dll then it all worked.

I think what is going on is that Oracle 8 predates the WITH clause (introduced in Oracle 9, and enhanced subsequently). Now, mostly you can get different versions of the Oracle client and server to talk to one another. However because the client has a certain amount of 'intelligence', it is supposed to be semi-aware of what sort of operation it is submitting to the database, and so does some form of primitive parse of the SQL. As it doesn't recognize the command as a SELECT, it treats it as some unknown command [e.g. possibly a DDL command] and doesn't recognize it as returning a resultset. If you turn on SQL_TRACE for the session you can see the SQL gets PARSEd and EXECUTEd fine on the server, but that no calls to FETCH are made.

I had a similar thing myself recently when trying to use the new WITH syntax in Oracle 12 that allows an inline function definition. If you try simple examples using an Oracle 11 thick client-based application, such as PL/SQL developer or SQL*Plus, then you get an error. If you use an Oracle 12 client, or a thin-client application that doesn't rely a client-side install, then it works.