Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Easy way to preview BLOB field with SELECT?

Posted on 2002-05-02
11
Medium Priority
?
7,646 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 7

Accepted Solution

by:
yoren earned 400 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 1

Assisted Solution

by:belem
belem earned 400 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
 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

719 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