oracle 获取 LONG RAW 的长度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5497238/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get the LENGTH of a LONG RAW
提问by Jeffrey Kemp
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?
我有一个包含数据类型列的表LONG RAW。如何确定此列中数据的大小（以字节为单位）？
If I call the LENGTHfunction on it, it raises ORA-00932: inconsistent datatypes: expected NUMBER got LONG BINARY.
如果我调用LENGTH它的函数，它会引发ORA-00932: inconsistent datatypes: expected NUMBER got LONG BINARY.
Just in case you think it: UTL_RAW.LENGTHraises ORA-00997: illegal use of LONG datatype:)
以防万一你认为：UTL_RAW.LENGTH加薪ORA-00997: illegal use of LONG datatype:)
(Yes, I know LONG RAWis deprecated - the question came up due to some old software that might require it)
（是的，我知道LONG RAW不推荐使用 - 由于某些可能需要它的旧软件而提出问题）
采纳答案by Vincent Malgrat
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.
我认为在 PLSQL 中操作长于 32k 的 LONG RAW 是不可能的。这是一个返回 LONG RAW 长度的 java 过程。
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):
java类（我的java有点生疏）：
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.
我已经测试了大于 32k 字段的函数，它似乎有效。
回答by Jeffrey Kemp
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.
只要列中的数据不超过16,383字节，就可以用PL/SQL函数解决这个问题，例如
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...
不幸的是，LONG RAW 最多可以容纳 2GB...
回答by pascal
One dirty trick, which might help if you're playing with a small test database: copy all data in a table with a BLOBinstead of a LONG RAW.
一个肮脏的技巧，如果您正在使用小型测试数据库，这可能会有所帮助：将表中的所有数据复制BLOB为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;
