-
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
-
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
-
-
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
-
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]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|