Skip to Main Content
  • Questions
  • long raws -- viewing and getting the length

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Devinder .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: October 29, 2005 - 11:16 am UTC

Version:

Viewed 10K+ times! This question is

You Asked

Is there a way to see what is in a long raw
column? If there is some value in a long raw
column Can I determine what is the size of
the long raw column.



and Tom said...



Using C -- yes.

Using PLSQL -- no (unless the long raw is always 32k or less).

If the long raw is 32k or less, you can do some things with it. Lets say you had a table IMAGE with a long raw in it. You can do something like this:

scott@8.0> create or replace function lr_length( p_rowid in rowid ) return number
2 as
3 l_raw long raw;
4 begin
5 select image into l_raw
6 from image
7 where rowid = p_rowid;
8
8 if ( l_raw is null ) then
9 return null;
10 else
11 return utl_raw.length(l_raw);
12 end if;
13 exception
14 when others then return NULL;
15 end;
16 /

Function created.

scott@8.0>
scott@8.0> create or replace function lr2hex( p_rowid in rowid ) return varchar2
2 as
3 l_raw long raw;
4 l_str varchar2(2000);
5 l_len number;
6 begin
7 select image into l_raw
8 from image
9 where rowid = p_rowid;
10
10 if ( l_raw is not null ) then
11 l_len := least( utl_raw.length( l_raw ), 1000 );
12 l_raw := utl_raw.substr(l_raw,1,l_len);
13 end if;
14 l_str := l_raw;
15
15 return l_str;
16 exception
17 when others then return NULL;
18 end;
19 /

Function created.

scott@8.0>
scott@8.0> column hex format a40
scott@8.0> select rowid, lr_length( rowid ) len, lr2hex( rowid ) hex from image
2 /

ROWID LEN HEX
------------------ ---------- ----------------------------------------
AAABy+AAcAAAAMyAAA 17 2F746D702F55303030303434312E747874
AAABy+AAcAAAAMyAAB 17 2F746D702F55303030303434322E646F63
AAABy+AAcAAAAMyAAC 13 2F746D702F5530303030343634
AAABy+AAcAAAAMyAAE 13 2F746D702F5530303030343633
AAABy+AAcAAAANjAAB
AAABy+AAcAAAANkAAA 34 626D696C6C65724073756E636F61737473636965
6E74696669632E636F6D0D0A0D0A


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.


In Oracle8.0 and up, blobs replace LONG RAWS and the dbms_lob package gives you 100% access to the length and contents of binary data.




Rating

  (12 ratings)

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

Comments

A reader, March 28, 2001 - 12:18 pm UTC

the location of the UTL_RAW down load has moved.
The question is where??

A reader, March 28, 2001 - 2:02 pm UTC

My bad. Look for the files on the local install of Oracle.
Everything drops in place.

will above solution be applied to Long also

kumar, November 25, 2002 - 11:52 am UTC

how can I apply the above solution to a case of finding the
length of Long column instead of Long Raw. I have to write some temp queries to be run from sql.
Thanks & regards


Tom Kyte
November 25, 2002 - 12:42 pm UTC

scott@8.0> create or replace function long_length( p_rowid in rowid ) return
number
2 as
3 l_txt long;
4 begin
5 select image into l_txt
6 from image
7 where rowid = p_rowid;
8 return length(l_txt);
13 exception
14 when others then return NULL;
15 end;
16 /



Application Developer

Dawar Naqvi, May 13, 2003 - 2:19 pm UTC

Tom,

I have an existing table below.
Here is the desc of table:

desc loft_glo_terms

NAME Null? Type
------------------------------- --------- -----
TERMS VARCHAR2(1000)
DEFINITION VARCHAR2(3000)

From sql navigator I cannot see complete data in some of the fileds for the column called definition.Because of the lenght of the data. Do I nedd to change the datatype?f yes what will be the data type and how it will work.
Any thought will appreciate as usual.

cheers,
Dawar, Burbank USA



Tom Kyte
May 13, 2003 - 5:09 pm UTC

time to call the makers of sql navigator i guess?

i've never used it myself.

Devloper

Bipin Ganar, October 14, 2003 - 10:07 am UTC

Hi Tom,
I'm not able to find how can i read image.
I have table Emp_Sign(Empno varchar2(10),Sign Long Raw)

I'm Cropping the Tiff file and with read_image_file i stored in database it shows like

SQL >Select * from Emp_Sign;

Empno Sign
----- -------
E005 4

Now , when i read the image from the developer(Forms6) it shows on the screen as i'm using same tool for storing and retrieving . But if i use VB or some other application it is difficult get sign on the screen.
and second thing while storing sing by using read_image_file how can i reduce the size of the sign in long raw column

length of raw over 32k

Anandhi, November 06, 2003 - 4:34 pm UTC

