I noticed this in Oracle 11. However, I do not think it is a bug as the same thing happens in Oracle 9. I joined two SELECT * statements with a UNION and added an ORDER BY at the end. This gave me an ORA-00904. It seemed to me that the ORDER BY did not know the names of the columns in the output from the SELECT *:
SQL> create table andrew (col1 number)
2 /
Table created.
SQL> create table john (col1 number)
2 /
Table created.
SQL> select *
2 from user_tables
3 where table_name = 'JOHN'
4 union
5 select *
6 from user_tables
7 where table_name = 'ANDREW'
8 order by table_name
9 /
order by table_name
*
ERROR at line 8:
ORA-00904: "TABLE_NAME": invalid identifier
SQL>
I found the following workarounds. One of them may be suitable if you hit this problem yourself. I used SELECT TABLE_NAME instead of SELECT *:
SQL> select table_name
2 from user_tables
3 where table_name = 'JOHN'
4 union
5 select table_name
6 from user_tables
7 where table_name = 'ANDREW'
8 order by table_name
9 /
TABLE_NAME
------------------------------
ANDREW
JOHN
SQL>
I removed the ORDER BY. For the purposes of this example, I also added SET HEAD OFF and removed some blank lines from the output:
SQL> set head off
SQL> select *
2 from user_tables
3 where table_name = 'JOHN'
4 union
5 select *
6 from user_tables
7 where table_name = 'ANDREW'
8 /
ANDREW
USERS
VALID 10
1 255 65536
1 2147483645
YES N
1 1 N ENABLED
NO N N NO DEFAULT DISABLED NO
NO DISABLED YES
DISABLED DISABLED
NO NO
JOHN
USERS
VALID 10
1 255 65536
1 2147483645
YES N
1 1 N ENABLED
NO N N NO DEFAULT DISABLED NO
NO DISABLED YES
DISABLED DISABLED
NO NO
SQL>
I looked at the USER_TABLES view, saw that TABLE_NAME was the first column and used ORDER BY 1 instead:
SQL> desc user_tables
Name Null? Type
-------------------------- -------- ------------------
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30)
IOT_NAME VARCHAR2(30)
STATUS VARCHAR2(8)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
DEGREE VARCHAR2(10)
INSTANCES VARCHAR2(10)
CACHE VARCHAR2(5)
TABLE_LOCK VARCHAR2(8)
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
IOT_TYPE VARCHAR2(12)
TEMPORARY VARCHAR2(1)
SECONDARY VARCHAR2(1)
NESTED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
ROW_MOVEMENT VARCHAR2(8)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
SKIP_CORRUPT VARCHAR2(8)
MONITORING VARCHAR2(3)
CLUSTER_OWNER VARCHAR2(30)
DEPENDENCIES VARCHAR2(8)
COMPRESSION VARCHAR2(8)
COMPRESS_FOR VARCHAR2(18)
DROPPED VARCHAR2(3)
READ_ONLY VARCHAR2(3)
SQL> select *
2 from user_tables
3 where table_name = 'JOHN'
4 union
5 select *
6 from user_tables
7 where table_name = 'ANDREW'
8 order by 1
9 /
ANDREW
USERS
VALID 10
1 255 65536
1 2147483645
YES N
1 1 N ENABLED
NO N N NO DEFAULT DISABLED NO
NO DISABLED YES
DISABLED DISABLED
NO NO
JOHN
USERS
VALID 10
1 255 65536
1 2147483645
YES N
1 1 N ENABLED
NO N N NO DEFAULT DISABLED NO
NO DISABLED YES
DISABLED DISABLED
NO NO
SQL>
I put the UNION inside brackets and put the ORDER BY outside the brackets:
SQL> select * from
2 (select *
3 from user_tables
4 where table_name = 'JOHN'
5 union
6 select *
7 from user_tables
8 where table_name = 'ANDREW')
9 order by table_name
10 /
ANDREW
USERS
VALID 10
1 255 65536
1 2147483645
YES N
1 1 N ENABLED
NO N N NO DEFAULT DISABLED NO
NO DISABLED YES
DISABLED DISABLED
NO NO
JOHN
USERS
VALID 10
1 255 65536
1 2147483645
YES N
1 1 N ENABLED
NO N N NO DEFAULT DISABLED NO
NO DISABLED YES
DISABLED DISABLED
NO NO
SQL>
