Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Using a number as the lead character in a field name

    We're trying to decide if there's a problem with using field names for tables which lead with a number, like

    Code:
    CREATE TABLE junk (12x NUMBER);
    It turns out that you have to surround the name in quotes to get it to work:

    Code:
    CREATE TABLE junk ("12x" NUMBER);
    But, at query time, you don't have to include those quotes. I thought perhaps it was going to behave like Oracle does when you use a reserved word, but it doesn't:

    Code:
    SQL> CREATE TABLE junk ("12x" NUMBER, "COMMENT" NUMBER);
    
    Table created.
    
    SQL> select * from junk;
    
    no rows selected
    
    SQL> select 12x, 12X from junk;
    
    no rows selected
    
    SQL> select COMMENT from junk;
    select COMMENT from junk
           *
    ERROR at line 1:
    ORA-00936: missing expression
    
    
    SQL> select "COMMENT" from junk;
    
    no rows selected
    
    SQL> select 12x, 12X, "COMMENT" from junk;
    
    no rows selected
    So, at first glance, it doesn't look like it's going to be a problem. Is there something that I've overlooked?

    Thanks,
    Chuck

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Code:
    SQL> select 12x from dual;
    
             X
    ----------
            12
    It seems that Oracle treats '12x' it as numeric literal followed by column alias, even if there is no space between leading digits and following alphanumeric characters.
    It has no relation with the column name anyway - you always have to use double quotes to reference it.
    In my opinion, it is not a good idea to use such column names.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by flyboy
    In my opinion, it is not a good idea to use such column names.
    I'd like to second that! Just don't do it...

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    I should have inserted some values before querying the "JUNK" table. Thanks for taking it that extra step. --=cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •