Long Raw data type
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Long Raw data type

  1. #1
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995

    Long Raw data type

    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.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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 :(

  3. #3
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Code:
    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

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    "Select * from table" will work if the table has clob data type.
    Code:
    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

  7. #7
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Sorry, to be precise "It fell over within the App..."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width