oracle 从表中选择 DISTINCT CLOB_COLUMN;
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3921982/
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
SELECT DISTINCT CLOB_COLUMN FROM TABLE;
提问by UltraCommit
I would like to find the distinct CLOB values that can assume the column called CLOB_COLUMN (of type CLOB) contained in the table called COPIA.
我想找到不同的 CLOB 值，这些值可以假设名为 COPIA 的表中包含的名为 CLOB_COLUMN（CLOB 类型）的列。
I have selected a PROCEDURAL WAY to solve this problem, but I would prefer to give a simple SELECT as the following: SELECT DISTINCT CLOB_COLUMN FROM TABLE avoiding the error "ORA-00932: inconsistent datatypes: expected - got CLOB"
我选择了一种程序方式来解决这个问题，但我更愿意给出一个简单的 SELECT 如下： SELECT DISTINCT CLOB_COLUMN FROM TABLE 避免错误“ORA-00932：不一致的数据类型：预期 - 得到 CLOB”
How can I achieve this?
我怎样才能做到这一点？
Thank you in advance for your kind cooperation. This is the procedural way I've thought:
预先感谢您的友好合作。这是我认为的程序方式：
-- Find the distinct CLOB values that can assume the column called CLOB_COLUMN (of type CLOB)
-- contained in the table called COPIA
-- Before the execution of the following PL/SQL script, the CLOB values (including duplicates)
-- are contained in the source table, called S1
-- At the end of the excecution of the PL/SQL script, the distinct values of the column called CLOB_COLUMN
-- can be find in the target table called S2
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE S1 DROP STORAGE';
EXECUTE IMMEDIATE 'DROP TABLE S1 CASCADE CONSTRAINTS PURGE';
EXCEPTION
WHEN OTHERS
THEN
BEGIN
NULL;
END;
END;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE S2 DROP STORAGE';
EXECUTE IMMEDIATE 'DROP TABLE S2 CASCADE CONSTRAINTS PURGE';
EXCEPTION
WHEN OTHERS
THEN
BEGIN
NULL;
END;
END;
CREATE GLOBAL TEMPORARY TABLE S1
ON COMMIT PRESERVE ROWS
AS
SELECT CLOB_COLUMN FROM COPIA;
CREATE GLOBAL TEMPORARY TABLE S2
ON COMMIT PRESERVE ROWS
AS
SELECT *
FROM S1
WHERE 3 = 9;
BEGIN
DECLARE
CONTEGGIO NUMBER;
CURSOR C1
IS
SELECT CLOB_COLUMN FROM S1;
C1_REC C1%ROWTYPE;
BEGIN
FOR C1_REC IN C1
LOOP
-- How many records, in S2 table, are equal to c1_rec.clob_column?
SELECT COUNT (*)
INTO CONTEGGIO
FROM S2 BETA
WHERE DBMS_LOB.
COMPARE (BETA.CLOB_COLUMN,
C1_REC.CLOB_COLUMN) = 0;
-- If it does not exist, in S2, a record equal to c1_rec.clob_column,
-- insert c1_rec.clob_column in the table called S2
IF CONTEGGIO = 0
THEN
BEGIN
INSERT INTO S2
VALUES (C1_REC.CLOB_COLUMN);
COMMIT;
END;
END IF;
END LOOP;
END;
END;
采纳答案by Janek Bogucki
Use this approach. In table profilecolumn contentis NCLOB. I added the where clause to reduce the time it takes to run which is high,
使用这种方法。在表配置文件列内容是 NCLOB。我添加了 where 子句以减少运行所需的时间，这是高的，
with
r as (select rownum i, content from profile where package = 'intl'),
s as (select distinct (select min(i) from r where dbms_lob.compare(r.content, t.content) = 0) min_i from profile t where t.package = 'intl')
select (select content from r where r.i = s.min_i) content from s
;
It is not about to win any prizes for efficiency but should work.
它不会因为效率而赢得任何奖品，但应该会奏效。
回答by Vincent Malgrat
You could compare the hashes of the CLOB to determine if they are different:
您可以比较 CLOB 的哈希值以确定它们是否不同：
SELECT your_clob
FROM your_table
WHERE ROWID IN (SELECT MIN(ROWID)
FROM your_table
GROUP BY dbms_crypto.HASH(your_clob, dbms_crypto.HASH_SH1))
Edit:
编辑：
The HASHfunction doesn't guarantee that there will be no collision. By design however, it is really unlikely that you will get any collision. Still, if the collision risk (<2^80?) is not acceptable, you could improve the query by comparing (with dbms_lob.compare) the subset of rows that have the same hashes.
该HASH函数不保证不会发生碰撞。但是，按照设计，您真的不太可能发生任何碰撞。尽管如此，如果碰撞风险 (<2^80?) 是不可接受的，您可以通过比较（与dbms_lob.compare）具有相同哈希值的行子集来改进查询。
回答by Laurent
If it is acceptable to truncate your field to 32767 characters this works:
如果将您的字段截断为 32767 个字符是可以接受的，则此方法有效：
select distinct dbms_lob.substr(FIELD_CLOB,32767) from Table1
回答by Tahir Usman
add TO_CHARafter distinct keyword to convert CLOB to CHAR
TO_CHAR在不同的关键字后添加以将 CLOB 转换为 CHAR
SELECT DISTINCT TO_CHAR(CLOB_FIELD) from table1; //This will return distinct values in CLOB_FIELD
回答by Nitin Pawar
select distinct DBMS_LOB.substr(column_name, 3000) from table_name;
从 table_name 中选择不同的 DBMS_LOB.substr(column_name, 3000);
回答by Chris Saxon
If truncating the clob to the size of a varchar2 won't work, and you're worried about hash collisions, you can:
如果将 clob 截断为 varchar2 的大小不起作用，并且您担心哈希冲突，您可以：
- Add a row number to every row;
- Use DBMS_lob.compare in a not exists subquery. Exclude duplicates (this means: compare = 0) with a higher rownum.
- 每行添加一个行号；
- 在不存在的子查询中使用 DBMS_lob.compare。排除具有更高 rownum 的重复项（这意味着：比较 = 0）。
For example:
例如：
create table t (
c1 clob
);
insert into t values ( 'xxx' );
insert into t values ( 'xxx' );
insert into t values ( 'yyy' );
commit;
with rws as (
select row_number () over ( order by rowid ) rn,
t.*
from t
)
select c1 from rws r1
where not exists (
select * from rws r2
where dbms_lob.compare ( r1.c1, r2.c1 ) = 0
and r1.rn > r2.rn
);
C1
xxx
yyy
回答by Salim Hamidi
To bypass the oracle error, you have to do something like this :
要绕过 oracle 错误，您必须执行以下操作：
SELECT CLOB_COLUMN FROM TABLE COPIA C1 WHERE C1.ID IN (SELECT DISTINCT C2.ID FROM COPIA C2 WHERE ....)
SELECT CLOB_COLUMN FROM TABLE COPIA C1 WHERE C1.ID IN (SELECT DISTINCT C2.ID FROM COPIA C2 WHERE ....)
