Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    44

    Unanswered: Change the datatype

    Hi everybody,
    I have a problem in changing the datatype of my column.
    I have a table called authenticate_user which has the columns userid number(7) and username varchar2(10). I have lot of records in my table. Now I have to change the datatype of username to
    nvarchar2(20). If i try with alter table command, it gives an error of column value should be empty/null. My oracle version 8.1.7. I am able to do in Oracle 9i. Let me know is there any other way to change the datatype in Oracle 8.1.7.


    Thanks in advance

    Mahesh BK

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    you might have to move the table

    rename the original table to tmp<table>
    create an empty <new table> with the needed columns and column sizes

    then

    insert into <new table> ( field1, ..., field<n>)
    select field1, ..., field<n> from tmp<table>;

    if the new table was named the original one

    drop the tmp<table>

    reminder constraints, indexes ... have to be dropped before and recreate after the insert select from action....

  3. #3
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Smile

    Here is what you can do:


    SQL> create table test (id number, test1 varchar2(20));

    Table created.

    SQL> insert into test values (1, 'TEST1');

    1 row created.

    SQL> insert into test values (2, 'TEST2');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> alter table test add test2 nvarchar2(20);

    Table altered.

    SQL> desc test;
    Name Null? Type
    ----------------------------------------------------- -------- -------------------
    ID NUMBER
    TEST1 VARCHAR2(20)
    TEST2 NVARCHAR2(20)

    SQL> select * from test;

    ID TEST1 TEST2
    ------------------------------ -------------------- --------------------
    1 TEST1
    2 TEST2

    2 rows selected.




    SQL> declare
    2
    3 cursor upd is
    4 select id, test1 from test;
    5 begin
    6 for upd_cur in upd loop
    7 update test set test2 = translate(upd_cur.test1 using nchar_cs)
    8 where id = upd_cur.id;
    9 end loop;
    10 end;
    11 /

    PL/SQL procedure successfully completed.

    SQL> select * from test;

    ID TEST1 TEST2
    ------------------------------ -------------------- --------------------
    1 TEST1 TEST1
    2 TEST2 TEST2

    2 rows selected.



    SQL> alter table test drop column test1;

    Table altered.

    SQL> select * from test;

    ID TEST2
    ------------------------------ --------------------
    1 TEST1
    2 TEST2

    2 rows selected.

    SQL> desc test
    Name Null? Type
    ----------------------------------------------------- -------- -------------------
    ID NUMBER
    TEST2 NVARCHAR2(20)



    Hope that helps,

    clio_usa - OCP - DBA


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •