Laurent Schneider

using trunc and round I cannot get lower than -4800 nor higher than 10001

SQL> select trunc(date '-4712-1-1','CC') from dual;
TRUNC(DATE'-4712-1-1','CC')
----------------------------------
Thursday 1st January 4800 B.C.

SQL> select round(date '9999-01-01','CC') from dual;
ROUND(DATE'9999-01-01','CC')
----------------------------------
Monday 1st January 10001 A.D.

Blogroll, sql

Is there any rule where you can use scalar subquery?
You can use a scalar subquery expression in most syntax that calls for an expression (expr).

Well, you cannot use it as the second argument of sys_connect_by_path

select sys_connect_by_path(ename,
   (select '/' from dual)) from emp 
   connect by prior empno=mgr;
*
ERROR at line 1:
ORA-30003: illegal parameter in SYS_CONNECT_BY_PATH 
function

You cannot use it in the ITERATE or in the RULES clause of model

SQL> select * from dual model 
dimension by (0 x) measures (0 y) 
rules iterate ( (select 1 from dual) ) (y[0]=0);
*
ERROR at line 1:
ORA-32607: invalid ITERATE value in MODEL clause
SQL> select * from dual model 
dimension by (0 x) measures (0 y) 
(y[0]=(select 1 from dual));
*
ERROR at line 1:
ORA-32620: illegal subquery within MODEL rules

Also impossible is in the DATAOBJ_TO_PARTITION function that is used in System Partitioning :

SQL> insert into t partition (
  dataobj_to_partition("T",
    (select :partition_id  from dual) )) 
values ('x') ;
*
ERROR at line 1: 
ORA-14198: rowid column must refer to table 
specified in 1st parameter 

Another documented limitation is the GROUP BY clause :

SQL> SELECT (SELECT COUNT(*) FROM EMP), 
  COUNT(*) FROM DEPT ;
*
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> SELECT (SELECT COUNT(*) FROM EMP), COUNT(*) 
  FROM DEPT GROUP BY (SELECT COUNT(*) FROM EMP);
*
ERROR at line 1:
ORA-22818: subquery expressions not allowed here

SQL> SELECT (SELECT COUNT(*) FROM EMP), 
  COUNT(*) FROM DEPT GROUP BY ();

(SELECTCOUNT(*)FROMEMP)   COUNT(*)
----------------------- ----------
                     14          4

Blogroll, sql

I made a funny typo today
SQL> select * from t1;

SQL> select 1.x from t1;

Should not I get ORA-904 invalid identifier ?

Blogroll, sql

Eddie Awad just tagged me.

1) I was born in 1971 and have a younger brother who will get married this year
2) I was for the first time in San Francisco in 1986
3) In 1990 I visited India
4) In 1991 I had a motorbike accident where I was quite lucky to break only two arms and one leg.
5) I have learnt to play Chinese chess in Vietnam in 2000. I am able to speak some Vietnamese
6) My family-in-law live in the Bakossi mountains in Cameroon
7) My kids are 4 and 5 years old and speak Swiss German better than me
My grand-parents live in their house in Jura. My grand-father is 95 and my grand-mother is 92, I love my grand-mother very much and we just opened a bottle of Champagne last Sunday

My turn to tag - in alphabetic order descending
Chen Shapira
Alan Nolan Davies
Tom Kyte
Kamus
Lutz Hartmann
Marco Gralike
Nicolas Gasparotto
Steven Feuerstein

blog, orablog-tag, personal

The 11g OCP certification should be available this year. You can register for the production exam 1Z0-050 New Features on Prometrics and pass the exam on Mon Feb 18th or later, the OCA exams 1Z1-051 SQL Fundamentals and 1Z1-052 Admin I are in beta and the OCP exam 1Z1-053 Admin II is planned for early 2008 (well, the OCA exams are planned for late 2007, so do not rely on this timeframe).
Check the official page :
Oracle Education 11g certification

The 11g OCM is planned for a later date (whatever that means). Well, I am no longer going to wait for the 10g OCM upgrade for 9i OCM… I am not going to do a certification for an older release since 11g exams are available. I will rather do the 11g ocp this year. As I wrote earlier, the 10g OCM was planned for late 2004…

11g, certification

I wish all my readers a very successful 2008

personal