9 Replies Latest reply on Sep 29, 2010 4:46 PM by Barbara Boehmer

    add column(in table) in required position

    402451
      If i add colum to table is goes on last position.
      e.g
      table :
      emp(no,sal,deptno)

      if i want to add column empname it goes on 4th position
      emp(no,sal,deptno,empname)
      but i want it in second position.
      how can i do it like
      emp(no,empname,sal,deptno)
        • 1. Re: add column(in table) in required position
          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
            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
              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
                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
                  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
                    (not sure)
                    try DBMS_REDEFINITION package
                    • 7. Re: add column(in table) in required position
                      801640
                      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
                        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>
                        • 9. Re: add column(in table) in required position
                          Barbara Boehmer
                          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.