This discussion is archived
1 2 3 Previous Next 35 Replies Latest reply: Feb 20, 2013 6:23 AM by 992245 RSS

how to change the column's position  in table?

483636 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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?
    JensPetersen Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    Thanks Jens for completing the answer.

    Nicolas.
  • 4. Re: how to change the column's position  in table?
    sgalaxy Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thanks alot.. its really help..
  • 6. Re: how to change the column's position  in table?
    316993 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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?
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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?
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    "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