6 Replies Latest reply: Dec 11, 2012 8:37 AM by Hoek RSS

    how to escape special word from col.

    947771
      Hi

      i have a two tables ,each one has varchar2 as column name,
      they are giving error while selecting.

      so please suggest me correct way of correcting the error.

      select a.varchar2, b.varchar2 bvarchar2
      from t a
      join t1 b on a.id=b.id

      yours sincerely

      Edited by: 944768 on Dec 11, 2012 5:54 AM
        • 1. Re: how to escape special word from col.
          jeneesh
          Better rename the column..
          • 2. Re: how to escape special word from col.
            Hoek
            one solution please suggest me is correct and also tel me if any good way of correcting the error.
            It is not correct to use reserved words/keywords as column names. It is confusing and will lead to errors.
            Also:
            If you query V$RESERVED_WORDS then you'll find out:
            SQL> select keyword, reserved from v$reserved_words where keyword = 'VARCHAR2'
              2  /
            
            KEYWORD                        R
            ------------------------------ -
            VARCHAR2                       Y
            
            1 row selected.
            Where RESERVED indicates whether the keyword cannot be used as an identifier (Y) or whether the keyword is not reserved (N)
            http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_2134.htm#REFRN30204
            • 3. Re: how to escape special word from col.
              947771
              but problem is i have used it

              so please suggest me how can i show data if possible else i have to change the name.

              yours sincerely

              Edited by: 944768 on Dec 11, 2012 5:56 AM

              Edited by: 944768 on Dec 11, 2012 5:57 AM
              • 4. Re: how to escape special word from col.
                BluShadow
                944768 wrote:
                but problem is i have used it

                so please suggest me how can i show data if possible else i have to change the name.
                So change the name now, before it creates more problems for everybody who has to use your database design.
                • 5. Re: how to escape special word from col.
                  Veejays.User10302525-Oracle
                  create table t1("varchar2" number);
                  
                  insert into t1 values(10);
                  
                  select * from t1;
                  
                  select "varchar2"  from t1;
                  You should not have used it, since you have made the mistake correct it now, better now than a lot of issues later.
                  • 6. Re: how to escape special word from col.
                    Hoek
                    You can show data by using double quotes (quoted identifiers), but it's nothing but a cumbersome hack, your collegues will not like you for using a reserved word as a column name, there will be problems and errors sooner or later and they are forced to use quoted identifiers, which is kind of 'silly' (that's an understatement).

                    Just rename it asap (ALTER TABLE ... RENAME COLUMN ...):
                    SQL> create table t ("varchar2" varchar2(10));
                    
                    Table created.
                    
                    SQL> insert into t values ('bla');
                    
                    1 row created.
                    
                    SQL> select * from t;
                    
                    varchar2
                    ----------
                    bla
                    
                    1 row selected.
                    
                    SQL> select varchar2 from t;
                    select varchar2 from t
                           *
                    ERROR at line 1:
                    ORA-00936: missing expression
                    
                    
                    SQL> select "varchar2" from t;
                    
                    varchar2
                    ----------
                    bla
                    
                    1 row selected.
                    
                    SQL> alter table t rename column "varchar2" to my_string;
                    
                    Table altered.
                    
                    SQL> select * from t;
                    
                    MY_STRING
                    ----------
                    bla
                    
                    1 row selected.
                    
                    SQL> select my_string from t;
                    
                    MY_STRING
                    ----------
                    bla
                    
                    1 row selected.
                    And never ever use a reserved word as an identifier again...