Get the LENGTH of a LONG RAW

I have a table with a column of data type LONG RAW. How do I determine the size (in bytes) of the data in this column?

If I c开发者_JAVA技巧all the LENGTH function on it, it raises ORA-00932: inconsistent datatypes: expected NUMBER got LONG BINARY.

Just in case you think it: UTL_RAW.LENGTH raises ORA-00997: illegal use of LONG datatype :)

(Yes, I know LONG RAW is deprecated - the question came up due to some old software that might require it)

I don't think it's possible to manipulate LONG RAWs longer than 32k in PLSQL. Here is a java procedure that returns the length of a LONG RAW.

First, the setup:

SQL> CREATE TABLE my_table (ID NUMBER, my_long_raw_column LONG RAW);

Table created

SQL> INSERT INTO my_table VALUES (1, utl_raw.cast_to_raw('123456789'));

1 row inserted

The java class (my java is a bit rusty):

SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Raw" AS
  2  import java.io.*;
  3  import java.sql.*;
  4  import oracle.jdbc.driver.*;
  5  
  6  public class Raw {
  7  
  8     public static int getLength(int pk) throws SQLException,IOException {
  9  
 10        Connection conn = new OracleDriver().defaultConnection();
 11  
 12        PreparedStatement ps = conn.prepareStatement
 13           ( "SELECT my_long_raw_column FROM my_table WHERE id = ?" );
 14        ps.setInt( 1, pk);
 15        ResultSet rs = ps.executeQuery();
 16  
 17        int len = 0;
 18        if (rs.next()) {
 19           InputStream is = rs.getBinaryStream(1);
 20           int nb = is.read(new byte[1024]);
 21           while (nb>0) {
 22              len += nb;
 23              nb = is.read(new byte[1024]);
 24           }
 25        } else
 26           len = -1;
 27  
 28        rs.close();
 29        ps.close();
 30
 31        return len;
 32     }
 33  }
 34  /

Java created

Let's call it:

SQL> CREATE OR REPLACE
  2  FUNCTION get_lr_length(p_id NUMBER) RETURN NUMBER
  3  AS LANGUAGE JAVA
  4  NAME 'Raw.getLength(int) return int';
  5  /

Function created

SQL> select get_lr_length(id) from my_table;

GET_LR_LENGTH(ID)
-----------------
                9

I've tested the function with larger than 32k fields and it seems to work.

As long as the data in the column does not exceed 16,383 bytes, you can solve this with a PL/SQL function, e.g.

CREATE OR REPLACE FUNCTION get_lr_length (id IN NUMBER)
  RETURN NUMBER IS
  raw_data LONG RAW;
  hex_data VARCHAR2(32767);
  len      NUMBER;
BEGIN
  SELECT my_long_raw_column INTO raw_data
  FROM my_table
  WHERE my_table.id = get_lr_length.id;
  hex_data := RAWTOHEX(raw_data);
  len := LENGTH(hex_data) / 2;
  RETURN len;
END get_lr_length;

Unfortunately, a LONG RAW can hold up to 2GB...

One dirty trick, which might help if you're playing with a small test database: copy all data in a table with a BLOB instead of a LONG RAW.

create table START(ID int not null, VAL long raw);
... inserts
create table START_BLOB(ID int not null, VAL blob);
insert into START_BLOB(ID,VAL) select ID,to_lob(VAL) from STAR;
select ID,length(VAL) from START_BLOB;