Hi All
As per the oracle rules column name of field can't start with Numeric value .It will give the following error message .
create table NUMERIC_COLUMN_NAME
(
JOB VARCHAR2(9),
10 NUMBER,
20 NUMBER,
30 NUMBER,
60 NUMBER,
80 NUMBER,
90 NUMBER,
100 NUMBER
)
ORA-00904: : invalid identifier
If we change the column name then its works fine .
SQL> create table NUMERIC_COLUMN_NAME
2 (
3 JOB VARCHAR2(9),
4 C10 NUMBER,
5 C20 NUMBER,
6 C30 NUMBER,
7 C60 NUMBER,
8 C80 NUMBER,
9 C90 NUMBER,
10 C100 NUMBER
11 );
Table created
But by using pivot concept oracle is allowing us to create the table with numeric column name .
SQL> drop table numeric_column_name ;
Table dropped
SQL> create table numeric_column_name as
2 SELECT *
3 FROM (SELECT job, deptno, sum(sal) sal
4 FROM SCOTT.emp
5 GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN(10,
6 20,
7 30,
8 60,
9 80,
10 90,
11 100));
Table created
SQL> desc numeric_column_name
Name Type Nullable Default Comments
---- ----------- -------- ------- --------
JOB VARCHAR2(9) Y
10 NUMBER Y
20 NUMBER Y
30 NUMBER Y
60 NUMBER Y
80 NUMBER Y
90 NUMBER Y
100 NUMBER Y
Any feature has to be accepted from all possible scenarios. Why this facility is restricted to only some concepts(just like pivot).
Could any body please explain the logic behind this?
Thanks
Revathi.T