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
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
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?
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
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
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