Home » SQL & PL/SQL » SQL & PL/SQL » Column name starting with numeric value (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Column name starting with numeric value [message #600752] Tue, 12 November 2013 01:23 Go to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
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
Re: Column name starting with numeric value [message #600758 is a reply to message #600752] Tue, 12 November 2013 02:21 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Hi,

your initial assumption is not correct - Oracle object naming rules allow to start with a numeric character. They are described in SQL Language Reference, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
For 11gR2, the relevant chapter is placed here: http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements008.htm#SQLRF51129
SQL> create table tst( "10" integer );

Table created.

Anyway, for your own convenience, you should not use such names even in PIVOT queries and start using aliases.
Re: Column name starting with numeric value [message #600762 is a reply to message #600758] Tue, 12 November 2013 02:58 Go to previous message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member


Hi flyboy

Thanks,Really this forum is very helpful to learn new concepts.

Thanks
Previous Topic: Need SQL query help
Next Topic: parallel_enable aggregate in Pacakged Function
Goto Forum:
  


Current Time: Fri Apr 26 14:07:57 CDT 2024