从表中选择DISTINCT CLOB_COLUMN; [英] SELECT DISTINCT CLOB_COLUMN FROM TABLE;
问题描述
我想找到不同的CLOB值,这些值可以假定包含在名为COPIA的表中的CLOB_COLUMN列(CLOB类型).
我选择了一种解决此问题的方法,但我希望给出一个简单的SELECT如下:SELECT DISTINCT CLOB_COLUMN FROM TABLE避免出现错误"ORA-00932:不一致的数据类型:预期的-得到了CLOB" >
我该如何实现?
在此先感谢您的合作.这是我想过的程序方式:
-- 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;
使用此方法.在表配置文件中,列内容是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
;
它不会因为效率而赢得任何奖励,但是应该可以工作.
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.
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"
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;
Use this approach. In table profile column content is NCLOB. I added the where clause to reduce the time it takes to run which is high,
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.
这篇关于从表中选择DISTINCT CLOB_COLUMN;的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!