Long datatypes Vs LOB
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Long datatypes Vs LOB

  1. #1
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Hi gurus,

    Is is advisable to use long datatypes for large character fields in Oracle 8i?

    If I LOB is advisable, how would I convert the existing long dataype field?

    Also, how could I recreate a table having a long datatype?

    Thanks!
    Hemant

  2. #2
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    Hi,
    long datatypes cause chaining in Oracle.
    LOB is better because it can use different storage than long data type (which is stored in table row).

    In order to convert:
    1) Leave the long column in the old table and add a new LOB column using the ALTER TABLE command. The ALTER TABLE command is not able to change the type of a LONG column to a LOB column. LONG and LOB columns are two distinct datatypes, so it is not possible to assign a LONG column to a LOB column.
    2) Write the data in the LONG or LONG RAW to a flat file
    3) Use DBMS_LOB.LOADFROMFILE() procedure

    cheers,
    R.


  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    It's easier to use the TO_LOB function like:

    -- Your old table
    CREATE TABLE long_table (n NUMBER, long_col LONG);

    -- The new table
    CREATE TABLE lob_table (n NUMBER, lob_col CLOB);

    -- Load the data into the new table
    INSERT INTO lob_table
    SELECT n, TO_LOB(long_col) FROM long_table;

    See: http://otn.oracle.com/docs/products/...tion.htm#84934

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  4. #4
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268

    Thanks

    Thanks for the info.
    R.

  5. #5
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Hi Guys,

    Thanks a lot for the info.

    I had another question regarding the same.

    I cannot recreate a table using

    create table X as select * from Y;

    in the case where Y has a long column.

    Any suggestions regarding as to how to do this?

    Thanks!
    Hemant

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    To convert to a LOB do this:

    SQL> create table a (id NUMBER(10), description long);

    Table created.

    SQL> insert into a values (1,'one');

    1 row created.

    SQL> create table b (id NUMBER(10), description clob);

    Table created.

    SQL> insert into b select id, to_lob(description) from a;

    1 row created.

    SQL>

    To create a copy of a table with a LONG in it do this:

    SQL> create table a (id NUMBER(10), description long);

    Table created.

    SQL> insert into a values (1,'one');

    1 row created.

    SQL> create table b (id NUMBER(10), description long);

    Table created.

    SQL> DECLARE
    2 CURSOR c_data IS
    3 SELECT * FROM a;
    4 BEGIN
    5 FOR cur_rec IN c_data LOOP
    6 INSERT INTO b (id, description)
    7 VALUES (cur_rec.id, cur_rec.description);
    8 END LOOP;
    9 END;
    10 /

    PL/SQL procedure successfully completed.

    SQL> select count(*) from b;

    COUNT(*)
    ----------
    1

    SQL>

    Cheers

    [Edited by TimHall on 08-20-2002 at 09:40 AM]
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

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