SQL 如何在 Oracle 中获取 CLOB 列的大小（以字节为单位）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1797183/
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
How to get size in bytes of a CLOB column in Oracle?
提问by rag
How do I get the size in bytes of a CLOBcolumn in Oracle?
如何CLOB在 Oracle 中获取列的大小（以字节为单位）？
LENGTH()and DBMS_LOB.getLength()both return number of characters used in the CLOBbut I need to know how many bytes are used (I'm dealing with multibyte charactersets).
LENGTH()并且DBMS_LOB.getLength()都返回 中使用的字符数，CLOB但我需要知道使用了多少字节（我正在处理多字节字符集）。
采纳答案by rag
After some thinking i came up with this solution:
经过一番思考，我想出了这个解决方案：
LENGTHB(TO_CHAR(SUBSTR(<CLOB-Column>,1,4000)))
SUBSTRreturns only the first 4000 characters (max string size)
SUBSTR仅返回前 4000 个字符（最大字符串大小）
TO_CHARconverts from CLOBto VARCHAR2
TO_CHAR转换CLOB为VARCHAR2
LENGTHBreturns the length in Bytes used by the string.
LENGTHB返回字符串使用的字节长度。
回答by Andrew Spencer
I'm adding my comment as an answer because it solves the original problem for a wider range of cases than the accepted answer. Note: you must still know the maximum length and the approximate proportion of multi-byte characters that your data will have.
我将我的评论添加为答案，因为它解决了比接受的答案更广泛的案例的原始问题。注意：您仍然必须知道数据将具有的最大长度和多字节字符的大致比例。
If you have a CLOB greater than 4000 bytes, you need to use DBMS_LOB.SUBSTR rather than SUBSTR. Note that the amountand offsetparameters are reversed in DBMS_LOB.SUBSTR.
如果 CLOB 大于 4000 字节，则需要使用 DBMS_LOB.SUBSTR 而不是 SUBSTR。请注意，数量和偏移量参数在 DBMS_LOB.SUBSTR 中是相反的。
Next, you may need to substring an amount less than 4000, because this parameter is the number of characters, and if you have multi-byte characters then 4000 characters will be more than 4000 byteslong, and you'll get ORA-06502: PL/SQL: numeric or value error: character string buffer too smallbecause the substring result needs to fit in a VARCHAR2 which has a 4000 byte limit. Exactly how many characters you can retrieve depends on the average number of bytes per character in your data.
接下来，您可能需要将数量小于 4000 的子串化，因为这个参数是字符数，如果您有多字节字符，那么 4000 个字符将超过 4000个字节长，您将得到ORA-06502: PL/SQL: numeric or value error: character string buffer too small因为子串结果需要适合具有 4000 字节限制的 VARCHAR2。您可以检索的确切字符数取决于数据中每个字符的平均字节数。
So my answer is:
所以我的回答是：
LENGTHB(TO_CHAR(DBMS_LOB.SUBSTR(<CLOB-Column>,3000,1)))
+NVL(LENGTHB(TO_CHAR(DBM??S_LOB.SUBSTR(<CLOB-Column>,3000,3001))),0)
+NVL(LENGTHB(TO_CHAR(DBM??S_LOB.SUBSTR(<CLOB-Column>,6000,6001))),0)
+...
where you add as many chunks as you need to cover your longest CLOB, and adjust the chunk size according to average bytes-per-character of your data.
您可以根据需要添加尽可能多的块来覆盖最长的 CLOB，并根据数据的每个字符的平均字节数调整块大小。
回答by TobiK
Try this one for CLOB sizes bigger than VARCHAR2:
对于大于 VARCHAR2 的 CLOB 大小，试试这个：
We have to split the CLOB in parts of "VARCHAR2 compatible" sizes, run lengthb through every part of the CLOB data, and summarize all results.
我们必须将 CLOB 分成“VARCHAR2 兼容”大小的部分，遍历 CLOB 数据的每个部分，并汇总所有结果。
declare
my_sum int;
begin
for x in ( select COLUMN, ceil(DBMS_LOB.getlength(COLUMN) / 2000) steps from TABLE )
loop
my_sum := 0;
for y in 1 .. x.steps
loop
my_sum := my_sum + lengthb(dbms_lob.substr( x.COLUMN, 2000, (y-1)*2000+1 ));
-- some additional output
dbms_output.put_line('step:' || y );
dbms_output.put_line('char length:' || DBMS_LOB.getlength(dbms_lob.substr( x.COLUMN, 2000 , (y-1)*2000+1 )));
dbms_output.put_line('byte length:' || lengthb(dbms_lob.substr( x.COLUMN, 2000, (y-1)*2000+1 )));
continue;
end loop;
dbms_output.put_line('char summary:' || DBMS_LOB.getlength(x.COLUMN));
dbms_output.put_line('byte summary:' || my_sum);
continue;
end loop;
end;
/
回答by user5534142
NVL(length(clob_col_name),0) works for me.
NVL(length(clob_col_name),0) 对我有用。
回答by schlebe
The simple solution is to cast CLOB to BLOB and then request length of BLOB !
简单的解决方案是将 CLOB 转换为 BLOB，然后请求 BLOB 的长度！
The problem is that Oracle doesn't have a function that cast CLOB to BLOB, but we can simply define a function to do that
问题是 Oracle 没有将 CLOB 转换为 BLOB 的函数，但我们可以简单地定义一个函数来做到这一点
create or replace
FUNCTION clob2blob (p_in clob) RETURN blob IS
v_blob blob;
v_desc_offset PLS_INTEGER := 1;
v_src_offset PLS_INTEGER := 1;
v_lang PLS_INTEGER := 0;
v_warning PLS_INTEGER := 0;
BEGIN
dbms_lob.createtemporary(v_blob,TRUE);
dbms_lob.converttoblob
( v_blob
, p_in
, dbms_lob.getlength(p_in)
, v_desc_offset
, v_src_offset
, dbms_lob.default_csid
, v_lang
, v_warning
);
RETURN v_blob;
END;
The SQL command to use to obtain number of bytes is
用于获取字节数的 SQL 命令是
SELECT length(clob2blob(fieldname)) as nr_bytes
or
或者
SELECT dbms_lob.getlength(clob2blob(fieldname)) as nr_bytes
I have tested this on Oracle 10g without using Unicode(UTF-8). But I think that this solution must be correct using Unicode(UTF-8) Oracle instance :-)
我已经在没有使用 Unicode(UTF-8) 的情况下在 Oracle 10g 上测试过这个。但我认为这个解决方案必须正确使用 Unicode(UTF-8) Oracle 实例 :-)
I want render thanks to Nashev that has posted a solution to convert clob to blob How convert CLOB to BLOB in Oracle?and to this post written in german (the code is in PL/SQL) 13ter.info.blogthat give additionally a function to convert blob to clob !
我想渲染感谢 Nashev 发布了一个将 clob 转换为 blob 的解决方案如何在 Oracle 中将 CLOB 转换为 BLOB？以及这篇用德语写的帖子（代码在 PL/SQL 中）13ter.info.blog 还提供了一个将 blob 转换为 clob 的函数！
Can somebody test the 2 commands in Unicode(UTF-8) CLOB so I'm sure that this works with Unicode ?
有人可以测试 Unicode(UTF-8) CLOB 中的 2 个命令，所以我确定这适用于 Unicode 吗？
回答by Nalla Krishna
Check the LOB segment name from dba_lobs using the table name.
使用表名称从 dba_lobs 检查 LOB 段名称。
select TABLE_NAME,OWNER,COLUMN_NAME,SEGMENT_NAME from dba_lobs where TABLE_NAME='<<TABLE NAME>>';
Now use the segment name to find the bytes used in dba_segments.
现在使用段名称查找 dba_segments 中使用的字节。
select s.segment_name, s.partition_name, bytes/1048576 "Size (MB)"
from dba_segments s, dba_lobs l
where s.segment_name = l.segment_name
and s.owner = '<< OWNER >> ' order by s.segment_name, s.partition_name;
回答by Imran
It only works till 4000 byte, What if the clob is bigger than 4000 bytes then we use this
它只能工作到 4000 字节，如果 clob 大于 4000 字节，那么我们使用它
declare
v_clob_size clob;
begin
v_clob_size:= (DBMS_LOB.getlength(v_clob)) / 1024 / 1024;
DBMS_OUTPUT.put_line('CLOB Size ' || v_clob_size);
end;
or
或者
select (DBMS_LOB.getlength(your_column_name))/1024/1024 from your_table
