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.
Tell me about it!! Nothing I can do about canned software except deal with it!
Originally posted by Mark Spritzler:
If it stores character data, why is it a BLOB and not a CLOB?....
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!
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! )
There is a package utl_raw.
You can use
some_vc_data := UTL_RAW.CAST_TO_VARCHAR2( some_raw_data );
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
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)
And thats it, it'll be there now.
From Ask Tom's website.
Here is the entire thread.
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!
[ September 10, 2003: Message edited by: Jamie Robertson ]