View BLOB Data

This might be what you are looking for

http://www.dba-oracle.com/t_read_blob.htm

>>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?

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

I believe it is just character data.  It's an exam with answers submitted by a student.

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;

Select allOpen in new window

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)

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.
:-) :-)

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.

Is this an exam question for you?

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.

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.

Yep, this is absolutely what we're looking for.  :-)

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;
/

Select allOpen in new window

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

if all you want to do is convert a BLOB to CLOB  why not use


dbms_lob.converttoclob

?

I forgot about that call. I guess it could have been used in the function instead of the loop.

I guess I was too slow, oh well