Printable View
Guys/ gals
I was doing some research on the Long Raw data type and Oracle has said that it recommends converting that data type to a BLOB. Anyone know of any benifits that are gained by doing so other than being able to create a table as select * from.... and reorgs ?? I've got a database full of long raw data types holding jpgs and I'd like to gather enough fodder to justify getting rid of this old data type.
I was reading Johnathan Lewis' book this morning and just started on LOB's :) If I remember correctly his strongest argument against LONG's was that you couldn't actually do anything with them! But then there's no much you can do with a jpg inside Oracle anyway . . .
Looks to me that ease of administration is the only benefit you'll get. Sorry :(
Yea, that's what I was affraid of. I was hoping for some grand dios tale of how someone converted all their long raws to blobs and all their load times were cut in half. Ohhh well so much for wishful thinking :p
Thanks for the input.
The only advantage of using long raw isCode:10:21:40 SQL> create table test_raw ( id int, pict long raw) ;
Table created.
10:22:18 SQL> create table test_blob (id int , pict blob);
Table created.
SQL> desc test_raw
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
PICT LONG RAW
SQL> desc test_blob
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
PICT BLOB
PICT2 BLOB
SQL> alter table test_raw add somebig long ;
alter table test_raw add somebig long
*
ERROR at line 1:
ORA-01754: a table may contain only one column of type LONG
10:22:41 SQL> alter table test_raw add pict2 long raw ;
alter table test_raw add pict2 long raw
*
ERROR at line 1:
ORA-01754: a table may contain only one column of type LONG
10:23:02 SQL> alter table test_blob add pict2 blob;
Table altered.
1* insert into test_raw values (10, null)
10:24:51 SQL> /
1 row created.
10:24:51 SQL> commit;
Commit complete.
1* insert into test_blob values (10,null,null)
10:25:23 SQL> /
1 row created.
10:25:24 SQL> commit;
Commit complete.
10:25:26 SQL> select * from test_raw ;
ID P
---------- -
10
10:25:33 SQL> select * from test_blob ;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
10:25:42 SQL> alter table test_raw move ;
alter table test_raw move
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
10:26:24 SQL> alter table test_blob move ;
Table altered.
you can execute "select * from long_raw_table" in sqlplus
which you can't do with blob.
The question is do we really need to
select raw column in sqlplus? No.
But if you use BLOB/CLOB, you get more advantages.
1 BLOB can be stored in a diff tablespace,
away from data tablespace.
2 Reorg of table is easy. MOVE command works.
3 You can have any number of BLOB/CLOB columns in the same table.
Tamil
CLOBS are far more 'searchable' as well.
Never been able to do any string manipulation or searching using LONGs.
I've just converted a Db from 7 to 9.2 , changed all the LONGS to CLOBS and found that (even though they never use the columns - they're always blank) that the app does several 'SELECT * ...' which fall over. Had to move them all back to LONGs.
"Select * from table" will work if the table has clob data type.
TamilCode:SQL> desc my_table ;
Name Null? Type
----------------------- -------- ------------------------------------
ID NUMBER
MY_CLOB CLOB
SQL> L
1* select * from my_table
SQL> /
ID MY_CLOB
---------- ----------------
1 Test clob
Sorry, to be precise "It fell over within the App..."