Home » SQL & PL/SQL » SQL & PL/SQL » trying to create a new column a specific position in already existing table (sql developer )
icon3.gif  trying to create a new column a specific position in already existing table [message #429266] Mon, 02 November 2009 22:47 Go to next message
satyam.sumit
Messages: 7
Registered: October 2009
Location: INDIA
Junior Member

hi all i am trying to create a new column a specific position in already existing table.

By defalut it is coming a last position.

Please suggest your views. Razz
Re: trying to create a new column a specific position in already existing table [message #429267 is a reply to message #429266] Mon, 02 November 2009 22:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
satyam.sumit wrote on Mon, 02 November 2009 20:47

hi all i am trying to create a new column a specific position in already existing table.

By defalut it is coming a last position.

Please suggest your views. Razz


It is expected behavior.
Order of columns within table is not material
Re: trying to create a new column a specific position in already existing table [message #429281 is a reply to message #429266] Tue, 03 November 2009 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Can you please search BEFORE posting, this question is asked once per month.

Regards
Michel
Re: trying to create a new column a specific position in already existing table [message #429285 is a reply to message #429266] Tue, 03 November 2009 00:57 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
DROP TABLE ...
CREATE TABLE ...


No other way...

regards,
Delna
Re: trying to create a new column a specific position in already existing table [message #429318 is a reply to message #429285] Tue, 03 November 2009 03:52 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
delna.sexy wrote on Tue, 03 November 2009 06:57
DROP TABLE ...
CREATE TABLE ...


No other way...

regards,
Delna


Assuming you want to keep the existing data that should be:
CREATE TABLE new_table AS SELECT...
DROP TABLE old_table
RENAME new_table to old_table


But personally I'd stop worrying about the column order.
Re: trying to create a new column a specific position in already existing table [message #429351 is a reply to message #429318] Tue, 03 November 2009 05:52 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Please consider the constraints too
Make sure about your constraints. Smile

Sriram

[Updated on: Tue, 03 November 2009 05:53]

Report message to a moderator

Re: trying to create a new column a specific position in already existing table [message #429356 is a reply to message #429266] Tue, 03 November 2009 06:22 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Create a view on the base table after adding the column to it.In that view you can put the columns in order the way you want.

Thanks

[Updated on: Tue, 03 November 2009 06:23]

Report message to a moderator

Re: trying to create a new column a specific position in already existing table [message #429370 is a reply to message #429356] Tue, 03 November 2009 07:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Always the same answers (could it be differently?).
One is missing:

Why do you need the column to be in a specific place?

Regards
Michel

[Updated on: Tue, 03 November 2009 07:28]

Report message to a moderator

Re: trying to create a new column a specific position in already existing table [message #429373 is a reply to message #429370] Tue, 03 November 2009 07:35 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

Always the same answers (could it be differently?).
One is missing:

Why do you need the column to be in a specific place?


The other answer would be (as already provided)
1. Alter the old table
2. Create new table putting the column in the order the OP want
3. Disable constraints if any
4. Drop the old table
5. Rename the new to to the old one

But again, the question arises..
Quote:

Why you need the column to be in a specific place?

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. But its really a bad bad idea



You can have a look at DBMS_REDEFINITION package
http://www.orafaq.com/node/4




[Updated on: Tue, 03 November 2009 07:43]

Report message to a moderator

Re: trying to create a new column a specific position in already existing table [message #429374 is a reply to message #429373] Tue, 03 November 2009 07:39 Go to previous messageGo to next message
satyam.sumit
Messages: 7
Registered: October 2009
Location: INDIA
Junior Member
hi all thanks for your valueable suggestions.

Re: trying to create a new column a specific position in already existing table [message #429379 is a reply to message #429373] Tue, 03 November 2009 07:48 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

The other answer would be (as already provided)
1. Alter the old table
2. Create new table putting the column in the order the OP want
3. Disable constraints if any
4. Drop the old table
5. Rename the new to to the old one
6.recreate index,trigger,constraint etc


This idea can cause the other objects like package/procedure etc make invalid
Re: trying to create a new column a specific position in already existing table [message #429415 is a reply to message #429379] Tue, 03 November 2009 11:05 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I for one am still waiting for the OP to tell us why they think they need columns to be in a specific order in the table.

Is there some special need?
Is there some piece of code that fails if columns are not in a specific order (types?).
Is there a third party tool you are trying to integrate with the requires it?

Kevin
Previous Topic: Correlation Approach
Next Topic: Recompiling database objects
Goto Forum:
  


Current Time: Sat Feb 08 08:01:51 CST 2025