Could you please provide a sample c program to get the length of a long raw column?

Tom Kyte
November 07, 2003 - 8:09 am UTC

you'd have to either

a) use oci to piecewise fetch and add up the lengths of the pieces
b) use to_lob to put into a gtt and then dbms_lob.getlength would work

I'll do (b), it is easier and more "general purpose".  I'll just ask that you select out the ROWID of the row that points to the LONG RAW and give me the table name, column name and rowid of the row you want the long raw length of.  We'll use a global temporary table to help us out:


ops$tkyte@ORA9I> create global temporary table gtt
  2  ( x blob )
  3  on commit delete rows
  4  /
 
Table created.
 
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> create or replace
  2  function length_of_longraw( p_tname in varchar2,
  3                              p_cname in varchar2,
  4                              p_rowid in rowid ) return number
  5  AUTHID CURRENT_USER
  6  as
  7      pragma autonomous_transaction;
  8      l_length number;
  9  begin
 10      execute immediate
 11      'insert into gtt(x)
 12       select to_lob(' || p_cname || ')
 13         from ' || p_tname || '
 14        where rowid = :x' using p_rowid;
 15
 16      select dbms_lob.getlength(x) into l_length
 17        from gtt;
 18
 19      commit;
 20
 21      return l_length;
 22  end;
 23  /
 
Function created.
 
 
ops$tkyte@ORA9I> exec dbms_output.put_line( length_of_longraw( 'rfp.image', 'image', chartorowid('AAAKPrAAzAAABfLAAD') ))
5541376
 
PL/SQL procedure successfully completed.


 

A reader, November 07, 2003 - 11:03 am UTC

thanks a lot!

Anandhi

A reader, November 07, 2003 - 11:20 am UTC

And this procedure (using to_lob to convert long raw to blob) will work only if the length is < 64K?

Thanks
Anandhi

Tom Kyte
November 07, 2003 - 2:47 pm UTC

the example used a 5.5 meg long raw.

so no, it does not have that limit...

Long Raw length

Doug Brown, October 28, 2005 - 3:36 pm UTC

Thanks for the great and creative way to use the tools provided by oracle. This saves me tons of time and money!!!

Length of a long raw column

A reader, October 28, 2005 - 11:25 pm UTC

Ok your function returns a numeric value.  but its half of what I had previously computed and wondered if you could se any flaws in either approach.  Previously I had used the rawtohex(<column>) approach in a plsql routine.  This obviously would not work on a column that would be longer than 32K but on those columns I was prepared to take only a smaller portion in hopes of having a small pct in that category.  But then I thought I might try and bypass the entire filling of a variable by just applying the length fuction to the rawtohex in one call 
DECLARE
 hex       varchar2(32767);
 rawparam  varchar2(32767);
 rawlen    number;

 CURSOR a1 is
 SELECT
a.OLE_OBJ,
a.OLE_OBJL
FROM SAPR3."/CPC/TOLEOBJ" a
for update;

 BEGIN
 vRaw :=0;

 FOR arec in a1 LOOP

 -- hex      := rawtohex(arec.ole_obj);
 rawlen      := length(rawtohex(arec.ole_obj));

 -- rawlen := utl_raw.length(arec.OLE_OBJ);
 
  update SAPR3."/CPC/TOLEOBJ"
  set           OLE_OBJL = rawlen
  where current of a1;

 END LOOP;
 END;

And it happily completed without errors and set all columns to a value.  

SQL> select min(ole_objl),max(ole_objl)
  2  from SAPR3."/CPC/TOLEOBJ";

MIN(OLE_OBJL) MAX(OLE_OBJL)
------------- -------------
        48000         48000

But I was not entirely confident with this and continued digging until I found your function example using to gtt and the function to convert to lob and then return the length.  And this too worked without a hitch but all values returned from this function were exactly half of what the example I show returned.  Do you see any reason for the discrepancy?

  1  update SAPR3."/CPC/TOLEOBJ" A
  2  set OLE_OBJL = ( select length_of_longraw('SAPR3."/CPC/TOLEOBJ"','OLE_OBJ',rowid )
  4                   from SAPR3."/CPC/TOLEOBJ" b
  5*                  where b.rowid = a.rowid)
SQL> /

48846 rows updated.

 select min(ole_objl),max(ole_objl)
SQL> /

MIN(OLE_OBJL) MAX(OLE_OBJL)
------------- -------------
        24000         24000 

Tom Kyte
October 29, 2005 - 11:16 am UTC

rawtohex by definition doubles the size of the raw (each byte of raw takes two bytes of HEX)



Length of Raw

Doug Brown, October 30, 2005 - 7:24 pm UTC

Thanks for clearing that up. In this case it didn't hurt I suppose because of the relatively small size of the attachments but this is good to know for future considerations.

A reader, September 10, 2006 - 5:56 am UTC


More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here