SQL 如何在 Oracle 中查询 CLOB 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3790379/
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 query a CLOB column in Oracle
提问by Catfish
I'm trying to run a query that has a few columns that are a CLOB datatype. If i run the query like normal, all of those fields just have (CLOB)as the value.
我正在尝试运行一个查询，其中有几列是 CLOB 数据类型。如果我像往常一样运行查询，所有这些字段都只是(CLOB)作为值。
I tried using DBMS_LOB.substr(column) and i get the error
我尝试使用DBMS_LOB.substr(column)，但出现错误
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
How can i query the CLOB column?
如何查询 CLOB 列？
采纳答案by mrjohn
When getting the substring of a CLOB column and using a query tool that has size/buffer restrictions sometimes you would need to set the BUFFER to a larger size. For example while using SQL Plus use the SET BUFFER 10000to set it to 10000 as the default is 4000.
当获取 CLOB 列的子字符串并使用具有大小/缓冲区限制的查询工具时，有时您需要将 BUFFER 设置为更大的大小。例如，在使用 SQL Plus 时，使用 将SET BUFFER 10000其设置为 10000，因为默认值为 4000。
Running the DBMS_LOB.substrcommand you can also specify the amount of characters you want to return and the offset from which. So using DBMS_LOB.substr(column, 3000)might restrict it to a small enough amount for the buffer.
运行该DBMS_LOB.substr命令，您还可以指定要返回的字符数量和偏移量。因此，使用DBMS_LOB.substr(column, 3000)可能会将其限制为足够小的缓冲区数量。
See oracle documentationfor more info on the substr command
有关substr 命令的更多信息，请参阅oracle 文档
DBMS_LOB.SUBSTR (
lob_loc IN CLOB CHARACTER SET ANY_CS,
amount IN INTEGER := 32767,
offset IN INTEGER := 1)
RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;
回答by Chris
This works
这有效
select DBMS_LOB.substr(myColumn, 3000) from myTable
回答by Cliff Bender
I did run into another condition with HugeClob in my Oracle database. The dbms_lob.substronly allowed a value of 4000 in the function, ex:
我的 Oracle 数据库中的 HugeClob 确实遇到了另一种情况。将dbms_lob.substr只允许4000的值的功能，例如：
dbms_lob.substr(column,4000,1)
so for my HughClob which was larger, I had to use two calls in select:
因此，对于较大的 HughClob，我不得不使用以下两个调用select：
select dbms_lob.substr(column,4000,1) part1,
dbms_lob.substr(column,4000,4001) part2 from .....
I was calling from a Java app so I simply concatenated part1 and part2 and sent as a email.
我是从 Java 应用程序调用的，所以我只是将 part1 和 part2 连接起来并作为电子邮件发送。
回答by Raj
If it's a CLOB why can't we to_char the column and then search normally ?
如果是 CLOB 为什么我们不能对列进行字符化，然后正常搜索？
Create a table
创建一个表
CREATE TABLE MY_TABLE(Id integer PRIMARY KEY, Name varchar2(20), message clob);
Create few records in this table
在此表中创建几条记录
INSERT INTO MY_TABLE VALUES(1,'Tom','Hi This is Row one');
INSERT INTO MY_TABLE VALUES(2,'Lucy', 'Hi This is Row two');
INSERT INTO MY_TABLE VALUES(3,'Frank', 'Hi This is Row three');
INSERT INTO MY_TABLE VALUES(4,'Jane', 'Hi This is Row four');
INSERT INTO MY_TABLE VALUES(5,'Robert', 'Hi This is Row five');
COMMIT;
Search in the clob column
在clob栏中搜索
SELECT * FROM MY_TABLE where to_char(message) like '%e%';
Results
结果
ID NAME MESSAGE
===============================
1 Tom Hi This is Row one
3 Frank Hi This is Row three
5 Robert Hi This is Row five
回答by Kapitula Alexey
For big CLOB selects also can be used:
对于大 CLOB 选择也可以使用：
SELECT dbms_lob.substr( column_name, dbms_lob.getlength(column_name), 1) FROM foo
SELECT dbms_lob.substr( column_name, dbms_lob.getlength(column_name), 1) FROM foo
回答by Mustafa
Another option is to create a function and call that function everytime you need to select clob column.
另一种选择是创建一个函数并在每次需要选择 clob 列时调用该函数。
create or replace function clob_to_char_func
(clob_column in CLOB,
for_how_many_bytes in NUMBER,
from_which_byte in NUMBER)
return VARCHAR2
is
begin
Return substrb(dbms_lob.substr(clob_column
,for_how_many_bytes
,from_which_byte)
,1
,for_how_many_bytes);
end;
and call that function as;
并将该函数称为；
SELECT tocharvalue, clob_to_char_func(tocharvalue, 1, 9999)
FROM (SELECT clob_column AS tocharvalue FROM table_name);
回答by Himanshu sharma
To add to the answer.
添加到答案中。
declare
v_result clob;
begin
---- some operation on v_result
dbms_lob.substr( v_result, 4000 ,length(v_result) - 3999 );
end;
/
In dbms_lob.substr
在 dbms_lob.substr
first parameteris clobwhich you want to extract .
first parameter是clob您要提取的 .
Second parameteris how much length of clob you want to extract.
Second parameter是你想提取多少长度的clob。
Third parameteris from which word you want to extract .
Third parameter是从哪个词中提取。
In above example i know my clob size is more than 50000 , so i want last 4000 character .
在上面的例子中，我知道我的 clob 大小超过 50000，所以我想要最后 4000 个字符。
回答by Scott
If you are using SQL*Plus try the following...
如果您使用 SQL*Plus，请尝试以下操作...
set long 8000
select ...
