-
Junior Member
Inserting column in existing table
How to insert a column inbetween two column in an existing table. Say, there r 5 columns in an table. If i want to insert a new column 'sal' as 3rd column. Wats the code?
-
Expert Member
Re: Inserting column in existing table
physically (at memory level)you can't do it...you don't need to insert a column between two existing columns.
-
Re: Inserting column in existing table
As per the rules of RDBMS physical position of data is not at all important.
Data should be accessable irrespective of its position.
Using oracle version available in the market till now it is not possible to do the same directly using only SQL commands.
-
Junior Member
Re: Inserting column in existing table
The replies u got r correct. u can't change the physical position of a column.
-
Contributing Member
Re: Inserting column in existing table
As all the two moderators said it's not important to do so.it matters only with view.but however if u want u can do so like ----> you can use
dbms_redefination package .
But you should have these previleges -->
# execute access on the dbms_redefinition package
# create any table # alter any table
# drop any table
# lock any table
# select any table
create table tab1(col1 char,col3 number) alter table tab1 add(col2 char);
create table tab2 as select col1,col2,col3 from tab1; exec
dbms_redefinition.can_redef_table('schema_name','tab1'); begin
dbms_redefinition.start_redef_table('schema_name','tab1','tab2','col1,col2,col3');
dbms_redefinition.finish_redef_table( 'schema_name', 'tab1', 'tab2' ); end;
select *from tab1;
col1 col2 col3
---------------
-
Junior Member
Re: Inserting column in existing table
you can perform the addition of a new column between two columns by a stored procedure with outline:
1. create table abc(col1 int , col2 int , col4 int)
2. alter table abc add column (colmn3 int)
3.create table abc_copy as select col1, col2, col3 , col4 from abc
4.drop table abc
5. rename abc_copy to abc
it will surely work. But it is not a good practice to add columns inbetween as it takes time to be performed.you must spend sufficient amt of time to design the database that may not need such alteration after table creation..
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules