Solved

View BLOB Data

Posted on 2011-03-09
18
975 Views
Last Modified: 2012-05-11
How do i look at BLOB data?
0
Comment
Question by:xoxomos
  • 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 76

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 73

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
 

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 76

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 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 76

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 76

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 73

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 76

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 73

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now