3 Replies Latest reply on Jun 7, 2015 9:24 PM by rp0428

    [4.1] Column named "CURRENT_DATE" displays current date instead of contents

    Arpod

      Well, here's the problem: we have a table which contains a field named CURRENT_DATE. When opening said table in navigator and going to "Data" tab, SQLDeveloper displays the contents of this column as a result of CURRENT_DATE function. Sure enough, the statement looks like this:

       

      /* + NO_PARALLEL */SELECT ROWID "ROWID", ..., CURRENT_DATE CURRENT_DATE, ... FROM "SCHEMA"."TABLE"
      
      

       

      Granted, this selects value of CURRENT_DATE function. This is pretty unfortunate, since the table is about 10 years old already, and there's a LOT of business logic built around it; additionally, the person who've created that monster left the job years ago and moved to another city, so I can't even ask him why did he do that in the first place.

       

      So, can sqldeveloper build queries in such way that columns are double-quoted when they collide with built-in function name, like it is done for reserved words like 'INDEX' already? Using table alias could also help.

       

      P.S. what's with that /* + NO_PARALLEL */ hint? Shouldn't it go after select to be useful?

        • 1. Re: [4.1] Column named "CURRENT_DATE" displays current date instead of contents
          thatJeffSmith-Oracle

          we'd have to parse the columns first to see if we found any functions to know if we should quote them, and that's expensive

           

          in your case i'd create a view probably

          • 2. Re: Re: [4.1] Column named "CURRENT_DATE" displays current date instead of contents
            Arpod

            I'm pretty sure you are already parsing columns, because they are actually listed in the query one-by-one instead of *'ing.

            In fact, this query, which is run before I first go to the data tab:

             

            SELECT 'COLUMN' type, owner, table_name object_name, column_name, column_id, data_type
            FROM sys.all_tab_cols
            WHERE hidden_column = 'NO' and rownum <=999 and owner = ? and table_name = ?
            

             

            is exactly for parsing columns. Also, keywords like "index" are already double-quoted, like I've mentioned. It looks like it's already 2/3 done anyway, just need some keyword list expansion =)

            • 3. Re: [4.1] Column named "CURRENT_DATE" displays current date instead of contents
              Also, keywords like "index" are already double-quoted, like I've mentioned. It looks like it's already 2/3 done anyway, just need some keyword list expansion =)

              If a 'solution' were really needed the simplest one would just be to quote ALL of the columns in the query.

               

              Lower-case names are already quoted when needed but upper-case names aren't. That seems to suggest that Sql dev is already doing some parsing/checking in order to know that a column with lower-case or special characters needs to be quoted.

              create table a_table ("abc" number, abc number, "current_date" varchar2(10), current_date varchar2(10));

              insert into a_table values (1, 2, 'lower_date', 'upper_date');

               

              Modified SQL: SELECT ROWID "ROWID", ORA_ROWSCN "ORA_ROWSCN", "abc" "abc", ABC ABC, "current_date" "current_date", CURRENT_DATE CURRENT_DATE FROM "SCOTT"."A_TABLE"

              The two column names that are case-sensitive were quoted.