Long Raw data type

[OracleDoc]

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.

[DaPi]

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 :(

[OracleDoc]

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

Thanks for the input.

[tamilselvan]

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.

The only advantage of using long raw is
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

[JMac]

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.

[tamilselvan]

"Select * from table" will work if the table has clob data type.

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

Tamil

[JMac]

Sorry, to be precise "It fell over within the App..."