Home » SQL & PL/SQL » SQL & PL/SQL » Oracle newbie
Oracle newbie [message #357414] Wed, 05 November 2008 05:07 Go to next message
ram_orac
Messages: 3
Registered: November 2008
Junior Member
Hi this is Ram. Kindly help me with this. I am a newbie.

How to insert a column in the existing table in between 2 column ?. Say, there r 20 columns in an table. If i want to insert a new column in between as 8th column. If i used the alter command will it be effective? Say im having lakhs of record in the table, i need to copy all those records before alter command. Instead of this shall we copy by making of an update? What's the code?


Million thanks!
Re: Oracle newbie [message #357416 is a reply to message #357414] Wed, 05 November 2008 05:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
New columns are always added at the end of a table.
Why do you think that you need to add it in the middle?
We see this request fairly frequently, but no-one has been able to come up with a decent reason for doing it.
Re: Oracle newbie [message #357418 is a reply to message #357414] Wed, 05 November 2008 05:15 Go to previous messageGo to next message
Littlefoot
Messages: 20892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You don't have to "copy" records anywhere; why do you think you need to copy those records? Where would you copy them?

Just add this new column using the ALTER TABLE statement.
Re: Oracle newbie [message #357419 is a reply to message #357416] Wed, 05 November 2008 05:16 Go to previous messageGo to next message
ram_orac
Messages: 3
Registered: November 2008
Junior Member
So is it not possible to insert columns in the middle of a recordset? Please?
Re: Oracle newbie [message #357423 is a reply to message #357419] Wed, 05 November 2008 05:21 Go to previous messageGo to next message
Littlefoot
Messages: 20892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why wouldn't it be possible? People managed to fly to the Moon, why wouldn't we be able to rearrange a table?

One option could be a CTAS (Create Table As) principle:
CREATE TABLE new_table AS
  SELECT first_column, 
         second_column, 
         0 NEW_COLUMN,      --> newly added column of a NUMBER datatype
         third_column
  FROM existing_table;

DROP existing_table;

RENAME new_table TO existing_table;


Or, you might save existing records, drop the original table, recreate it (don't forget to rearrange columns' order) and insert saved data into a newly created table.

But all of this simply isn't worth it ... why would you care whether this newly added column is 21st or 8th?

[EDIT] Forgot to mention; perhaps you won't be able to DROP a table if there are foreign key constraints involved. In that case, it would be a little bit or even rather difficult.

[Updated on: Wed, 05 November 2008 05:23]

Report message to a moderator

Re: Oracle newbie [message #357424 is a reply to message #357419] Wed, 05 November 2008 05:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It is not possible to add a new column into the middle of an existing table, and neither is there any need to do so.

If you are convinced that you have to, you will need to:

1) Create a new table with all the existing columns, plus the new one in the desired place
2) Copy the data from the old table
3) Drop the old table
4) rename the new table to the old one
5) recreate the constraints and indexes on the new table.

It's a lot of work for something that has no point.
Re: Oracle newbie [message #357426 is a reply to message #357419] Wed, 05 November 2008 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So is it not possible to insert columns in the middle of a recordset? Please?

A record set or a table?

Regards
Michel
Re: Oracle newbie [message #357427 is a reply to message #357423] Wed, 05 November 2008 05:30 Go to previous messageGo to next message
ram_orac
Messages: 3
Registered: November 2008
Junior Member
Thank You so much seniors!

I got it. Thanks again.
Re: Oracle newbie [message #357476 is a reply to message #357427] Wed, 05 November 2008 09:59 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Don't thank us for helping you to do something pointless.
It would be better to realise why what you're asking to do is pointless - that would be something worth being thanked for.
Previous Topic: Urgent help on ORA-06502.
Next Topic: oracle view - urgent
Goto Forum:
  


Current Time: Mon Dec 05 04:27:47 CST 2016

Total time taken to generate the page: 0.06735 seconds