Solved

View BLOB Data

Posted on 2011-03-09
18
993 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Not able to drop or recreate an Oracle stored procedure 1 67
Import and exporting Oracle Data with encrypted columns 4 66
date show only hh:mm 2 48
oracle query 4 26
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

738 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