aspose file tools*
The moose likes Oracle/OAS and the fly likes BLOB conversion Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "BLOB conversion" Watch "BLOB conversion" New topic
Author

BLOB conversion

Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

We need to view data stored in a BLOB column. Is there a conversion function to view the BLOB as a VARCHAR or such? The actual data stored in the BLOB is character data.
Thanks,
Jamie
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17259
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17259
    
    6

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
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
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: BLOB conversion