3 Replies Latest reply on Aug 25, 2017 4:47 PM by rp0428

    Cannot move fields when altering a table

    Andy Frédéric

      I added a new column to a table. So the column is situated at the end of all the columns. I want it to be placed after the first column , but the top, up, down and bottom buttons at the right of the alter table window are disabled :

       

      alter_table.PNG

       

      I use SQL Developer Version  4.1.3.20 Build MAIN-20.78

       

      So how to enable these buttons ?

        • 1. Re: Cannot move fields when altering a table
          Gaz in Oz

          Those buttons are only available when creating a new table, not to edit an existing table.

           

          To change the order of the columns within the table (if you really need to change them), you can:

          1. Recreate the table in the column order you want.

          2. Use the ALTER TABLE MODIFY column INVISIBLE/VISIBLE... If you are on Oracle 12.1.0.2 or later.

          • 2. Re: Cannot move fields when altering a table
            EdStevens

            Andy Frédéric wrote:

             

            I added a new column to a table. So the column is situated at the end of all the columns. I want it to be placed after the first column , but the top, up, down and bottom buttons at the right of the alter table window are disabled :

             

            alter_table.PNG

             

            I use SQL Developer Version 4.1.3.20 Build MAIN-20.78

             

            So how to enable these buttons ?

            WHY do you "need" to change the order of the columns?  Basic relational db theory says that there is no "order" to either rows or columns.  If you want rows returned in a particular order, you use the ORDER BY clause.  If you want columns returned in a particular order, specify that order in your SELECT statement.  Beyond that, rows and columns are like balls in a basket.  And like someone else (BluShadow ?) said the other day to a nearly identical question, there is nothing in the ANSI standard that requires a rdbms to respond to 'SELECT *" by returning the columns in the "order" in which they are defined.  If you write your apps to depend on that column order, there will come a day that you regret it.  Depending on the order of column definition is quite simply a "design fail."

            • 3. Re: Cannot move fields when altering a table

              2. Use the ALTER TABLE MODIFY column INVISIBLE/VISIBLE... If you are on Oracle 12.1.0.2 or later.

              That will NOT change the physical order of the columns - only the logical order.

               

              So that can be a very DANGEROUS thing to do.

               

              For anyone using 'SELECT *' in PL/SQL altering visibility will likely either break their code altogether or return the WRONG column and data.

               

              Suppose you alter what the 3rd column is.

               

              It can break it if the 3rd column had been a DATE column but is now VARCHAR2 (or vice versa).

               

              And it can return the wrong column and data if the 3rd column had been a status date but is now some other date like a birth date.