If it stores character data, why is it a BLOB and not a CLOB? Usually when I need to view data in a CLOB in a simple query in something like TOAD. I always use the SUBSTR(<<string>>,<<start at>>,<<length>> function to display the beginning portion. because you can never know how much data is actually in the CLOB or BLOB since it can store up to about 4 Gigs of data. Mark
Originally posted by Mark Spritzler: If it stores character data, why is it a BLOB and not a CLOB?....
Tell me about it!! Nothing I can do about canned software except deal with it! I'll try your suggestion, but I hope you mean SUBSTR(<<clob_column_name>>,<<start at>>,<<length>> ) . Otherwise, that is kind of a circular answer, because the original question is how to get it into a String/VARCHAR! Jamie
and you are right in that I am trying to preview the first 100 characters or so in SQL*Plus to see what's in there. So really I want to do something like: Select substr( to_char( BLOB_column ), 1, 100 ) FROM table_x but I don't know how to convert a BLOB into readable characters ( except in java, but I don't want to go their if I don't have to! ) thanks, Jamie
All that function effectively does is change the datatype marker of the raw into varchar2 and just leaves the data as is. On the way into a RAW, you can use utl_raw.cast_to_raw to conver a varchar2 into a raw type (no conversion, just a cast) UTL_RAW may not be installed on your system. If not (there would be a grant execute to public and a public syonym if it is installed), simply: o cd $ORACLE_HOME/rdbms/admin o using SVRMGRL o connect as INTERNAL or SYS (and only these 2 users, none other) o @utlraw o @prvrawb.plb And thats it, it'll be there now.
From Ask Tom's website. Here is the entire thread. Good Luck Mark
Mark, this worked great for the Character data! Now I've discovered that 1/4 for the BLOB's are in fact not Character data, but are records in various formats. If anyone can help me decode these into readable data that would be appreciated Record Layout within each BLOB:
Don't hurt your brain trying to help me as this is officially 'the vendors' problem. But brownie points are good too come contract negotiation time! Jamie [ September 10, 2003: Message edited by: Jamie Robertson ]