Get the LENGTH of a LONG RAW

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

Get the LENGTH of a LONG RAW

Related

Recent Posts