Skip to Main Content
  • Questions

  • Viewing Data associated with BLOB Datatype

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Thiruvaiyaru.

Asked: August 14, 2000 - 5:09 pm UTC

Last updated: November 12, 2003 - 1:33 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Tom,

In my queries I use a column that is BLOB datatype.
(I use the column in the where condition)
Is there a way in SQL*PLUS where I could find out
where the BLOB datatype column is fetching it's results
from. For eg:- I have a column "content_bfile" which
is of datatype BLOB (Binary File LOB). When I do a
select on the column it gives the following error:-
"SP2-0678: Column or attribute type can not be displayed by SQL*Plus". But when I use the column in the where condition
I get the desired results. How can I see the data
associated with column CONTENT_BFILE.


Thanks
Sai

and Tom said...

SQLPlus just refuses to print raw data (be careful with the following, if you are using telnet or something -- and the file has control characters -- it can really jam up your session)

We can use dbms_lob.substr to get upto 2000 bytes (any 2000 -- I'm getting the first 2000) and then "cast" them into a varchar2. This cast just flips the datatype on the internal data -- it does no conversion. What you see is what you get...

select utl_raw.cast_to_varchar2(
dbms_lob.substr(content_bfile,2000,1)), ....
from T
/

the utl_raw package may not be installed in your database. If
not, cd $ORACLE_HOME/rdbms/admin and do an ls *raw*. You'll
find a .sql and .plb file. Install the .sql file and then the
.plb file WHEN CONNECTED AS SYS OR INTERNAL using svrmgrl.
Then, you'll have it.



Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

numeric error

reddy v, January 31, 2002 - 9:30 am UTC

Hi Tom ,
I am getting the following error b_blob is my blob field..

Invalid SQL statement: select utl_raw.cast_to_varchar2( dbms_lob.substr(b_blob,2000,1)) from blob_test (WWV-13005)
ORA-06502: PL/SQL: numeric or value error (WWV-11230)

reddy


Tom Kyte
January 31, 2002 - 6:10 pm UTC 

that just means whatever webdb/portal object you are selecting that into isn't big enough.  You can see for yourself that it works in general:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( b_blob blob );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2          l_raw raw(10000);
  3  begin
  4          l_raw := hextoraw( rpad( to_char(ascii('A'),'fmXX'), 20000, to_char(ascii('A'),'fmXX') ) );
  5          insert into t values ( l_raw );
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select utl_raw.cast_to_varchar2( dbms_lob.substr( b_blob, 2000, 1 ) ) from t;

UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(B_BLOB,2000,1))
-----------------------------------------------------------------------------------------------------------------------------------
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
....

A reader, July 04, 2002 - 6:46 pm UTC


Excellent!

David, November 12, 2003 - 1:33 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here