1 2 Previous Next 24 Replies Latest reply: Nov 20, 2006 5:48 AM by user454189 RSS

    ALTER TABLE

    user454189
      hi,
      i am trying to modify the datatype but i couldn't.i know we can't modify the datatype if the column contain values(unless it contain null values).but itz the situation arises for me to modify it without dropping the column.is it possible?actually i hav datatype LONG(1000) i want to convert it to VARCHAR2(1000).how come it possible any idea? (i dont want to drop that column and add the column)

      SQL> ALTER TABLE T_COURSECATALOG MODIFY (SCODESC VARCHAR2(1000));
      ALTER TABLE T_COURSECATALOG MODIFY (SCODESC VARCHAR2(1000))
      *
      ERROR at line 1:
      ORA-01439: column to be modified must be empty to change datatype

      ur idea's are greatly welcome........

      With Regards
      Boo
        • 1. Re: ALTER TABLE
          user503699
          Add a column of datatype VARCHAR2(1000) to this table and copy data from old (LONG) column to new column.
          • 2. Re: ALTER TABLE
            Avinash Tripathi
            Hi,
            Column modification allowed even if you have data in that column. During modification you can increase its size but can not decrease it. Converting from LONG to varchar2 means decreasing its size from 2GB to 1000 bytes ( 9i). But if you convert it to CLOB it doesnt have a problem.
            Please have a look on this:
            SQL>CREATE TABLE t ( NAME LONG);
            Table created.
            SQL>INSERT INTO t values('aaa');
            1 row created.
            SQL>INSERT INTO t VALUES('bbb');
            1 row created.
            SQL>COMMIT;
            Commit complete.
            SQL>ALTER TABLE T modify name varchar2(1000);
            ALTER TABLE T modify name varchar2(1000)
            *
            ERROR at line 1:
            ORA-01439: column to be modified must be empty to change datatype
            But you try to convert it into CLOB then
            SQL>ALTER TABLE T modify name CLOB;
            Table altered.
            Please have a look on data type and its default size
            http://www.ss64.com/orasyntax/datatypes.html
            SQL>SELECT * FROM v$version;
            BANNER
            ----------------------------------------------------------------
            Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
            PL/SQL Release 9.2.0.6.0 - Production
            CORE 9.2.0.6.0 Production
            TNS for Solaris: Version 9.2.0.6.0 - Production
            NLSRTL Version 9.2.0.6.0 - Production
            5 rows selected.


            For complete list of datatype for 10G Please refere this link:

            http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330




            Regards
            Message was edited by:
            Avinash Tripathi

            Message was edited by:
            Avinash Tripathi
            • 3. Re: ALTER TABLE
              RadhakrishnaSarma
              If you want to add a not null column to a non-empty table, then you may want to find the value for that column for already existing rows. For example, if it is a flag, you can use 'Y' or 'N' as default. So in order to achieve such functionality, you may do this.
              ALTER TABLE with the new column to be NULLABLE.
              then
              update new column with the default for the existing rows
              then
              ALTER TABLE make the column NOT NULL
              "Sorry! Please consider this post invalid."
              "Have not read the reqs properly"
              Cheers
              Sarma.
              • 4. Re: ALTER TABLE
                137669
                depending of the size of the table it may be much faster to recreate this table

                1) create new table with new definition
                2) insert data from old to new table
                3) drop FKs, PK and Indexes of old table
                4) create FKs, PK and Indexes on new table
                5) drop old table
                6) rename new table
                • 5. Re: ALTER TABLE
                  94799
                  I don't believe tables support LONG (1000) (although PL/SQL does), do you mean LONG?

                  It would typically be more appropriate to migrate to CLOB datatype using the following (you may wish to add LOB storage clause):
                  ALTER TABLE t_coursecatalog MODIFY (scodesc CLOB);
                  If you wanted to further (i.e. do the above first) migrate to VARCHAR2 (1000) and you cannot recreate table (which would typically be more efficient) then one option would be to add a column, update the new column to the old column then drop or mark unused (and later drop) the old column and rename the new column to old.
                  • 6. Re: ALTER TABLE
                    APC
                    Padders got there first but seeing as how I've done the work I'll post it anyway...
                    SQL> create table no_more_longs (col1 number, col2 long)
                      2  /

                    Table created.

                    SQL> insert into no_more_longs values (1, 'This is some text')
                      2  /

                    1 row created.

                    SQL> alter table no_more_longs modify col2 varchar2(1000)
                      2  /
                    alter table no_more_longs modify col2 varchar2(1000)
                                                     *
                    ERROR at line 1:
                    ORA-01439: column to be modified must be empty to change datatype


                    SQL> alter table no_more_longs add new_col  varchar2(1000)
                      2  /

                    Table altered.

                    SQL> update no_more_longs set new_col = col2
                      2  /
                    update no_more_longs set new_col = col2
                                                       *
                    ERROR at line 1:
                    ORA-00932: inconsistent datatypes: expected NUMBER got LONG


                    SQL> declare
                      2      v1 no_more_longs.new_col%type;
                      3      r no_more_longs%rowtype;
                      4  begin
                      5     for r in ( select * from no_more_longs) loop
                      6        v1 := r.col2;
                      7        update no_more_longs
                      8        set new_col = v1
                      9        where col1 = r.col1;
                    10     end loop;
                    11  end;
                    12  /

                    PL/SQL procedure successfully completed.


                    SQL> alter table no_more_longs  set unused (col2)
                      2  /

                    Table altered.

                    SQL> desc no_more_longs
                    Name                                      Null?    Type
                    ----------------------------------------- -------- ----------------------------
                    COL1                                               NUMBER
                    NEW_COL                                            VARCHAR2(1000)

                    SQL>
                    Modifying to CLOB certainly is a lot simpler....
                    SQL> drop table no_more_longs
                      2  /

                    Table dropped.

                    SQL> create table no_more_longs (col1 number, col2 long)
                      2  /

                    Table created.

                    SQL> insert into no_more_longs values (1, 'This is some text')
                      2  /

                    1 row created.

                    SQL> alter table no_more_longs modify col2 clob
                      2  /

                    Table altered.

                    SQL> select * from no_more_longs
                      2  /
                          COL1
                    ----------
                    COL2
                    --------------------------------------------------------------------------------
                             1
                    This is some text


                    SQL>
                    As shown it works in 9.2 but I don't know when it was introduced.

                    Cheers, APC
                    • 7. Re: ALTER TABLE
                      94799
                      How did you get 6000 posts? - you must be updating the damn database ;-)
                      • 8. Re: ALTER TABLE
                        495612
                        You can try this way!
                        CREATE TABLE TEMP2 (A VARCHAR2(1000));
                        ALTER TABLE T_COURSECATALOG
                        MODIFY ( SCODESC CLOB );
                        INSERT INTO TEMP2(A)
                        SELECT SCODESC FROM T_COURSECATALOG;
                        DROP TABLE T_COURSECATALOG;
                        RENAME TEMP2 TO T_COURSECATALOG;
                        
                        
                        oops I am too late to reply...
                        Message was edited by:
                        Jameel
                        • 9. Re: ALTER TABLE
                          user454189
                          hi all,
                          Thanks for your replies
                          but i am sorry i forget to mention i am using ORACLE 8i.so wat ever u said will it work on this?

                          With Regards
                          Boo
                          • 10. Re: ALTER TABLE
                            orawarebyte
                            try this one too
                            SQL> CREATE TABLE lng (a  LONG);
                            
                            Table created.
                            
                            SQL> INSERT INTO lng VALUES ('longlonglong');
                            
                            1 row created.
                            
                            SQL> ALTER TABLE lng MODIFY (a   VARCHAR2(1000));
                            ALTER TABLE lng MODIFY (a   VARCHAR2(1000))
                                                    *
                            ERROR at line 1:
                            ORA-01439: column to be modified must be empty to change datatype
                            
                            
                            SQL> CREATE TABLE vrchr  (b  VARCHAR2(1000));
                            
                            Table created.
                            
                            SQL> BEGIN
                              2    FOR c IN (SELECT * FROM lng) 
                              3    LOOP
                              4      INSERT INTO vrchr 
                              5        (b) 
                              6    VALUES  
                              7        (c.a); 
                              8    END LOOP;
                              9  END;
                             10  .
                            SQL> /
                            
                            PL/SQL procedure successfully completed.
                            
                            SQL> SELECT * FROM vrchr;
                            
                            B
                            --------------------------------------------------------------------------------
                            longlonglong
                            
                            SQL> DROP TABLE lng;
                            
                            Table dropped.
                            Khurram
                            • 11. Re: ALTER TABLE
                              APC
                              but i am sorry i forget to mention i am using ORACLE 8i.so wat ever u said will it work on this?
                              The easiest thing will be for you to try it. I don't have access to an Oracle 8i database and I never tried this myself. It should be difficult for you to knock up a test case like we did.

                              Cheers, APC
                              • 12. Re: ALTER TABLE
                                Colin'tHart
                                Hi Boo,

                                I presume you have a column of datatype LONG not LONG(1000).

                                LONGs are very difficult to work with; I haven't ever been able to manage to convert a LONG to a VARCHAR2 -- even via roundabout means. The problem is inserting the LONG data into a VARCHAR2 column.

                                You may be able to do it by going via a CLOB.

                                And in general you will have to add a column, bring the data across, alter the datatype and then move the data back.

                                Or you can do it with another table as a temporary repository.

                                Something like that.

                                Cheerio,

                                Colin
                                • 13. Re: ALTER TABLE
                                  APC
                                  How did you get 6000 posts?
                                  By always rising to the bait of course :D

                                  Although actually it's not six thousand yet...
                                  • 14. Re: ALTER TABLE
                                    Jens Petersen
                                    How did you get 6000 posts? - you must be updating the damn database ;-)
                                    Then he would probably have merged his two accounts into a single one, which would sum up his posts to more than 8000 currently
                                    1 2 Previous Next