Solved

View BLOB Data

Posted on 2011-03-09
18
996 Views
Last Modified: 2012-05-11
How do i look at BLOB data?
0
Comment
Question by:xoxomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 3
  • +1
18 Comments
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 35085959
This might be what you are looking for

http://www.dba-oracle.com/t_read_blob.htm
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35086031
>>This might be what you are looking for

Google, first link?

>>How do i look at BLOB data?

Please define 'look at'.  Are you just wanting to see it in sqlplus or are you wanting to extract it using some tool and literally view the blob?  In other words, it the blob is a JPG, do you want to extract it and look at the image?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35086129
as slightwv pointed out above, it really depends on what the BLOB content is.

If it's a an image then you'll need to load that data into software that will display the picture.
If it's a pdf file then you'll need to load that data into software that supports the adobe file format.
If it's a music then you'll need to load that data into software that can play the mp3 or whatever format the music is in.

etc.

The basic idea is select it like you would any other column, but then save that data into a file.
DBMS_LOB has some routines to assist with BLOB processing, particularly if the content is large
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:xoxomos
ID: 35086411
I believe it is just character data.  It's an exam with answers submitted by a student.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35086483
Then try the example below.
drop table tab1 purge;
create table tab1(col1 blob);
insert into tab1 values(utl_raw.cast_to_raw('Hello'));
commit;

select utl_raw.cast_to_varchar2(col1) from tab1;

Open in new window

0
 

Author Comment

by:xoxomos
ID: 35086704
SQL> select utl_raw.cast_to_varchar2(data) from qti_asi_data;
ERROR:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual:
2980, maximum: 2000)
0
 

Author Comment

by:xoxomos
ID: 35086740
I'm seeing maybe first i need to:

Action: Do one of the following: 1. Make the LOB smaller before performing the conversion, for example, by using SUBSTR on CLOB 2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.
:-) :-)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35086743
Now wer're back to what are you wanting to do with the data.

Do you want to see it all or just get a sample?

To get a sample use dbms_lob.substr:

http://psoug.org/reference/dbms_lob.html

select utl_raw.cast_to_varchar2(dmbs_lob.substr(col1,1,2000)) from tab1;

To get it all, you'll need a PL/SQL loop and read it in 'chunks' for display.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 35086754
oops..  backwards:

select utl_raw.cast_to_varchar2(dmbs_lob.substr(col1,2000,1)) from tab1;

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35086764
Is this an exam question for you?
0
 

Author Comment

by:xoxomos
ID: 35088495
Nope.   A student took an essay type exam and the instructor does not have it although there is a key in Oracle that says it's there contained in a column called DATA defined as BLOB.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35088531
Ok, then try the substr command above to see the first 2000 characters.  If the first 2000 characters are what you are looking for, we can move forward with retrieving the rest.
0
 

Author Comment

by:xoxomos
ID: 35088729
Yep, this is absolutely what we're looking for.  :-)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35088829
Take a look at the simple function from:

http://forums.oracle.com/forums/thread.jspa?threadID=204993

Using that you should be able to do (using the sample code above:

select blob_to_clob(col1) from tab1;
CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)
RETURN CLOB
AS
	v_clob    CLOB;
	v_varchar VARCHAR2(32767);
	v_start	 PLS_INTEGER := 1;
	v_buffer  PLS_INTEGER := 32767;
BEGIN
	DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
	
	FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
	LOOP
		
	   v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
 
           DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
 
		v_start := v_start + v_buffer;
	END LOOP;
	
   RETURN v_clob;
  
END blob_to_clob;
/

Open in new window

0
 

Author Closing Comment

by:xoxomos
ID: 35088933
Actually we'll still need to somehow find out which table the student answers are on but it the solution will be very much along this line.
Thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35088965
if all you want to do is convert a BLOB to CLOB  why not use


dbms_lob.converttoclob

?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35089026
I forgot about that call. I guess it could have been used in the function instead of the loop.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35089113
I guess I was too slow, oh well
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question