• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

BLOB conversion

 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
reply
    Bookmark Topic Watch Topic
  • New Topic