1 2 3 Previous Next 35 Replies Latest reply: Feb 20, 2013 8:23 AM by 992245 RSS

    how to change the column's position  in table?

    483636
      hye all,
      how can i change the position of column in the database
      for example:
      when i do query = "select column_name from all_tab_columns where table_name = upper('table_test')"

      this will be the column output - "columnA, columnB, columnC, columnD"

      but what i need is output like this : "columnD, columnA, columnB, columnC

      i think we have to change the position of the column in the database..so, how can i do that?
      thanks~
        • 1. Re: how to change the column's position  in table?
          Nicolas.Gasparotto
          Hi,
          SQL> create table izza (col1 number, col2 number, col3 number);

          Table created.

          SQL> desc izza
          Name                                      Null?    Type
          ----------------------------------------- -------- -------------------
          COL1                                               NUMBER
          COL2                                               NUMBER
          COL3                                               NUMBER

          SQL> create table izza2 as select col3, col2, col1 from izza;

          Table created.

          SQL> drop table izza;

          Table dropped.

          SQL> alter table izza2 rename to izza;

          Table altered.

          SQL> desc izza;
          Name                                      Null?    Type
          ----------------------------------------- -------- -------------------
          COL3                                               NUMBER
          COL2                                               NUMBER
          COL1                                               NUMBER

          SQL>
          And recreate indexes.

          Nicolas.
          • 2. Re: how to change the column's position  in table?
            Jens Petersen
            And recreate indexes.
            ... and don't forget to recreate constraints, triggers and default values for that table.
            i think we have to change the position of the column in the database
            Why do you think so?
            • 3. Re: how to change the column's position  in table?
              Nicolas.Gasparotto
              Thanks Jens for completing the answer.

              Nicolas.
              • 4. Re: how to change the column's position  in table?
                sgalaxy
                This can be done very quickly , using 3-rd party tools - such as PL/SQL Developer...in a manner called 'RECREATE TABLE'
                Before the recreation you have to move the data in another table (with the same format) and also take a copy of triggers/indexes.

                Simon
                • 5. Re: how to change the column's position  in table?
                  483636
                  thanks alot.. its really help..
                  • 6. Re: how to change the column's position  in table?
                    orawarebyte
                    hi

                    Why dont you achieve it by using view
                    SQL> CREATE TABLE a (col1  NUMBER,col2   NUMBER,col3   NUMBER);

                    Table created.

                    SQL> CREATE VIEW a_view AS SELECT col3,col2,col1 FROM a;

                    View created.

                    SQL> DESC a
                    Name                            Null?    Type
                    ------------------------------- -------- ----
                    COL1                                     NUMBER
                    COL2                                     NUMBER
                    COL3                                     NUMBER

                    SQL> DESC a_view
                    Name                            Null?    Type
                    ------------------------------- -------- ----
                    COL3                                     NUMBER
                    COL2                                     NUMBER
                    COL1                                     NUMBER


                    SQL> INSERT INTO a VALUES (1,2,3);

                    1 row created.

                    SQL> SELECT * FROM a;

                         COL1      COL2      COL3
                    --------- --------- ---------
                            1         2         3

                    SQL> SELECT * FROM a_view;

                         COL3      COL2      COL1
                    --------- --------- ---------
                            3         2         1
                    Khurram Siddiqui
                    oraware@yahoo.com
                    • 7. Re: how to change the column's position  in table?
                      518838
                      Hi,
                      I just want to know if there is any provision in Oracle so that we can decide column position during adding a column to an already existing table.
                      As the table is an existing table in production, we can't take risk of droping & recreating it at any cost.
                      for example :

                      table x
                      ---------------------
                      column position
                      col1-----------1
                      col2-----------2
                      col3-----------3

                      Now I want to add column 4 at position 3 so it should be like:

                      table x
                      ---------------------
                      column position
                      col1-----------1
                      col2-----------2
                      col4-----------3
                      col3-----------4

                      Pls suggest if it is possible without any recreation of table

                      Regards
                      Deepak
                      • 8. Re: how to change the column's position  in table?
                        27876
                        just want to know if there is any provision in Oracle so that we can decide column position during adding a column
                        No.
                        we can't take risk of droping & recreating it at any cost.
                        Then don't.
                        There is no genuine requirement that necessacitate this type of operation to be supported.

                        If you think you have one, let us know and we can suggest alternatives, if needed.

                        Message was edited by:
                        Kamal Kishore
                        • 9. Re: how to change the column's position  in table?
                          JustinCave
                          While it is possible to do this sort of thing with the DBMS_REDEFINITION package, I second Kamal's comment that it is generally a bad idea.

                          Justin
                          • 10. Re: how to change the column's position  in table?
                            518838
                            Thanks Kamal,
                            The need for repositioning the column is arising while we're making enhancement to an existing application.
                            What we've to do is to add a new column in a table.
                            The existing functioning is like that

                            Below is the table structure:

                            customer customer_exceptions
                            -------------- ------------------------------
                            col1----------col1
                            col2----------col2
                            ............. ................
                            colLast----colLast
                            ------------- colException

                            Now what existing application is doing, is

                            Insert Into Customer_Exceptions
                            (
                            Select A.*, 'Customer name is empty'
                            From Customer A Where
                            A.Customer_Name Is Null
                            );
                            The same type of process is occurring at several places in the application.

                            The problem comes while we're adding a new column group_code at the end of both the tables.
                            As per the existing query, the colException column should be the last one in Customer_Exceptions table, while after adding group_code, it will become second last in the list, & entire logic will fail.

                            If we plan to change the existing logic of "select * ", & keep individual column names in place, it can solve the problem, but in that case, it'll be a very big change which is not advised.

                            Pls suggest any complementary workaround for this problem.

                            Thanks
                            Deepak
                            • 11. Re: how to change the column's position  in table?
                              JustinCave
                              Using SELECT * in production code is bad practice for this (and other) reasons. The right thing to do would be to eliminate this coding style from the application.

                              If you don't want to fix the root cause, you can, as I suggested, use the DBMS_REDEFINITION package. An example of inserting a column in the middle of the table

                              Justin
                              • 12. Re: how to change the column's position  in table?
                                545090
                                In that case use the view instead as advised by Khurram Siddiqui. You can perform all dml as in a table with the view too and you would not have to change the application code either
                                • 13. Re: how to change the column's position  in table?
                                  BluShadow
                                  If we plan to change the existing logic of "select *
                                  ", & keep individual column names in place, it can
                                  solve the problem, but in that case, it'll be a very
                                  big change which is not advised.
                                  Not advised by who? I would certainly advise that you put the effort in now to fix the cause of the issue rather than leave it there to cause further issues later on in the products life. "Select *..." is bad practice. Remove it now while you can.
                                  • 14. Re: how to change the column's position  in table?
                                    APC
                                    "Select *..." is bad practice.
                                    hmmm... I agree that this:
                                    DECLARE
                                        v1 number;
                                        v2 date;
                                    BEGIN
                                        select * into v1, v2
                                        from some_table
                                        where ...;
                                    END;
                                    /
                                    is a bad practice. But what precisely is wrong with this?
                                    DECLARE
                                        some_rec some_table%rowtype;
                                    BEGIN
                                        select * into  some_rec
                                        from some_table
                                        where ...;
                                    END;
                                    /
                                    Oh I know it might be bad news if we add a CLOB column or something that we don't need in this particular chunk of code but that's why Nature gave us impact analysis.

                                    Cheers, APC
                                    1 2 3 Previous Next