Home » SQL & PL/SQL » SQL & PL/SQL » Adding column
Adding column [message #201357] Fri, 03 November 2006 12:01 Go to next message
vimal1234
Messages: 12
Registered: November 2006
Junior Member
Hello,

I'm having oracle version as below

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

I would like to add new column in the middle. Will that possible.

Alter table emp add (job varchar2(20) before ename --Something like this.


Thank you,
--Vimal

Re: Adding column [message #201359 is a reply to message #201357] Fri, 03 November 2006 13:00 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I *think* this isn't that easy; why would you, anyway, want to add a column in the middle of a table? What would you get with it?

However, here's one way to do it: create a temporary table as

CREATE TABLE temp_table AS SELECT * FROM original_table;

Now drop original table and recreate it, specifying desired column order. Insert data from the temporary table using column list as

INSERT INTO newly_created_original_table
(col1, col2, ..., colx)
(SELECT col1, col2, ..., colx
FROM temp_table);

This scenario isn't always possible; for example, when the original table is just too big to allow such things or if there are numerous foreign key constraints which would prevent you to drop the table).

In my opinion, you should simply add that column to its default place (as last in the table) and live with it. Of course, statements like

SELECT * FROM your_table;

won't look as you'd like them to - you'll have to specify column list.
Re: Adding column [message #201435 is a reply to message #201359] Sat, 04 November 2006 03:28 Go to previous messageGo to next message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

When you alter and add column, oracle automatically adds it at the end only.

There is no "before ename" like this in oracle.
You have to backup, drop and recreate the table like Littlefoot said.

Make sure, you also backup the index scripts, triggers and views associated with this table. You may loose it after you drop the table!
Re: Adding column [message #201618 is a reply to message #201435] Mon, 06 November 2006 01:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the order of the columns is important for some reason, then your best bet is to create a View on that table, which lists the columns in the order you want.

Re: Adding column [message #202075 is a reply to message #201618] Wed, 08 November 2006 02:29 Go to previous messageGo to next message
pritika
Messages: 9
Registered: November 2006
Junior Member
yes its true, if the order of the columns is important then one must create a view.
Re: Adding column [message #202098 is a reply to message #202075] Wed, 08 November 2006 03:28 Go to previous message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

There are pros and cons. Dropping the table and recreating is hassle and needs lot of issue with commercial organization in production enviornment. If this is adequately tested, perhaps it is good choice as it improves the performance.

Creating view is easy, but if the table is used by many programs, it adds complexity. Secondly, the new column is added at the end of the table which affects some performance especially varchar2(1000) to varchar2(4000) at the end of table.

Since both will work, the developer has to find out the best situation for his/her environment.

Jayg
Fun Programming with Oracle & Visual C++

[Updated on: Wed, 08 November 2006 04:05]

Report message to a moderator

Previous Topic: SQL query - need help
Next Topic: Sql Help
Goto Forum:
  


Current Time: Sat Dec 10 16:53:17 CST 2016

Total time taken to generate the page: 0.04538 seconds