Finding the length of LONG RAW data

[ChrisHunt]

I've been asked to find the size of the largest value in a LONG RAW column of a table. Heaven knows why they're using LONG RAWs instead of BLOBs, but it's not in my power to change that.

I thought I'd start by getting the length of the value held in one row - going on to find the maximum should be trivial but possibly time consuming.

I can't use the LENGTH() or VSIZE() functions on a LONG RAW in regular SQL. I've tried using PL/SQL, but I get an ORA-6502 Numeric or Value Error when I try to select a value into a PL/SQL variable, like this for example:

DECLARE
   CURSOR cDoc IS
      SELECT image_data
      FROM   documents
      WHERE  id = 228;
BEGIN

   FOR rDoc IN cDoc LOOP
      NULL;
   END LOOP; 
END;

Selecting [tt]TO_LOB(image_data)[/tt] gives another error message: "ORA-00932: inconsistent datatypes: expected - got BINARY".

Does anybody know how I can do this?

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[jimirvine]

Surprisingly little on google for this, but I found one that might help within certain limits i.e. values < 32K

http://www.dbforums.com/showthread.php?t=828265

As for converting from long raw to lob using to_lob, this will only work with an insert into ... select ... query.:

    create table raw1 (x long raw);
    insert into raw1 values ('1233455');
    
    select to_lob(x)
    from raw1;
    
    create table lblob (x blob);
    insert into lblob select tx from raw1;
    insert into lblob select to_lob(x) from raw1;

 

[jimirvine]

Apologies, this is what I meant to post in code:

SQL>    create table raw1 (x long raw);

Table created.

SQL>     insert into raw1 values ('1233455');

1 row created.

SQL>
SQL>     select to_lob(x)
  2      from raw1;
    select to_lob(x)
           *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got LONG BINARY


SQL>
SQL>     create table lblob (x blob);

Table created.

SQL>     insert into lblob select x from raw1;
    insert into lblob select x from raw1
                             *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


SQL>     insert into lblob select to_lob(x) from raw1;

1 row created.

 

[Dagon]

This seems to work in PL/SQL:

create table raw1 (x long raw);

insert into raw1 values ('1233455');

declare
v_result varchar2(5000);
begin
select x
into v_result
from raw1;
dbms_output.put_line(to_number(v_result));
end;

A cursor for loop won't work because it will try to create a PL/SQL record structure containing a long raw column.

 

[ChrisHunt]

Thanks guys, but we're dealing with LONG data here (anything up to 2GB) - reading it into a 32K Varchar isn't going to do the job.

The cursor for loop was only one approach I tried, neither of these work either...

DECLARE
   CURSOR cDoc IS
      SELECT image_data
      FROM   documents
      WHERE  id = 228;

   v_long LONG RAW;
BEGIN
   OPEN cDoc;
   
   FETCH cDoc INTO v_long;
   WHILE cDoc%FOUND LOOP
      null;
      
      FETCH cDoc INTO v_long;
   END LOOP;
   
   CLOSE cDoc;
END;

DECLARE
   v_long LONG RAW;
BEGIN
   SELECT image_data
   INTO   v_long
   FROM   documents
   WHERE  id = 228;
END;

Surely there must be some way to get stuff out of a LONG once it's been put in?

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[JCool32]

I am faced with the exact problem you faced when posted this thread.

Did you ever find a solution?

thx!

 

[dstxaix]

Hi Guys,

I was browsing for a similar solution and found the below link. I do not want to take credit for something I did not do. Please let me know if it helped you guys.So, here's teh link to the solution.

http://www.techonthenet.com/oracle/questions/long_length.php

 

[Thargy]

Gents,

according to the illustrious Mr Kyte see

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1037232794454

In Oracle8i release 8.1 and up, yes:

create table new_table as select c1, c2, TO_LOB(c3) from old_table;

or 

insert into new_table select c1, c2, to_lob(c3) from old_table;


In Oracle9i, you can even:

alter table old_table modify ( c clob );

to convert it.

So chris, how about a swift alter table statement.
I can't try it as I only have access to 10g at work, which would prove nothing about your 9i question.

Regards

T


Grinding away at things Oracular