• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1025
  • Last Modified:

View BLOB Data

How do i look at BLOB data?
0
xoxomos
Asked:
xoxomos
  • 8
  • 6
  • 3
  • +1
1 Solution
 
Alex MatzingerDatabase AdministratorCommented:
This might be what you are looking for

http://www.dba-oracle.com/t_read_blob.htm
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
sdstuberCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
xoxomosAuthor Commented:
I believe it is just character data.  It's an exam with answers submitted by a student.
0
 
slightwv (䄆 Netminder) Commented:
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
 
xoxomosAuthor Commented:
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
 
xoxomosAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
oops..  backwards:

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

0
 
slightwv (䄆 Netminder) Commented:
Is this an exam question for you?
0
 
xoxomosAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
xoxomosAuthor Commented:
Yep, this is absolutely what we're looking for.  :-)
0
 
slightwv (䄆 Netminder) Commented:
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
 
xoxomosAuthor Commented:
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
 
sdstuberCommented:
if all you want to do is convert a BLOB to CLOB  why not use


dbms_lob.converttoclob

?
0
 
slightwv (䄆 Netminder) Commented:
I forgot about that call. I guess it could have been used in the function instead of the loop.
0
 
sdstuberCommented:
I guess I was too slow, oh well
0

Featured Post

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!

  • 8
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now