This content has been marked as final.
Show 9 replies
-
1. Re: add column(in table) in required position
402451 Oct 16, 2003 6:23 AM (in response to 402451)
i thing colum position we can give
in command
ALTER TABLE ADD COLUMN
BUT I AM NOT KNOWING CORRECT SYNTAX
-
2. Re: add column(in table) in required position
247823 Feb 17, 2006 10:55 AM (in response to 402451)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 -
3. Re: add column(in table) in required position
555399 Jan 10, 2007 7:33 AM (in response to 402451)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 -
4. Re: add column(in table) in required position
480582 Jan 22, 2007 10:01 PM (in response to 402451)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. -
5. Re: add column(in table) in required position
247823 Mar 4, 2007 7:58 AM (in response to 402451)Hi,
It may be possible when we have any GUI interface (from Oracle) kind of tool.
Or
With manually ..... as Tejaycar said......
Regards,
Sailaja -
6. Re: add column(in table) in required position
622229 Oct 1, 2008 1:33 PM (in response to 247823)(not sure)
try DBMS_REDEFINITION package -
7. Re: add column(in table) in required position
801640 Sep 29, 2010 10:37 AM (in response to 402451)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. -
8. Re: add column(in table) in required position
Barbara Boehmer Sep 29, 2010 4:43 PM (in response to 622229)user619226 wrote:
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.
(not sure)
try DBMS_REDEFINITION package
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>
-
9. Re: add column(in table) in required position
Barbara Boehmer Sep 29, 2010 4:46 PM (in response to 801640)798637 wrote:
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.
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.
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.