2 Replies Latest reply on Oct 3, 2014 9:54 AM by Arpod

    [4.0.3] ORA-00904 - Can't view tables where column names are reserved words

    S-Max

      Hi all,

       

      although I have found an existing thread to the same problem ([4.0] Can't view tables where column names are reserved words), I start the new one, because the last post in that thread is from Jeff Smith "always start a new thread".

       

      So I have the same problem like Arpod in the last version of sql developer [4.0.3] - I can't view tables in the data tab where column names are reserved words.

       

      Hier is the create table statement

       

      create table MYTEST

      (ID NUMBER,

      "ALTER" NUMBER);

       

      I use SQL Developer Version 4.0.3.16, Java(TM)-Plattform1.7.0_55, DB is Oracle 11gR2 Enterprise Edition 64-bit.

       

      With SQL Developer Version 3.2.20.09, Java(TM)-Plattform1.6.0_29 I have no problems!

       

      Best regards

        • 1. Re: [4.0.3] ORA-00904 - Can't view tables where column names are reserved words
          Gary Graham-Oracle

          Running your test case with the "ALTER" column name, this is what I see when running worksheet queries in 4.0.3:

          drop table MYTEST;

          create table MYTEST

          (ID NUMBER,

          "ALTER" NUMBER);

           

          select ID, "ALTER" from mytest;

          select ID,  ALTER  from mytest;

          produces this output...

          table MYTEST dropped.

          table MYTEST created.

          no rows selected

           

          Error starting at line : 7 in command -

          select ID,  ALTER  from mytest

          Error at Command Line : 7 Column : 13

          Error report -

          SQL Error: ORA-00936: missing expression

          00936. 00000 -  "missing expression"

          *Cause:   

          *Action:

          Turning to SQL*Plus...

          hr@XE> drop table MYTEST;

           

          Table dropped.

           

          hr@XE> create table MYTEST

            2  (ID NUMBER,

            3  "ALTER" NUMBER);

           

          Table created.

           

          hr@XE>

          hr@XE> select ID, "ALTER" from mytest;

           

          no rows selected

           

          hr@XE> select ID,  ALTER  from mytest;

          select ID,  ALTER  from mytest

                      *

          ERROR at line 1:

          ORA-00936: missing expression

          So, in general, we can conclude Oracle requires that reserved names be quoted -- nothing new there.  SQL Developer is consistent with SQL*Plus behavior.  When you say...

          I can't view tables in the data tab where column names are reserved words.

          ...are you referring to a worksheet query result (with the ORA-00936 message) or to opening a browser on a table object from the Connection navigator (with the ORA-00904: <column_name>:invalid identifier" error mention in the other discussion).  In my testing, I do not see ORA-00904 when opening a browser.

           

          Regards,

          Gary

          SQL Developer Team

          • 2. Re: [4.0.3] ORA-00904 - Can't view tables where column names are reserved words
            Arpod

            I have to say that my (same) problem was resolved in 4.0.1, resurfaced in 4.0.2 and was resolved again in 4.0.3.

            Since it doesn't work for you, and some people were not able to repeat it, my best guess would be to try and upgrade your Java version - I upgraded mine every time I upgraded SQLDev version. Currently, I have 1.7.0.67.

             

            Also, if that doesn't work, you should try to trace your SQLDev session and then tkprof the results - that way, you can see the exact query SQLDev is making and deduce why that happens. See message #13 in the thread you mentioned in your OP. In my case, when reserved-word-named column was uppercase, it was lowercased but still double-quoted which caused the error.