add column(in table) in required position

Hi,

User cannot insert a new column i.e. in between user cannot add a new column with an ALTER statement in Oracle.

Regards,
Sailaja

Hi
I face the same problem.when i add a column it btdefault adds in last.

alter table a add b varchar2(25);

any help is appriciable.

Regards
prusty

I am not aware of any direct way to add a new column in the middle of the table. There is a roundabout way to accomplish it however. Take this as an example:

CREATE TABLE my_user (
userID NUMBER
, firstName VARCHAR2(25)
, lastName VARCHAR2(25));

----------------------------------------------------
Now we decide to include a middleInitial column
We have two choices.
1) We can alter the table to add a middle initial column, and then we can create a view that changes the order of the middleInitial and lastName column. We can then use the view for everything. However, that is not a great solution.
2) We can create a temporary copy of the user table, drop the user table, create the new user table, and then copy over the old data.
----------------------------------------------------
Example:

CREATE TABLE temp_my_user AS (
SELECT * FROM my_user);

DROP TABLE my_user;

CREATE TABLE my_user (
userID NUMBER
, firstName VARCAHR2(25)
, middleInitial VARCHAR2(1)
, lastName VARCHAR2(25));

INSERT INTO my_user(userID, firstName, lastName)
(SELECT userID
, firstName
, lastName
FROM temp_my_user);

DROP TABLE temp_user;

*Now you should be done.  This is a pain, but it will give you the columns you need in the order you desire.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

Hi,

It may be possible when we have any GUI interface (from Oracle) kind of tool.
Or
With manually ..... as Tejaycar said......


Regards,
Sailaja

(not sure)
try DBMS_REDEFINITION package

ALTER TABLE table_name
ADD column_name column-definition [ FIRST | AFTER col_name ]
this is the command for add column(in table) in required position

i think it is for mysql but it is all so work for sql just check it once.

user619226 wrote:
(not sure)
try DBMS_REDEFINITION package

Yes, as demonstrated below. It does something similar to the previous demonstration of creating an interim table, copying the data, creating a table with the new structure, copying the data, and dropping the interim table. This is a minimal demonstration. Dbms_redefinition has many more features.

However, this thread has nothing to do with Oracle objects, so it does not belong in the objects forum. Perhaps there is someone with moderator privileges who can move it to an appropriate forum.

SCOTT@orcl_11gR2> DESC my_user
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERID                                             NUMBER
 FIRSTNAME                                          VARCHAR2(25)
 LASTNAME                                           VARCHAR2(25)

SCOTT@orcl_11gR2> CREATE TABLE temp_my_user
  2    ( userID      NUMBER
  3    , firstName     VARCHAR2(25)
  4    , middleInitial     VARCHAR2(1)
  5    , lastName     VARCHAR2(25))
  6  /

Table created.

SCOTT@orcl_11gR2> BEGIN
  2    DBMS_REDEFINITION.START_REDEF_TABLE
  3         (USER, 'my_user', 'temp_my_user',
  4          'userid, firstname, lastname',
  5          DBMS_REDEFINITION.CONS_USE_ROWID);
  6    DBMS_REDEFINITION.FINISH_REDEF_TABLE
  7        (USER, 'my_user', 'temp_my_user');
  8  END;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> DROP TABLE temp_my_user
  2  /

Table dropped.

SCOTT@orcl_11gR2> DESC my_user
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERID                                             NUMBER
 FIRSTNAME                                          VARCHAR2(25)
 MIDDLEINITIAL                                      VARCHAR2(1)
 LASTNAME                                           VARCHAR2(25)

SCOTT@orcl_11gR2>

798637 wrote:
ALTER TABLE table_name
ADD column_name column-definition [ FIRST | AFTER col_name ]
this is the command for add column(in table) in required position

i think it is for mysql but it is all so work for sql just check it once.

That is not valid syntax for Oracle. The SQL used in mysql and the SQL used in Oracle are very different. You can easily tell that it is not valid syntax by either attempting to run it on an Oracle database or checking the online documentation for the syntax for ALTER TABLE.

Also, this thread has nothing to do with Oracle objects, so it is in the wrong forum. If there is somebody with moderator privileges, then please move it to an appropriate forum.