获得长RAW的长度

问题描述

我有一个表，该表的列的数据类型为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?

如果我在其上调用LENGTH函数，它将引发ORA-00932: inconsistent datatypes: expected NUMBER got LONG BINARY.

If I call the LENGTH function on it, it raises ORA-00932: inconsistent datatypes: expected NUMBER got LONG BINARY.

以防万一:UTL_RAW.LENGTH引发ORA-00997: illegal use of LONG datatype:)

(是的，我知道LONG RAW已过时-由于某些旧软件可能需要它而出现了问题)

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

推荐答案

我认为在PLSQL中无法操作超过32k的LONG RAW.这是一个返回LONG RAW长度的Java过程.

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.

首先，设置:

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

java类(我的Java有点生锈):

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

我们叫它:

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

我已经使用大于32k的字段测试了该函数，并且似乎可以正常工作.

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

这篇关于获得长RAW的长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！