1 2 Previous Next 17 Replies Latest reply: May 6, 2014 7:17 AM by Jeff Smith Sqldev Pm-Oracle RSS

    [4.0] Can't view tables where column names are reserved words

    Arpod

      If a table contains any column, which name is the same as reserved word (like "level", "index", "connect" etc.), then "Table->Data" tab fails to open with "ORA-00904: <column_name>:invalid identifier" error.

      I know that having reserved words as column names is "wrong", but it's way too late to change our design, and it's still a rather serious bug that needs addressing.

       

      I use SQLDev 4.0.0.13.80, Java 1.7.0_45 and our DB is Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 64bit.

        • 1. Re: [4.0] Can't view tables where column names are reserved words
          rp0428

          How were those tables created?

           

          I just tested on vanilla 10.2.0.1.0 and can't create a table using those column names:

          create table test_reserved_words1 (level number, index number, connect number)

          SQL> create table test_reserved_words1 (level number, index number, connect numb
          er)
            2  /
          create table test_reserved_words1 (level number, index number, connect number)
                                             *
          ERROR at line 1:
          ORA-00904: : invalid identifier


          SQL>

          If I put quotes around the column names and make them case sensitive the table gets created and I can use the 'data' tab just fine in sql developer Version 4.0.0.13 Build MAIN-13.80

           


          • 2. Re: [4.0] Can't view tables where column names are reserved words
            Arpod

            Thanks for your reply.

             

            How were those tables created?

             

            Can't test it right now, but using double quotes around column names allows it, if I remember correctly.

            create table test_reserved_words1 ("level" number, "index" number, "connect" number); should work.

             

            I remember accessing the same table just fine with SQLDeveloper 3.2. If it actually works on 4.0, then the only possible difference I can think of is that I switched to OCI/Thick driver to access the DB. Will test further once I get to work at monday.

            • 3. Re: [4.0] Can't view tables where column names are reserved words
              rp0428
              Can't test it right now, but using double quotes around column names allows it, if I remember correctly.

              That is what I said; that does work to create the table AND view it, and the data tab, in sql developer.

              • 4. Re: [4.0] Can't view tables where column names are reserved words
                Arpod

                OK, it still doesn't work for me. Let me go through the steps once more.

                 

                I create the table with

                CREATE TABLE "Z_TEST" ( "CONNECT" VARCHAR2(1024 BYTE) );

                , then go to navigator and select that table.

                "Columns" tab opens normally, but "Data" tab opens empty, with grayed-out refresh/insert/other buttons.

                "Messages - Log" shows "ORA-00904: "connect": invalid identifier".

                The same thing works fine in 3.2. In 4.0 it doesn't work with thin nor OCI drivers.

                Even if I enable the debug configuration, it shows no additional info at all when that error happens.

                 

                The only suspicious thing I noticed after enabling debug is this message:

                фев 03, 2014 11:47:38 AM oracle.javatools.db.ora.OracleDatabaseDescriptor getDatabaseCharset

                WARNING: Could not load CharacterSet for ID 196

                which appears once per connection when I open any table for the first time. It appears even if the data tab is loading normally, though, so I have no idea if it's related at all. Our DB charset is CL8KOI8R, if that matters.

                • 5. Re: [4.0] Can't view tables where column names are reserved words
                  rp0428

                  Those steps still work for me on vanilla 10g with character set WE8MSWIN1252.

                   

                  Able to insert a row just fine.


                  • 6. Re: [4.0] Can't view tables where column names are reserved words
                    Arpod

                    I think I've found the cause. This error appears only if created column name wasn't all lowercase. So, if I create table with

                     

                    CREATE TABLE "Z_TEST" ( "connect" VARCHAR2(1024 BYTE) ); 
                    

                     

                    then "DATA" tab works just fine. Actually, if I create the table with uppercase-named column and then execute

                     

                    select "connect" from Z_TEST;
                    


                    it gets me the same ORA-00904. Moreover, judging by error that appears at "Data" tab, sqldeveloper does an internal query using lowercase column name.

                    • 7. Re: [4.0] Can't view tables where column names are reserved words
                      rp0428
                      I think I've found the cause. This error appears only if created column name wasn't all lowercase.

                      The cause of 'what'? The 'problem' you reported doesn't appear at all for me: the 'data' tab always opens. This works just fine and it is not 'all lowercase'.

                      • CREATE TABLE "Z_TEST" ( "coNNect" VARCHAR2(1024 BYTE) );  

                      As for that last statement - well now you are just being ridiculous:

                      Actually, if I create the table with uppercase-named column and then execute 

                       

                      select "connect" from Z_TEST; 

                      select "connect" from Z_TEST;

                      it gets me the same ORA-00904. Moreover, judging by error that appears at "Data" tab, sqldeveloper does an internal query using lowercase column name.

                      Of course you get that exception. Using double-quotes makes the column name case-sensitive so there is no column named "connect" if you create the table 'with uppercase-named column'.

                       

                      And of course sqldeveloer 'does an internal query using lowercase' when you use double-quotes for the same reason.

                       

                      You still haven't posted ANYTHING that can be reproduced by anyone else.

                      • 8. Re: [4.0] Can't view tables where column names are reserved words
                        Arpod

                        I would appreciate less offensive tone, please. If you can't reproduce the bug, it doesn't mean it's nonexistent. No matter how many times you repeat that it "works fine" for you, the bug is still there for me. Looking away and crossing your fingers won't fix it, alas.

                         

                        I was able to reproduce the same ora-00904 on "Data" tab for the fresh install of SQLDev 4.0 on another PC with different OS (Win XP). That excludes the possibility of it being the side-effect of migration from 3.2, running SQLDev on Linux or using OCI instead of JDBC.

                         

                        And of course sqldeveloer 'does an internal query using lowercase' when you use double-quotes for the same reason.

                        Now you are being ridiculous yourself. What do you mean "of course"!? If my column is named 'CONNECT', in uppercase, why should sqldeveloper select "connect" and not "CONNECT"? And before you say that "of course it doesn't": the message I get after entering the tab reads, literally:

                         

                        ORA-00904: "connect": invalid identifier
                        
                        

                         

                        and that message keeps the case of the query. If the query was made with uppercase column name, 'CONNECT', and it didn't exist, the message would look like this instead:

                         

                        ORA-00904: "CONNECT": invalid identifier
                        
                        

                         

                        You can check that yourself.

                         

                        I've also traced my session and parsed it with TKProf:

                         

                        The following statement encountered a error during parse:

                         

                        SELECT ROWID "ROWID", "connect" "CONNECT" FROM "MYSCHEMA"."Z_TEST

                         

                        Error encountered: ORA-00904

                         

                        See?  Even though the column is actually named "CONNECT", SQLDev does a query with "connect".

                        If that's not enough proof that bug exists, then I honestly have no idea what else to say.

                         

                        UPD: Now this is getting weird. If I name the column "connect", "Connect", "CONNect", in whatever mixed case - everything works fine. Once I name it "CONNECT", I get the error.

                        • 9. Re: [4.0] Can't view tables where column names are reserved words
                          rp0428
                          If you can't reproduce the bug, it doesn't mean it's nonexistent

                          Which is EXACTLY why I said I can't reproduce it and didn't say it was nonexistent.

                           

                          You have been lax in not posting the EXACT commands you are executing and the exact results that you get.

                           

                          Even your last post that I replied to said this:

                          I think I've found the cause. This error appears only if created column name wasn't all lowercase.

                          But then you did NOT post that example that you said caused the error. Instead you posted this:

                          • CREATE TABLE "Z_TEST" ( "connect" VARCHAR2(1024 BYTE) );

                          and told us that it does NOT cause the error. To try to reproduce your problem we need the EXACT code you are using that DOES cause the error.

                           

                          My comment about you being ridiculous was because you then also posted this:

                          Actually, if I create the table with uppercase-named column and then execute

                           

                          1. select "connect" from Z_TEST; 
                          select "connect" from Z_TEST;

                          it gets me the same ORA-00904. Moreover, judging by error that appears at "Data" tab, sqldeveloper does an internal query using lowercase column name.

                          You create an 'uppercase-named column' and then wonder why you can't select a column using totally different name: a lower-case case-sensitive name.

                           

                          That is the same as saying: I created a table named Z_TEST with a column named 'A' and get an exception when I say 'select B from Z_TEST. Well of course you do: there is no column named B in Z_TEST.

                           

                          Create a column named CONNECT (uppercase be default since no quotes were used) and you cannot query a column named "connect" (lower-case case-sensitive name). Those names are just as different as if you had used A and B.

                           

                          When you enclose a column/table name in double quotes sql developer MUST USE that same exact string and MUST USE double quotes when sending the query to the server. Your use of double quotes indicates a CASE-SENSITIVE name so sql developer must also use those double quotes.

                          See?  Even though the column is actually named "CONNECT", SQLDev does a query with "connect".

                          No - I do NOT see. Which is why I have been trying to tell you that you need to post the ENTIRE DDL and DML that you are using.

                           

                          How do I know the column is 'actually named "CONNECT"? You didn't post the DDLd for the table you are using for this example.

                           

                          I can sympathesize that  a problem of this nature can be frustrating but it is imperative that any attempt to help you be based on FACT and not just a statement about what you think/know you did.

                           

                          I've tried to replicate your problem using different OSs, different OS versions, different DB versions and different sql developer versions. I can't replicate it.

                          Once I name it "CONNECT", I get the error.

                          And again - you TELL us that but you don't post the EXACT DDL that you used. Are you back to what you said at the beginning of the thread?

                          I create the table with

                          CREATE TABLE "Z_TEST" ( "CONNECT" VARCHAR2(1024 BYTE) );

                          , then go to navigator and select that table.

                          "Columns" tab opens normally, but "Data" tab opens empty, with grayed-out refresh/insert/other buttons.

                          If so - then I am back to what I said in my reply to that - I can't reproduce it.

                           

                          The only difference I see so far is the character set being different as you mentioned in that early post. Sql Developer could certainly be doing something different when a different character set is being used on the client and server sides.

                           

                          That issue will need to be addressed by someone from the sql developer team.

                          • 10. Re: [4.0] Can't view tables where column names are reserved words
                            Arpod

                            You have been lax in not posting the EXACT commands you are executing and the exact results that you get.

                            But I did describe everything I did to get the error in my 3rd post, step by step, even the exact error message I get. It'd be impossible to read the thread if I repeated it in the every post. Everything further is just trying to dig deeper and speculations, since I don't have a chance to look at the actual source code, and I thought that you are able to.

                             

                            When you enclose a column/table name in double quotes sql developer MUST USE that same exact string and MUST USE double quotes when sending the query to the server. Your use of double quotes indicates a CASE-SENSITIVE name so sql developer must also use those double quotes.

                            I admit that I have had no idea about double quotes indicating case-sensitive name before starting this thread - I thought their usage was for reserved words. However, what I was trying to show with my own query is that I was getting the exact same error that SQLDeveloper gave me after entering "DATA" tab; which was supposed to mean that SQLDeveloper somehow uses a similar query, using double-quoted lowercase column name, internally, when it fetches the contents of "DATA" tab. Sorry if that wasn't clear enough, I'm not a native speaker, so conveying my thoughts can be hard, especially when in a hurry like I was at that time.

                             

                            Anyway, I understand that you can't reproduce it, and I was able to reproduce it using two different PCs with different OSs, but connecting to the same DB. The only logical conclusion would be that something in our DB configuration or version causes the bug to appear. Part of trace log I posted yesterday showed that in my case, SQLDeveloper actually uses lowercase column name when querying the data tab, even though column name is uppercase. Pretty much the only way to understand what causes it is to know the algorithm SQLDeveloper uses to make the query for "DATA" tab, which is - you are right- impossible unless we get some input from SQL Developer team.

                            • 11. Re: [4.0] Can't view tables where column names are reserved words
                              Jeff Smith Sqldev Pm-Oracle

                              I can make it happen, it's a bug.

                               

                              create table DONT_DO_THIS

                              ("TABLE" varchar2(20),

                              "iNdEx" varchar2(20),

                              "connect" varchar2(20));

                               

                              we choke on the TABLE bit - it's not the query to to get the data, because we use *, it happens as we get the column list and try to do some other bg work. You can see the queries yourself if you run SQL Developer in debug mode.

                               

                              When you hit the data tab here, we complain about "table" being an invalid identifier - so we're probably not quoting the column names once they're retrieved.

                              • 12. Re: [4.0] Can't view tables where column names are reserved words
                                marcusafs

                                I once inherited a table with the column CURRENT_DATE.  Whenever I queried data the CURRENT_DATE column was always the same date, today.  When I realized the problem I had to investigate how to get the stored date.  The solution was to use a table alias.

                                 

                                If you use double quotes on your column names when you create the table you will always have to use double quotes when you query the data unless the column name is all upper case.  I think you should only use double quotes if you have a business reason to use mixed case identifiers.  And job security is not a business reason. Unfortunately there are some query builders out there that double quote everything.  If I modify one of these generated queries the first thing I do is replace all double quotes with nothing.

                                • 13. Re: [4.0] Can't view tables where column names are reserved words
                                  Arpod

                                  Phew, so it wasn't just our DB, after all. Thanks for your reply!

                                   

                                  You can see the queries yourself if you run SQL Developer in debug mode.

                                  I was about to say that I can't, but it turns out I actually commented out both sqldeveloper-debug.conf and sqldeveloper-nondebug.conf, and now I feel incredibly stupid. Well, now that extended logging seems to work, expect more new bug reports soon!

                                   

                                  When you hit the data tab here, we complain about "table" being an invalid identifier - so we're probably not quoting the column names once they're retrieved.

                                   

                                  I traced the sessions of versions 3.2 and 4.0 and parsed them in TKProf. "SELECT *" query doesn't shows up anywhere in there, all columns are named explicitly. It probably gets unwrapped somewhere along the way? Anyway, 3.2 and 4.0 use somewhat different queries.

                                   

                                  I created the extended version of your table, to include a "normally"-named column:

                                   

                                  create table DONT_DO_THIS
                                  ("TABLE" varchar2(20),
                                  "iNdEx" varchar2(20),
                                  "connect" varchar2(20),
                                  normal_column varchar2(20));
                                  

                                   

                                  Here's what TKProf shows for 3.2:

                                   

                                  SELECT ROWID "ROWID", "TABLE" "TABLE", "iNdEx" "iNdEx", "connect" "connect",
                                    NORMAL_COLUMN NORMAL_COLUMN
                                  FROM
                                  "MYSCHEMA"."DONT_DO_THIS"
                                  

                                   

                                  And for 4.0:

                                   

                                  The following statement encountered a error during parse:
                                  SELECT ROWID "ROWID", "table" "TABLE", "iNdEx" "iNdEx", "connect" "connect", normal_column NORMAL_COLUMN FROM "MYSCHEMA"."DONT_DO_THIS
                                  Error encountered: ORA-00904
                                  

                                   

                                  4.0 seems to convert "normal" column names to lowercase for some reason. My best guess is that it first converts "TABLE" to lowercase, then sees that it's a reserved word and quotes it.

                                  • 14. Re: [4.0] Can't view tables where column names are reserved words
                                    rp0428

                                    Jeff Smith SQLDev PM wrote:

                                     

                                    I can make it happen, it's a bug.

                                     

                                    create table DONT_DO_THIS

                                    ("TABLE" varchar2(20),

                                    "iNdEx" varchar2(20),

                                    "connect" varchar2(20));

                                     

                                    we choke on the TABLE bit - it's not the query to to get the data, because we use *, it happens as we get the column list and try to do some other bg work. You can see the queries yourself if you run SQL Developer in debug mode.

                                     

                                    When you hit the data tab here, we complain about "table" being an invalid identifier - so we're probably not quoting the column names once they're retrieved.

                                    Sorry - still works fine for me on win xp sp3 using sql dev Version 4.0.0.13 Build MAIN-13.80 and both vanilla 10gr2 and 11gr2.

                                     

                                    1. no problem creating that table

                                    2. no problem using the data tab

                                    3. no problem using 'plus' to add a row

                                    4. no problem committing the row

                                    1 2 Previous Next