asked on

Easy way to preview BLOB field with SELECT?

Using Oracle 8i, is there any easy way to preview the contents of a BLOB as a field result within a normal select?

For instance, suppose a table t_data has two fields... item_id (integer correlating the data to a real item somewhere) and item_data (the blob). For casual debugging purposes, it would be great to be able to preview the first part of the blob's data in hex dump format. Kind of like:

SELECT (TO_HEXDUMP(item_data, 1000)) from t_data where item_id = 5

and have it print the first 1000 bytes of item_data as:

45 33 39 57 71 36 42 30 0A 0D 4D 51 3C Fake Text..Not
41 6B 52 44 39 3C 41 41 6A 0A 0D 37 30 real values..A

(I made up the hex values).

Obviously something like that would be mostly useless in a program, but it would be really helpful when trying to debug a program to quickly verify whether the right values are being written to the blob in the first place (or find out what values are being read).

ASKER CERTIFIED SOLUTION

yoren

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yoren

Oops! The code above prints out 1 too many characters. Change the "rawline :=..." to this:

rawline := utl_raw.substr(data,i,least(bytes_per_line,max_i-i-1));

waynezhu

You can use DBMS_LOB.SUBSTR, for example,

set serveroutput on
SELECT dbms_lob.substr(item_data, 1000) from t_data where item_id = 5;

Good luck.

SOLUTION

belem

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

belem

pardon
select item_id, utl_raw.cast_to_varchar2(dbms_lob.substr(item_data,100)) from t_data where item_id = 5;

jskubick

ASKER

Yoren --

I compiled the function, but when I try to call it I get:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'TO_HEXDUMP'

Belem --

Your solution worked! I'm still planning to give 200 points to Yoren as soon as it works because he spent lots of time working on it (and because it will be a little better for hardcore binary files, as opposed to ascii files that are simply stored in LOBs), but you'll be getting at least 100 points too for coming up with a good answer as well :-)

belem

Thank you

yoren

iskubick,

I added a few parameters to the function to allow it preview any part of the LOB, so you have to change
your line of code a bit. The exact code you need to run is at the bottom of this message, but you'll need to recreate the function as listed here.

Here's the syntax:

TO_HEXDUMP([position], [length], [line display length])
where
[position] is an integer (use 1 for the start of the lob)
[length] is an integer; number of characters to read
[line...] is an integer; screen width (probably 80)

I've made it a bit easier to call the function by making the last parameter optional. Recreate the function from the SQL below:

create or replace function to_hexdump
(data in raw,
pos in number,
len in number,
linesize in number := 80)
return varchar2
is
i number;
max_i number;
dump varchar2(10000) := null;
rawline raw(255);
charline varchar2(255);
bytes_per_line number;
begin
bytes_per_line := linesize/4 - 1;
i := pos;
max_i := least(pos+len,utl_raw.length(data)+1);

while (i < max_i ) loop
charline := null;
rawline := utl_raw.substr(data,i,least(bytes_per_line,max_i-i-1));

for j in 1..utl_raw.length(rawline) loop
charline := charline || rawtohex(utl_raw.substr(rawline,j,1)) || ' ';
end loop;

dump := dump || rpad(charline,(bytes_per_line*3)+2,' ')
||
translate(utl_raw.cast_to_varchar2(rawline),
chr(9)||chr(10)||chr(11)||chr(12)||chr(13),
'.....') || chr(10);
i := i + bytes_per_line;
end loop;

return dump;
end;
/

Here's how to call it from SQL*Plus. Note that I've omitted the optional line display parameter. This will give you a nice dump:

SELECT TO_HEXDUMP(item_data, 1,1000) from t_data where item_id = 5;

Let me know if it doesn't work for you!

Moondancer

Greetings.

This question has been locked with a Proposed Answer, and remains open today. Questions in the LOCKED versus OPEN question queue draw little to zero attention.

If the Proposed Answer did not serve your needs, please reject it and comment with an update. If the Proposed Answer helped you, please accept it to grade and close this question. If you need help splitting points between multiple experts, please comment here with details so we can help you.

EXPERTS -> Please guide me here in terms of closing recommendations if the Asker does not respond in 4 days.

Thanks to all,
Moondancer - EE Moderator

modulo

Dear: yoren

I've rejected your proposed answer as Experts Exchange holds an experiment to work without the answer button.

See: https://www.experts-exchange.com/jsp/communityNews.jsp
Paragraph: Site Update for Wednesday, November 06, 2002

By this rejection the Asker will be notified by mail and hopefully he will take his responsibility to finalize the question or post an additional comment.
The Asker sees a button beside every post which says "Accept This Comment As Answer" (including rejected answers) -- so if he/she thinks yours is the best, you'll be awarded the points and the grade.

Thanks !

modulo

Community Support Moderator
Experts Exchange

anand_2000v

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
points to be split between Yoren and belem
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

anand_2000v
EE Cleanup Volunteer