Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

How do I convert the datatype of a field in an existing table

vholloway1127vholloway1127 Member Posts: 2
Hello. I created a table with a field name 'request_notes' of datatype varchar2(4000). The problem is sometimes the field exceeds 4000 and produces an errors. I would like to convert this field to a datatype which can hold a larger amount of data (ie. LONG, CLOB or BLOB). The field has valuable data in it which I don't want to loose. Please tell me how I can successfully make the conversion and maintain the data without losing or corrupting the data.

Comments

  • FunmiladeFunmilade Member Posts: 15
    : Hello. I created a table with a field name 'request_notes' of datatype varchar2(4000). The problem is sometimes the field exceeds 4000 and produces an errors. I would like to convert this field to a datatype which can hold a larger amount of data (ie. LONG, CLOB or BLOB). The field has valuable data in it which I don't want to loose. Please tell me how I can successfully make the conversion and maintain the data without losing or corrupting the data.
    :
    Let's see if this works for you.
    Assuming your first table is t1 with the following definition:
    CREATE TABLE T1
    ( ID NUMBER,
    COL1 VARCHAR2(4000));
    Your new table is T2
    CREATE TABLE T2
    (ID NUMBER,
    COL1 LONG);
    To migrate your existing data into the new table, T2
    INSERT INTO T2 (COL1)
    SELECT COL1 FROM T1;

    This technique works fine for VARCHAR2 to LONG conversion. You may give VARCHAR2 to CLOB|BLOB a try and tell me the results.

    Cheers,
    XtrmLade
  • vholloway1127vholloway1127 Member Posts: 2
    : : Hello. I created a table with a field name 'request_notes' of datatype varchar2(4000). The problem is sometimes the field exceeds 4000 and produces an errors. I would like to convert this field to a datatype which can hold a larger amount of data (ie. LONG, CLOB or BLOB). The field has valuable data in it which I don't want to loose. Please tell me how I can successfully make the conversion and maintain the data without losing or corrupting the data.
    : :
    : Let's see if this works for you.
    : Assuming your first table is t1 with the following definition:
    : CREATE TABLE T1
    : ( ID NUMBER,
    : COL1 VARCHAR2(4000));
    : Your new table is T2
    : CREATE TABLE T2
    : (ID NUMBER,
    : COL1 LONG);
    : To migrate your existing data into the new table, T2
    : INSERT INTO T2 (COL1)
    : SELECT COL1 FROM T1;
    :
    : This technique works fine for VARCHAR2 to LONG conversion. You may give VARCHAR2 to CLOB|BLOB a try and tell me the results.
    :
    : Cheers,
    : XtrmLade
    :

    By doing what you suggested, I am inserting rows after the existing rows. Remember that the data already exist, and I currently have 341 records in the table. When I do an insert, it puts the data after the existing 341 rows, therefore, I end up with 682 rows. I did find another way: update(tablename) set col2 = col1. This works without inputting the new rows.

    I do have another question that you may be able to answer: How do I convert a datatype of clob to a varchar2(4000)?
Sign In or Register to comment.