Long datatypes Vs LOB

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!

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.

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

Thanks for the info.
R.

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!

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]