Long datatypes Vs LOB

[hemanr]

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!

[rotem_fo]

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.

[TimHall]

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

[rotem_fo]

Thanks for the info.
R.

[hemanr]

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!

[TimHall]

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]