Did you ever think you could write something nobody can read ?

Submit your query before April 1st to the Oracle SQL Obfuscation Contest…

You can also vote there

My query : http://www.oraclecommunity…7305

I wrote it already :
Laurent Schneider » Blog Archive » TO_CHAR(…, ‘D’)

I thought the one I found out this night was fun

select to_char(sysdate,'DAY',
  'NLS_DATE_LANGUAGE=''numeric date language''') 
from dual;
T
-
3

[edit]this feature is not documented, do not use in prod[/edit]

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.

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

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

SQL> select 1.x from t1;

Should not I get ORA-904 invalid identifier ?

I read a user question on forums.oracle.com this morning :
As we know NOT EQUAL operations by pass indexes and cause full table scans in queries

I did not know that. Do I need to use hints?

set autotrace traceonly explain
create table t as 
select 
  sign(rownum-1) r, rpad(rownum,4000,'.') t 
from dual connect by level<10000/*00*/;
create index i on t(r);
exec dbms_stats.gather_table_stats(user,'T',cascade=>true)

select /*+ INDEX(T,I) */ r,t from t where r!=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3993571787

--------------------------------------------------------------
----------------------

| Id  | Operation                   | Name | Rows  | Bytes | C
ost (%CPU)| Time     |

--------------------------------------------------------------
----------------------

|   0 | SELECT STATEMENT            |      |     1 |  4004 |
1979   (2)| 00:00:24 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |  4004 |
1979   (2)| 00:00:24 |

|*  2 |   INDEX FULL SCAN           | I    |     1 |       |
1979   (2)| 00:00:24 |

--------------------------------------------------------------
----------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R"<>1)

SQL> select r,t from t where r!=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------
------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)
| Time     |

--------------------------------------------------------------
------------

|   0 | SELECT STATEMENT  |      |     1 |  4004 |   271K  (1)
| 00:54:18 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |  4004 |   271K  (1)
| 00:54:18 |

--------------------------------------------------------------
------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R"<>1)

If I need to generate 3 rows called AAA, BBB, CCC, I could use dual and union all. Another method is to use Extensibility Types

select * from table
   (sys.ODCIVarchar2List('AAA','BBB','CCC'));

COLUMN_VALUE
------------
AAA
BBB
CCC

I read the ultimate excuse database and checked if I can use model to do generate random statements:

with t as (
  select '%E and %E are travelling to %L.' fmt 
  from dual union all
  select 'About %N employees live in %L.' 
  from dual)
select str from t
model
reference
  dept on (
    select loc,count(*) over () c,rownum r 
    from dept)
  dimension by (r) measures (loc,c)
reference
  emp on (
    select ename,count(*) over () c, rownum r 
    from emp)
  dimension by (r) measures (ename, c) 
partition by (fmt)
dimension by (1 x) 
measures (cast(fmt as varchar2(4000)) str)
rules 
  iterate (1000) 
  until str[1] not like '%/%%' escape '/'
(str[1]=substr(str[1],1,
  decode(instr(str[1],'%'),0,4000,
    instr(str[1],'%')-1))||
  decode(substr(str[1],nullif(
    instr(str[1],'%'),0)+1,1),
    'N',to_char(trunc(
      dbms_random.value(0,1+emp.c[1]))),
    'E',initcap(emp.ename[trunc(
      dbms_random.value(1,1+emp.c[1]))]),
    'L',initcap(dept.loc[trunc(
      dbms_random.value(1,1+dept.c[1]))]))||
    substr(str[1],nullif(
      instr(str[1],'%'),0)+2))
/
STR
----------------------------------------
James and Ward are travelling to Dallas.
About 1 employees live in Chicago.
/
STR
--------------------------------------------
King and Blake are travelling to New York.
About 3 employees live in Dallas.

Is rownum=1 the first row returned? well, it depends :

select rownum, ename 
from emp 
order by ename;
    ROWNUM ENAME
---------- ----------
        11 ADAMS
         2 ALLEN
         6 BLAKE
         7 CLARK
        13 FORD
        12 JAMES
         4 JONES
         9 KING
         5 MARTIN
        14 MILLER
         8 SCOTT
         1 SMITH
        10 TURNER
         3 WARD

The ROWNUM is evaluated before the order by

select rownum 
from emp 
group by rownum 
having rownum between 3 and 6;
    ROWNUM
----------
         6
         5
         3
         4

the rownum is selected before the GROUP BY and before the HAVING.

select 
   rownum, 
   sal, 
   median(sal) over () 
from emp;
    ROWNUM        SAL MEDIAN(SAL)OVER()
---------- ---------- -----------------
         1        800              1550
        12        950              1550
        11       1100              1550
         3       1250              1550
         5       1250              1550
        14       1300              1550
        10       1500              1550
         2       1600              1550
         7       2450              1550
         6       2850              1550
         4       2975              1550
         8       3000              1550
        13       3000              1550
         9       5000              1550

The optimiser may chose to resort the result, here as a WINDOW SORT operation for the MEDIAN analytic function

you want to BIT_OR multiple rows. For example you have a table with 3 rows that you want to aggregate with BIT_OR

1010 (10)
1100 (12)
0110 (6)
=========
1110 (14)

Let’s try

with t as (
  select 10 n from dual union all 
  select 12 from dual union all 
  select 6 from dual) 
select 
  utl_raw.cast_to_binary_integer(
    sys.mvaggrawbitor(
      utl_raw.cast_from_binary_integer(
        n
      )
    )
  ) N 
from t;
  N
---
 14

It is that easy !

disclaimer: mvaggrawbitor is not documented