Solved

Easy way to preview BLOB field with SELECT?

Posted on 2002-05-02
11
7,361 Views
Last Modified: 2012-08-14
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).
0
Comment
Question by:jskubick
11 Comments
 
LVL 7

Accepted Solution

by:
yoren earned 100 total points
ID: 6987489
How about this?

SQL> list
  1  select to_hexdump(item_data,1,1000,50) hexdump
  2  from t_data
  3* where item_id = 5
SQL> /

HEXDUMP
--------------------------------------------------
45 33 39 57 71 36 42 30 0A 0D 4D  E39Wq6B0..M
51 3C 41 6B 52 44 39 3C 41 41 6A  Q<AkRD9<AAj
6A 0A 0D 37 30                    j..70



Here's the to_hexdump(data,start,length,linesize)
function:

create or replace function to_hexdump
  (data in raw,
   pos in number,
   len in number,
   linesize in number)
  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 - 2;
  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));

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

Expert Comment

by:yoren
ID: 6987501
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));
0
 
LVL 7

Expert Comment

by:waynezhu
ID: 6987701
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.
0
 
LVL 1

Assisted Solution

by:belem
belem earned 100 total points
ID: 6988444
select item_id, utl_raw.cast_to_varchar2(dbms_lob.substr(item_data,100)) from testc;from t_data where item_id = 5;
0
 
LVL 1

Expert Comment

by:belem
ID: 6988446
pardon
select item_id, utl_raw.cast_to_varchar2(dbms_lob.substr(item_data,100)) from t_data where item_id = 5;
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.

 

Author Comment

by:jskubick
ID: 6994069
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 :-)
0
 
LVL 1

Expert Comment

by:belem
ID: 6994175
Thank you
0
 
LVL 7

Expert Comment

by:yoren
ID: 6994316
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!

0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7078668
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
0
 

Expert Comment

by:modulo
ID: 7429529
Dear: yoren

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

See:        http://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
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9204540
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
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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

760 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

20 Experts available now in Live!

Get 1:1 Help Now