DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: How to solve this problem with LONG column?

  1. #1
    Join Date
    Mar 2002
    Posts
    171
    I am having a table X with a LONG column C1.

    I have 3000 rows in it.

    I want to change the datatype from LONG to VARCHAR. If we have to do this, we have to truncate the data first and only then modify. How can I backup the data before truncating?

    I want to change the datatype to VARCHAR and still retain the data? How can I achieve this? Please advise.

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by dbafreak
    I am having a table X with a LONG column C1.

    I have 3000 rows in it.

    I want to change the datatype from LONG to VARCHAR. If we have to do this, we have to truncate the data first and only then modify. How can I backup the data before truncating?

    I want to change the datatype to VARCHAR and still retain the data? How can I achieve this? Please advise.
    Hi dbafreak,

    1. Make a temp table:

    Code:
    CREATE TABLE T1 (V1 varchar2(4000));
    2. Run this PL/SQL block:

    Code:
    declare
    w_string varchar2(4000);
    cursor Cur1 is
    select C1 from X;
    begin
    open Cur1; loop
    fetch Cur1 into w_string;
    if Cur1%notfound then close Cur1; exit; end if;
    insert into T1 values (w_string);
    commit;
    end loop;
    end;
    /
    Now, you have the 3000 values as varchar2(4000) in the T1 table.

    3. Drop the column (do you use Oracle 8i)? Then create the new column as varchar2(4000) and populate it from T1. Another option is to create a temp table containing all values from X but T1, add a new column C1 as varchar2(4000) and populate it from T1.

    Ask, if something's unclear.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Mar 2002
    Posts
    171
    Originally posted by julian
    Originally posted by dbafreak
    I am having a table X with a LONG column C1.

    I have 3000 rows in it.

    I want to change the datatype from LONG to VARCHAR. If we have to do this, we have to truncate the data first and only then modify. How can I backup the data before truncating?

    I want to change the datatype to VARCHAR and still retain the data? How can I achieve this? Please advise.
    Hi dbafreak,

    1. Make a temp table:

    Code:
    CREATE TABLE T1 (V1 varchar2(4000));
    2. Run this PL/SQL block:

    Code:
    declare
    w_string varchar2(4000);
    cursor Cur1 is
    select C1 from X;
    begin
    open Cur1; loop
    fetch Cur1 into w_string;
    if Cur1%notfound then close Cur1; exit; end if;
    insert into T1 values (w_string);
    commit;
    end loop;
    end;
    /
    Now, you have the 3000 values as varchar2(4000) in the T1 table.

    3. Drop the column (do you use Oracle 8i)? Then create the new column as varchar2(4000) and populate it from T1. Another option is to create a temp table containing all values from X but T1, add a new column C1 as varchar2(4000) and populate it from T1.

    Ask, if something's unclear.
    Thanks. Let me try this and get back to you. Yes, I am using 8i. My only doubt is whether the "fetch into option" (used with the cursor) works with LONG columns. I will getback if I face any problems. Thanks again.

  4. #4
    Join Date
    Mar 2002
    Posts
    171
    Excellent Juilan. This worked. Thanks for help.

    Now I have one other question extended to this topic. I don't know the maximum length of the data in the LONG column. I cannot use LENGTH() function with long columns. Since I have to modify the column to a VARCHAR, I will have to know the maximum length of the LONG columns' data. How can I find out this? Kindly help.

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Hi Julian,

    I was also thinking on same solution but have one doubt. How to corelate those records in the new table to the original table?
    I think we should also insert the primary key columns in the temporary table so that it will be easy to insert (or update) them back to original table.

    Sanjay

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Metalink Note 1036895.6 will show you how can you find the length of a LONG column.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by SANJAY_G
    Hi Julian,

    I was also thinking on same solution but have one doubt. How to corelate those records in the new table to the original table?
    I think we should also insert the primary key columns in the temporary table so that it will be easy to insert (or update) them back to original table.

    Sanjay
    Yes, you are right. The PK must also be stored to achieve the practical solution. This means that table T1 should have one more column, namely the PK of the original table. Based on that, you can repopulate the X table with the correct (corresponding) values. Good remark!

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    Mar 2002
    Posts
    171
    Originally posted by julian
    Metalink Note 1036895.6 will show you how can you find the length of a LONG column.
    ???

    I didn't get you. Could you pls get back to me with the details.

  9. #9
    Join Date
    Mar 2002
    Posts
    171
    Originally posted by julian
    Originally posted by SANJAY_G
    Hi Julian,

    I was also thinking on same solution but have one doubt. How to corelate those records in the new table to the original table?
    I think we should also insert the primary key columns in the temporary table so that it will be easy to insert (or update) them back to original table.

    Sanjay
    Yes, you are right. The PK must also be stored to achieve the practical solution. This means that table T1 should have one more column, namely the PK of the original table. Based on that, you can repopulate the X table with the correct (corresponding) values. Good remark!

    Yes. Thats right and a good remarak. Anyways, how do we find the maximum length (in terms of the number of characters) of a LONG column. Please help.

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by dbafreak
    Originally posted by julian
    Metalink Note 1036895.6 will show you how can you find the length of a LONG column.
    ???

    I didn't get you. Could you pls get back to me with the details.
    I've just tested the function in Metalink note 1036895.6. Works perfectly fine:

    Do you have access to Metalink?

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

Posting Permissions

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


Click Here to Expand Forum to Full Width