0

How to insert a column in a specific position in oracle without dropping and recreating the table?

Consider this inital table I have created in Oracle

  CUSTOMER_ID ACC_NO ACC_BALANCE 
------------------------------------
         100    200        1000 
         101    150        4000  
         102    350        2000  
         103    450        2500  
         104    550        2200

Now I want to add another column customer_name as second column(posotion) into the table. i wanted to check the same for hundreds of table and i am using an script to acive this and passingtable_name as dynamic input.

can you somedody pls help mw with dynamic solution

Ashok Kumar Dabbadi
  • 235
  • 3
  • 6
  • 12
  • 1
    The order of columns in a table is totally irrelevant. If you need the in a specific order in the front end, then create a SELECT statement that lists them in the order you want. –  Feb 20 '14 at 10:32
  • thanq Mihai, but here im taking care of aktering from backend it self and i dont want to drop and recreate the table again i have to alter and add the column to table generically – Ashok Kumar Dabbadi Feb 20 '14 at 10:35
  • @Mihai: that is not valid DDL for Oracle –  Feb 20 '14 at 10:36

2 Answers2

0

Use ALTER TABLE

ALTER TABLE table_name
  ADD column_name column-definition;

Oracle does not support adding columns in the middle of a table, only adding them to the end. Your database design and app functionality should not depend on the order of columns in the database schema. You can always specify an order in your select statement, after all.

Community
  • 1
  • 1
Alexander
  • 3,129
  • 2
  • 19
  • 33
0

Short answer - it cannot be done, unless you're upgrading to 12c (where you can temporarily set columns after the position to invisible, add your column and then set them to visible again).

Long answer - if you really, really need to do this (and I've never found a valid reason why anybody would need to do this), you can

  • create a new table T_new with the columns in the right order
  • remove the old table T_old
  • rename the new table T_new to T_old
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107