Laurent Schneider

Hi Laurent,

Either you are very very tired, or I’ve missed something ?!
select
user,
count(*)
from
user_objects
where
status=’INVALID’
group by user

Disconnected from Oracle 12g Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Expensive option :)))))

That could probably a reason

Hi Leo,

Yes he missed that… but wouldn’t you expect Oracle 12g WITH expensive option to think for you?

Laurent,

I love the version banner, I take it that’s hacked and not a beta version your working with.

The “Expensive Option” bit is particularly amusing.

Thanks,
Alan…

Ah is this an April Fools Day prank?

Hey Laurent,

You forgot:
alter session set automatic_group_clause = true;

PS. I’ve been wanting that alter session command since Oracle 7.3!

Gareth

what’s wrong ?
well, beside “Oracle 12g …”
it is wrong that you would never get
ORA-00937: not a single-group group function

probably if you would ask for
1 select
2 object_type,
3 count(*)
4 from
5 user_objects
6 where
7 status=’INVALID’

but not if you asked for
1 select
2 user,
3 count(*)
4 from
5 user_objects
6 where
7 status=’INVALID’

happyu April’s fools day !

Obviously you are stuck on the 12g beta release, it is a well known bug, you should apply the bug fix #20090401 or wait for the next patchset !

Hey Laurent, what the Sum of Ten + Five? Please put easier spam protection, took me 15 minutes to figure this out

Your post made my day!

worked for me after I applied the patch.

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 1 09:58:46 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle 12g Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Expensive option

SQL> select
2 user,
3 count(*)
4 from
5 user_objects
6 where
7 status=’INVALID’;

Query finished, retrieving results…
USER COUNT(*)
—————————— ————————————–

0 row(s) retrieved

Disconnected from Oracle 12g Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Expensive option

Laurent Schneider :
happy april fool of course
The query is perfectly fine to count all invalid objects in the current schema. The error and banner were fake of course
…
Adding a GROUP BY clause will suppress the output when no invalid object exists…
…

em ?
sorry, but I don’t understand it does not work for me:

sql > select user,count(*) from dual group by user;

USER COUNT(*)
—————————— ———-
SOKRATES 1

sql > select count(*) from dual where dummy=’X';

COUNT(*)
———-
1

sql > select user,count(*) from dual where dummy=’X’ group by user;

USER COUNT(*)
—————————— ———-
SOKRATES 1

sql > select user,count(*) from user_objects where status=’INVALID’;

USER COUNT(*)
—————————— ———-
SOKRATES 2

sql > select user,count(*) from user_objects where status=’INVALID’ group by user;

USER COUNT(*)
—————————— ———-
SOKRATES 2

something wrong with my database ?

I was shocked since I understood the USER is gathered from my session info

nice one Laurent

@Laurent Schneider

Great example why SQL is sometimes so misleading, Chris Date would have loved this example.

What do you want today?

“1 row selected” or “no rows selected”.

Great example as a basis for a “Nullology” / “SQL is bad” discussion…